Use a cursor to Insert data from one table to the other

Question

Use a cursor to Insert data from one table to the other

LOOKUP_BI-756009

SSCertifiable

Points: 7286
More actions
August 24, 2011 at 2:45 pm

#246868

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 .

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Certainly do NOT use a cursor!!!!!!!!!! I assume you want to insert these new records with a number larger than TableA has? Just use an identity column and seed it with a large number. You should probably make it a big bigger to allow for inserts into TableA while you are inserting your data into TableB. Then you should probably reseed the identity of TableA to start with number higher than the largest value in Tableb.

create table #test

(

ID int identity(123456, 1),

someValue varchar(20)

)

insert #test (someValue) values ('val1'), ('val2'), ('val3'), ('val4'), ('val15')

select * from #test

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

You dont need a cursor to do this 🙂

some like

DECLARE @MaxIDVal BIGINT

SELECT @MaxIDVal = TOP 1(AcctID) FROM dbo.tableA ORDER BY AcctID DESC

; WITH CTE AS

(

SELECT RN = ROW_NUMBER() OVER( Order BY NULL ) ,

ContactId,Amount,TransDate,Rate

FROM TableB

)

INSERT INTO CTE (AcctID , ContactId,Amount,TransDate,Rate)

SELECT RN + MaxIDVal AcctID , ContactId,Amount,TransDate,Rate

FROM CTE

Hope that helps

LOOKUP_BI-756009 SSCertifiable Points: 7286 More actions · Answer 3

ColdCoffee (8/24/2011)
You dont need a cursor to do this 🙂
some like
DECLARE @MaxIDVal BIGINT
SELECT @MaxIDVal = TOP 1(AcctID) FROM dbo.tableA ORDER BY AcctID DESC
; WITH CTE AS
(
SELECT RN = ROW_NUMBER() OVER( Order BY NULL ) ,
ContactId,Amount,TransDate,Rate
FROM TableB
)
INSERT INTO CTE (AcctID , ContactId,Amount,TransDate,Rate)
SELECT RN + MaxIDVal AcctID , ContactId,Amount,TransDate,Rate
FROM CTE
Hope that helps

ColdCoffee, Thanks for your response. Im getting an error message Incorrect syntax near the keyword 'TOP'.

Here is my code,

DECLARE @MaxIDVal BIGINT

SELECT @MaxIDVal = TOP 1(AcctID) FROM dbo.tableA ORDER BY AcctID DESC

; WITH CTE AS

(

SELECT RN = ROW_NUMBER() OVER( Order BY NULL ) ,

ContactId,Amount,TransDate,Rate

FROM TableB

)

INSERT INTO TableA (AcctID , ContactId,Amount,TransDate,Rate)

SELECT RN + @MaxIDVal AcctID , ContactId,Amount,TransDate,Rate

FROM CTE

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Variable declaration was on the wrong side of the top.

SELECT TOP 1 @MaxIDVal = (AcctID) FROM dbo.tableA ORDER BY AcctID DESC

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

LOOKUP_BI-756009 SSCertifiable Points: 7286 More actions · Answer 5

Thanks Sean, that worked but now I get

Msg 5309, Level 16, State 1, Line 4

Windowed functions do not support constants as ORDER BY clause expressions.

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

That error should be pretty clear. You can't use a constant as the order by column in a windowed function. You will have to choose a column to sort by. In your case probably AcctID.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

LOOKUP_BI-756009 SSCertifiable Points: 7286 More actions · Answer 7

Yup did that, Thanks a lot. Im looking at the data.Will update if run into any other problems.

Thanks You Sean & Cold Coffee

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 8

OH my bad, i am sorry being a hasty poster. I should have taken time to atleast parse the code, sorry!

Here is the syntax-error-free code; Whether is works for you, you got to test 😉

DECLARE @MaxIDVal BIGINT

SELECT TOP 1 @MaxIDVal = (AcctID) FROM dbo.tableA ORDER BY AcctID DESC

; WITH CTE AS

(

SELECT RN = ROW_NUMBER() OVER( ORDER BY (SELECT NULL) ) ,

ContactId

, Amount

, TransDate

, Rate

FROM TableB

)

INSERT INTO TableA( AcctID

, ContactId

, Amount

, TransDate

, Rate )

SELECT (RN + @MaxIDVal) AcctID

, ContactId

, Amount

, TransDate

, Rate

FROM CTE