Select SQL Server Issue
I am using SQL Server 2008 and I need to select all data from one table of one DB to another table of another DB on the same instance of SQL Server.
Here is my script. The server will run out of memory. The data is large - a table about 50G in size on disk. Any lightweight alternative solution or any solution to reduce memory consumption is good. The server has 16 GB of physical memory and x64.
Here is the operator I am using,
insert into [TargetDB].[dbo].[Orders]
select *
from [SourceDB].[dbo].[Orders];
Any quick and easy solutions?
thanks in advance george
source to share
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
)
This should be done in batches of 100, which you can adjust according to the number of records you need to process. This code assumes that you have some form of unique value, such as OrderId, to turn off during data copying.
Copy in ranges
If you have a field that you can use to select "ranges" such as OrderDate, start by running a query like
SELECT OrderDate, COUNT(1)
FROM [SourceDB].[dbo].[Orders]
to find out how many distinct values there are and how many records there are for a particular value. This should allow you to select some ranges (e.g. 2009-01-01 -> 2009-01-31) and then use the given queries to copy the data through:
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'
source to share