Solved

script to insert records from one table to another in batches

Posted on 2013-06-20
6
1,087 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 22

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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

762 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