CODEWITHSUNDEEP
sqlsql-servert-sql
Amaan Khan
Amaan Khan

Reputation: 89

SQL to insert 200 Million records

I am trying to insert around 200M records into a table after doing a select from a fact table but a

System.OutOfMemoryException

Following is my query :

Select 
    ProductID,
    SaleDate,
    Quantity 
from 
    dbo.Product 
where
    SaleDate BETWEEN '2012-01-01' AND '2016-01-01';

The result of the above SELECT returns 200M records which is to be inserted in a table.

Can someone suggest an alternative?

Upvotes: 0

Views: 7522

Answers (7)

Yara
Yara

Reputation: 4565

Some time ago I needed to insert about 20 million rows from one table to another. My solution was to splitting insertions into small pieces of SQL statements. Using the print command I generated thousands of queries and smoothly do my migration. The detailed way I do it look here https://yarkul.com/how-smoothly-insert-millions-of-rows-in-sql-server/

Upvotes: 0

Slawomir Cieslinski
Slawomir Cieslinski

Reputation: 635

Use SQL Server Integration Services

Integration Services includes a rich set of built-in tasks and transformations; tools for constructing packages; and the Integration Services service for running and managing packages. You can use the graphical Integration Services tools to create solutions without writing a single line of code; or you can program the extensive Integration Services object model to create packages programmatically and code custom tasks and other package objects.

enter image description here

Upvotes: 1

David דודו Markovitz
David דודו Markovitz

Reputation: 44991

If you have indexes on your target table drop them and create them after the insert.

Upvotes: 1

Nolan Shang
Nolan Shang

Reputation: 2328

you can do this in batchs, for example each time do one year or half year The following is get one year data one time.

     DECLARE @StartYear INT =2012,@EndYear INT =2016,@iYear INT 
     SET @iYear=@StartYear+1
     WHILE @iYear<@EndYear
     BEGIN
         Select ProductID,SaleDate,Quantity from dbo.Product where SaleDate BETWEEN DATEADD(yy,@iYear-1900,0) AND DATEADD(yy,@iYear+1-1900,0); 
         SET @iYear+=1
     END

Upvotes: 2

Homan
Homan

Reputation: 26768

As mentioned don't do the whole thing at once. Step through it with smaller records. Here is sample of someone else doing the same thing: https://dba.stackexchange.com/questions/53831/large-insert-into-select-from-gradually-gets-slower

Upvotes: 2

Eduard
Eduard

Reputation: 676

The error System.OutOfMemoryException occurs because SSMS has insufficient memory to allocate for large results.

Try with a small set of data if possible by using row_number().

SELECT * FROM (
    Select ROW_NUMBER() OVER(ORDER BY ProductID,SaleDate,Quantity) AS rowid, 
    ProductID,SaleDate,Quantity 
    from dbo.Product where SaleDate BETWEEN '2012-01-01' AND '2016-01-01'
    ) foo 
WHERE rowid BETWEEN 1 AND 500000;

Upvotes: 1

Rob
Rob

Reputation: 1

Try to insert a smaller record set. Start with selecting all of 2012. The error is throw because there's insufficient memory to allocate. Your select query is eating up all the memory, and that is why i recommend selecting less data.

Upvotes: 0

Related Questions