Solved

Insert statement causing out of memory error

Posted on 2009-07-10
8
290 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.
0
Comment
Question by:jvoconnell
  • 4
  • 2
8 Comments
 
LVL 17

Expert Comment

by:pssandhu
ID: 24827312
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.
0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 250 total points
ID: 24836606
Or, you could try inserting the "GO statment after about every 100 lines.  
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24836610
How many columns are being inserted by these statements?  
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 1

Author Comment

by:jvoconnell
ID: 24840156
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.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24840394
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.
0
 
LVL 1

Accepted Solution

by:
jvoconnell earned 0 total points
ID: 24840615
Hello all,

I didn't mean to offend or slight anyone. This site has been a great resoucre. I honestly thought I was doing the right thing by closing out the question because I didn't use anyones suggestions. I had found the file splitter late Friday afternoon and I really do aplogize but I didn't get back online  until this morning. I can see the point made by 8080 Diver. I'm not sure how EE likes to handle these objections but I'm open to anything. As I said, I just thought I was doing the right thing.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24859529
@quomodo,
Were you ,perhaps, intending your comment for the author of the original post? ;-)
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question