jvoconnell
asked on
Insert statement causing out of memory error
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
How many columns are being inserted by these statements?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@quomodo,
Were you ,perhaps, intending your comment for the author of the original post? ;-)
Were you ,perhaps, intending your comment for the author of the original post? ;-)
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.
Hope this helps.
P.