Solved

script to insert records from one table to another in batches

Posted on 2013-06-20
6
815 Views
Last Modified: 2013-06-21
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?
0
Comment
Question by:navajo26354
  • 3
  • 2
6 Comments
 
LVL 19

Expert Comment

by:Patricksr1972
Comment Utility
Hi,

SELECT Top 100000 INTO db2.table1 FROM db1.table2

sounds like an option?
0
 
LVL 8

Assisted Solution

by:didnthaveaname
didnthaveaname earned 250 total points
Comment Utility
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
 
LVL 25

Accepted Solution

by:
jogos earned 250 total points
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 25

Expert Comment

by:jogos
Comment Utility
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
 
LVL 8

Expert Comment

by:didnthaveaname
Comment Utility
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
 
LVL 25

Expert Comment

by:jogos
Comment Utility
@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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now