Solved

script to insert records from one table to another in batches

Posted on 2013-06-20
6
1,335 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 23

Expert Comment

by:Patrick Bogers
ID: 39263950
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
ID: 39264122
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
ID: 39266220
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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
LVL 25

Expert Comment

by:jogos
ID: 39266255
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
ID: 39266335
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
ID: 39266468
@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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

617 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