We help IT Professionals succeed at work.

Insert statement causing out of memory error

326 Views
Last Modified: 2013-11-05
Hello Experts,

We are using SQL Server 2005. We received a sql file from a vendor that needs to be executed. It is made up of 400K insert statements.  At every 1000 line interval it is split by the "GO" keyword. When this statement is executed, we get an 'out of memory' error. Any suggestions would be appreciated. Thanks in advance.
Comment
Watch Question

Commented:
Try using the BCP (Bulk Copy Program) to get data into a staging table in your database and then do a:
INSERT INTO TABLE
SELECT * From Staging table
Here is reference on BCP syntax and usage. You can also consult BOL for more information.
http://blogs.techrepublic.com.com/datacenter/?p=319
Hope this helps.
P.
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
How many columns are being inserted by these statements?  

Author

Commented:
Hello.
There were 17 columns per each insert statement.

I found a shareware tool that was able to split the file up into smaller files. This appears to have solved our issue. I don't like to reccommend freeware/shareware to people just in case it causes them issues. But just as reference, I used a trial download of 'Replace Pioneer' to spit the files. Thank you both for jumping in to assist.
The solution the author is using is, essentially, the solution I proposed.  Inserting the Go statement every 100 (or every X lines) would perform the equivalent of creating multiple insertion scripts without actually slitting the file.
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
@quomodo,
Were you ,perhaps, intending your comment for the author of the original post? ;-)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.