William White
asked on
script to insert records from one table to another in batches
I have a very large table in one database. I need to copy a subset of records from this table and insert them into another database. Since this table is so large I need to find a way to copy these records in batches of 100000. Can someone give me an example script that will do this?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I see my sollution is basicly the same technique as that of 'didnthaveaname'. Got a little confused by his confusing variable-name
@Rowcount for a maximum value of an ID? Why not @MaxId? ... And also easily confused with @@Rowcount
And the error ... if you have bad luck the last row won't be copied if you don't use the <=
@Rowcount for a maximum value of an ID? Why not @MaxId? ... And also easily confused with @@Rowcount
set @rowCount = ( select max( primaryKey ) from srcTbl );
And the error ... if you have bad luck the last row won't be copied if you don't use the <=
while( @currPos < @rowCount )
jogos, good points on all fronts, completely missed the while loop logic error. And i can see how my variables are a little confusing now that you mention it. In my defense, I had initially started out using a count( primaryKey ) instead of a max( primaryKey ) but realized that there could be more issues with count vs. max =) (only been in the SQL field for 5 months, so still learning!) Cheers!
@didnthaveanam
We all have it starting with trying something and change in another direction. Good names are important. I judged your code simply on the variable-names and thouth you where complete wrong, it wsn't but it wasn't perfect either. If you or a collegue looks at same code some months later it should read easy.
We all have it starting with trying something and change in another direction. Good names are important. I judged your code simply on the variable-names and thouth you where complete wrong, it wsn't but it wasn't perfect either. If you or a collegue looks at same code some months later it should read easy.
SELECT Top 100000 INTO db2.table1 FROM db1.table2
sounds like an option?