Solved

Insert statement causing out of memory error

Posted on 2009-07-10
8
293 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

689 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