Commit Question

[Jimreaper]

Hello all,
What is the proper SQL to use for this senario:
Insert all records into table1 from table2 and commit after every 10000 rows until complete.

Thanks for helping a beginner.
Jim

 

[tlbroadbent]

Assuming the table has an identity column or unique constraint, you could do something like the following.

Set Rowcount 10000

Declare @rc int
Select @rc=1

While @rc>0
Begin
Begin Transaction
Insert table2 (ID, Col2, col3, ....)
Select ID, colB, colC, ...
From table1
Where Not Exists
(Select * From table2
Where ID=table1.ID)
Select @rc=@rowcount
Commit
End

Set rowcount 0 Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.