Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Cursor to Insert data from one table to another

Author  Topic 

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2011-08-24 : 16:47:39
Hi Folks, How can I achive this.

I have a table A (AcctID bigint not null, ContactId,Amount,TransDate,Rate)

I have a table B (ContactId,Amount,TransDate,Rate) [[all columns same as table A except no AcctId column]]

I need to insert rows from table B to table A and assign a new value for the AcctId in table A while doing so, AcctId column is a PK in table A.

These tables belong to an application and the AcctId gets auto generated normally by the application but since we are doing huge Inserts from backend I need to populate a unique AcctId for each row inserted.

When I execute SELECT TOP 1(AcctID) FROM dbo.tableA ORDER BY AcctID DESC

I get 990,986 .

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-24 : 16:58:47
This should do the trick:
;WITH ID(AcctID) AS (SELECT MAX(AcctID) FROM TableA WITH (SERIALIZABLE))
INSERT TableA(AcctID, ContactID, Amount, TransDate, Rate)
SELECT I.AcctID + ROW_NUMBER() OVER (ORDER BY B.ContactID), B.ContactID, B.Amount, B.TransDate, B.Rate
FROM TableB B CROSS JOIN ID I

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-25 : 01:57:48
put max value in a variable and then add row number value as suggested by Rob

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2011-08-25 : 10:06:08
Thank You robvolk
Go to Top of Page
   

- Advertisement -