select into issue of SQL Server

Question

Hello everyone,

I am using SQL Server 2008 and I need to select all data from one table of one DB into another table of another DB on the same SQL Server instance.

Here is my script using. The server will run out of memory. The data is big -- table is about 50G size on disk. Any easy alternative solution or any solution to lower memory consumption is fine. The server has 16G physical RAM and is x64.

Here is the statement I am using,

insert into [TargetDB].[dbo].[Orders]
select *
from [SourceDB].[dbo].[Orders];

Any quick and simple solutions?

thanks in advance, George

Answer 1

Copy in batches

INSERT INTO [TargetDB].[dbo].[Orders]
SELECT TOP 100 *
FROM [SourceDB].[dbo].[Orders] S
WHERE NOT EXISTS
(
 SELECT 1 FROM [TargetDB].[dbo].[Orders] T1
 WHERE T1.OrderId = S.orderID
)

That should do it in batches of 100, which you could tweak to suit the number of records you need to process. This code does assume that you have some form of Unique value, such as OrderId to key off in the data copy process

Copy in ranges

If you have a field you can use to choose "ranges" such as an OrderDate, start off by running a query like

SELECT OrderDate, COUNT(1)
FROM [SourceDB].[dbo].[Orders]

to see how many distinct values there are and how many records there are per distinct value. That should allow you to choose some ranges (eg. 2009-01-01 -> 2009-01-31) and then use ranged queries to copy the data across:

INSERT INTO [TargetDB].[dbo].[Orders]
SELECT *
FROM [SourceDB].[dbo].[Orders] 
WHERE OrderDate BETWEEN '2009-01-01 00:00:00.000' AND '2009-01-31 23:59:59.997'

Answer 2

Add some partitioning so that you don't have so take it all at once. Get data for one month at a time, or all ID:s ending with a specific number.

That way each batch gets a bit smaller.

Answer 3

You might want to look into using BCP to bulk copy the data.