Solved

Insert statement causing out of memory error

Posted on 2009-07-10
8
288 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
Comment Utility
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
Comment Utility
Or, you could try inserting the "GO statment after about every 100 lines.  
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
How many columns are being inserted by these statements?  
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 1

Author Comment

by:jvoconnell
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
@quomodo,
Were you ,perhaps, intending your comment for the author of the original post? ;-)
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

743 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

16 Experts available now in Live!

Get 1:1 Help Now