Solved

script to insert records from one table to another in batches

Posted on 2013-06-20
6
945 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
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
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…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

773 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