Making a row-based Iteration of a Bulk-Insertion
-
We are looking a change on the code to make it watchable and improve it's performance. Want to change this piece of SQL query into a loop version which Could `Select-Insert` records by 1000 items per iteration. On each iteration want to get a print to see what was the last inserted item and if possible get the elapsed time on the iteration. The Code : INSERT INTO [tData2] ( [Key], Info1, Info2 ) SELECT [Key], Info1, Info2 FROM [tData] -- Conditions were removed as weren't related to this question your help is really appreciated.
-
We are looking a change on the code to make it watchable and improve it's performance. Want to change this piece of SQL query into a loop version which Could `Select-Insert` records by 1000 items per iteration. On each iteration want to get a print to see what was the last inserted item and if possible get the elapsed time on the iteration. The Code : INSERT INTO [tData2] ( [Key], Info1, Info2 ) SELECT [Key], Info1, Info2 FROM [tData] -- Conditions were removed as weren't related to this question your help is really appreciated.
-
We are looking a change on the code to make it watchable and improve it's performance. Want to change this piece of SQL query into a loop version which Could `Select-Insert` records by 1000 items per iteration. On each iteration want to get a print to see what was the last inserted item and if possible get the elapsed time on the iteration. The Code : INSERT INTO [tData2] ( [Key], Info1, Info2 ) SELECT [Key], Info1, Info2 FROM [tData] -- Conditions were removed as weren't related to this question your help is really appreciated.
Limiting the loop will slow your performance. There really isn't a nice way of doing it without resorting to pre-import queries or using a cursor. Neither choice will result in faster performance. Proper indexes on the conditions will improve performance given the query you listed. If you are moving lots of data into an empty table and you can fully control its integrity, then remove all constraints (except identities) and indexes on the new table - update the table - and put them back on. This can greatly speed up a large insert. You can also take a look at BULK INSERT if your SQL version will allow.
-
Limiting the loop will slow your performance. There really isn't a nice way of doing it without resorting to pre-import queries or using a cursor. Neither choice will result in faster performance. Proper indexes on the conditions will improve performance given the query you listed. If you are moving lots of data into an empty table and you can fully control its integrity, then remove all constraints (except identities) and indexes on the new table - update the table - and put them back on. This can greatly speed up a large insert. You can also take a look at BULK INSERT if your SQL version will allow.