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?
navajo26354Asked:
Who is Participating?
 
jogosConnect With a Mentor Commented:
Why is it to large to do this in one shot? Is it a one time operation? What is the recovery model of your destination-db? Answers on this question can give you a variation of other options.
- copy table wizzard
- Bulk insert  in combination with change temporary recovery model  from full to simple or bulk logged (and changing it back followed by a new full backup)

Just answering your how to split it in batches
If your sourcetable has an indentity-column use that to have your batches. Change the  the batch-size to have the best performance.
DECLARE @BatchSize INT = 100000
declare @StartId = 0;
declare @MaxId;
select @MaxId = max(Id) from [dbo].[SOURCE] 

WHILE 1 = 1
BEGIN

    INSERT INTO [dbo].[Destination]
    (        col1,col@
    )
    SELECT col1,col2
    FROM [dbo].[SOURCE] 
    WHERE id between @StartId and @StartId + @BatchSize;

    SET @StartId = @StartId + @BatchSize
    IF @StartId > @MaxId BREAK
    
END

Open in new window

0
 
Patrick BogersDatacenter platform engineer LindowsCommented:
Hi,

SELECT Top 100000 INTO db2.table1 FROM db1.table2

sounds like an option?
0
 
didnthaveanameConnect With a Mentor Commented:
Is the primary key a unique numeric value or no?  You need to loop through the table, but there has to be a column that you can use to do similar to the following:

declare @rowCount as bigint;
declare @batchSize as int;
declare @currPos as bigint;

set @currPos = 0;
set @batchSize = 100000;
set @rowCount = ( select max( primaryKey ) from srcTbl );

while( @currPos < @rowCount )
begin
	insert into destTbl
		select 
			* 
		from 
			srcTbl
		where
			primaryKey >= @currPos and
			primaryKey < ( @currPos + @batchSize );

	set @currPos = @currPos + @batchSize;
end;

Open in new window

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
jogosCommented:
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
set @rowCount = ( select max( primaryKey ) from srcTbl );

Open in new window



And the error ... if you have bad luck the last row won't be copied if you don't use the <=

while( @currPos < @rowCount )

Open in new window

0
 
didnthaveanameCommented:
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!
0
 
jogosCommented:
@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.
0
All Courses

From novice to tech pro — start learning today.