May 6, 2011 at 7:07 am
Trying to find a way to commit after N rows inserted to keep the logs from filling up. Here is the scenario:
1. In two seperate databases on the same server, there are two identical tables (TrackingTable)
2. AccessKey is the PK in both tables
3. Records in DatabaseB need to be inserted into DatabaseA
4. The "where not exists" gets rid of the duplicate records
Insert into DatabaseA.dbo.TrackingTable
Select * from DatabaseB.dbo.TrackingTable b
where not exists
(Select AccessKey from DatabaseA.dbo.TrackingTable a where c.AccessKey= a.AccessKey)
May 6, 2011 at 8:30 am
You can either use a While loop to insert the records or create an SSIS package and set the Maximum Insert Commit Size on the component. Just beware that as these tables get larger, the more selects can cause performance issues.
For the While Loop you can use something like:
declare @CommitSize int
set @CommitSize = 10000
while @CommitSize = 10000
begin
begin tran
Insert into DatabaseA.dbo.TrackingTable
Select top 10000 * from DatabaseB.dbo.TrackingTable b
where not exists
(Select AccessKey from DatabaseA.dbo.TrackingTable a
where c.AccessKey= a.AccessKey)
set @CommitSize = @@RowCount
commit tran
end
P.S. you probably already know, but you shouldn't use the * when selecting the fields.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 6, 2011 at 9:08 am
Thank you so much for your reponse! I really appreciate you sharing your knowledge. It works for exactly what I need.
I'm thinking of future needs now...If I ever wanted it to move onto the next set of 10000 records rather than the top 10000, would I need to use a cursor and a for/while loop? If so, where would I put the insert statement?
May 6, 2011 at 9:12 am
I'm not really sure what you're asking. This while loop will continue looping through all records until the rowcount <> 10000. Presumably, the last insert will only add <10000 records, so then the while loop will end. For instance, if there were 95000 records to insert, the first 9 loops woul enter 10000 records and the last will insert the remaining 5000 then exit.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 6, 2011 at 9:23 am
Yes I didn't make any sense...I had a confused moment! What can I say? It's Friday 😉 Thanks again for all of your help
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply