Duplicate Rows with one diffrent Column value

Question

0.00/5 (No votes)

See more:

TranDate        Amount    EvolutionCreditAccount   EvolutionDebitAccount    Completed

31/01/2013 12:00:00 AM  262033.83   1020/MICT/MOS/PISA/0006 1020/MICT/MOS/PISA/0002 1
31/01/2013 12:00:00 AM  197237.28   1020/MICT/MOS/PISA/0099 1020/MICT/MOS/PISA/0033 1
31/01/2013 12:00:00 AM  980834.24   1020/MICT/MOS/PISA/0002 1020/MICT/MOS/PISA/0003 1
31/01/2013 12:00:00 AM  31579.87    1020/MICT/MOS/PISA/0001 1020/MICT/MOS/PISA/0002 0

i want to duplicate values in sql server where column complete is equal to '1' and it only changes TranDate value for it.

EG: my inserted values will be following

TranDate        Amount    EvolutionCreditAccount   EvolutionDebitAccount    Completed

31/02/2013 12:00:00 AM  262033.83   1020/MICT/MOS/PISA/0006 1020/MICT/MOS/PISA/0002 1
31/02/2013 12:00:00 AM  197237.28   1020/MICT/MOS/PISA/0099 1020/MICT/MOS/PISA/0033 1
31/02/2013 12:00:00 AM  980834.24   1020/MICT/MOS/PISA/0002 1020/MICT/MOS/PISA/0003 1

simply:
if i change date as value it must duplicate all the rows with column Complete value of 1 and only change TranDate for it.

Posted 24-Feb-13 23:23pm

ByakuyaKuchiki

Updated 24-Feb-13 23:35pm

Aarti Meswania

v2

Add a Solution

Comments

gvprabu 25-Feb-13 5:34am

Hi,
You need to insert all Row again in case if you change tranDate right...?

ByakuyaKuchiki 25-Feb-13 5:40am

so are you saying i have to insert each row again indivigualy with new TranDate value?

CHill60 25-Feb-13 5:35am

what have you tried so far?

ByakuyaKuchiki 25-Feb-13 5:38am

insert into ECS_PayRollTableView
select * from ECS_PayRollTableView
where Completed=1

and i am geting this error, but this dose not let me inser my own choice date

The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.

Prakash Thirumoorthy 25-Feb-13 5:47am

do u have id field in table structure.. can u share table structure

CHill60 25-Feb-13 5:50am

To use your own choice of date try explicitly listing your columns instead of using "select *" ... then you can include something like GetDate() As TranDate.

Also you appear to be trying to insert into a View rather than the underlying table - see Books Online for the kind of problems you can encounter. This is probably the source of the error you are seeing.

Prakash Thirumoorthy 25-Feb-13 5:53am

if u used any id field with identity property u cannot use select * from.. u have to mention filed name. Because cannot add/edit value for identity field.

3 solutions

Solution 1

Try using UNION ALL,

SQL

INSERT INTO Student(TranDate, Amount,EvolutionCreditAccount, EvolutionDebitAccount,Completed <pre lang="sql">Completed)
SELECT GetDate(), Amount, EvolutionCreditAccount, EvolutionDebitAccount,Completed WHERE Completed=1;
UNION ALL
SELECT GetDate(), Amount, EvolutionCreditAccount, EvolutionDebitAccount,Completed WHERE Completed=1;

Posted 24-Feb-13 23:45pm

MalwareTrojan

Solution 3

Try the below SQL Query -

DECLARE @TranDate   DATETIME = '02/23/2013 12:00:00 AM', -- new date time for the duplicate records
        @Amount MONEY,
        @EvolutionCreditAccount VARCHAR(MAX),
        @EvolutionDebitAccount VARCHAR(MAX),
        @Id INT = 1,
        @MaxId INT 

DECLARE @TEMPTABLE TABLE (Id INT IDENTITY(1,1),TranDate DATETIME, Amount    MONEY, EvolutionCreditAccount   NVARCHAR(MAX), EvolutionDebitAccount    NVARCHAR(MAX), Completed BIT)


INSERT @TEMPTABLE 
SELECT * FROM temp 
WHERE Completed = 1

SELECT  @MaxId = MAX(Id) FROM @TEMPTABLE

WHILE @Id <= @MaxId
	BEGIN 
	 SELECT 
		@Amount = Amount, 
		@EvolutionCreditAccount =  EvolutionCreditAccount,
		@EvolutionDebitAccount = EvolutionDebitAccount
	 FROM @TEMPTABLE
	 WHERE Id = @Id 	
	
	 
	 INSERT INTO Temp
	 (
	 	TranDate,
	 	Amount,
	 	EvolutionCreditAccount,
	 	EvolutionDebitAccount,
	 	Completed
	 )
	 VALUES
	 (
	 	@TranDate,
	 	@Amount,
	 	@EvolutionCreditAccount,
	 	@EvolutionDebitAccount,
	 	1
	 )
	  SET @Id = @Id + 1
	  
	END

Posted 25-Feb-13 0:05am

mostafiz rahman

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

S R JHALA · Accepted Answer · 2013-02-24T23:50:00

You need to specify the field name after the table name and Field which you want as your choice, I have replaced that with GETDATE() which will insert CurrentDatetime of SQL Server see the query below.

SQL

INSERT INTO ECS_PayRollTableView
(TranDate,Amount,EvolutionCreditAccount,EvolutionDebitAccount,Completed)
SELECT
    GETDATE()
    ,Amount
    ,EvolutionCreditAccount
    ,EvolutionDebitAccount
    ,Completed
FROM
    ECS_PayRollTableView
where Completed=1