Solved

Insert statement causing out of memory error

Posted on 2009-07-10
8
289 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query - which index being used? 2 48
passing parameter in sql procedure 9 57
Passing value to a stored procedure 8 93
Error when saving to sql table a '/' 5 27
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
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 …
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 …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now