how to run a large SQL Query? "Insufficient memory"

I have a 50MB .sql file.  It loads easily enough into SQL Management studio but I cannot execute it, it spits out an error that there is insufficient memory to parse the script.   Any ideas how I can run this?

SQL 2008
arthurh88Asked:
Who is Participating?
 
dsackerConnect With a Mentor Contract ERP Admin/ConsultantCommented:
You can split the file into parts, perhaps each 10-15 MB in Size. If you have any CREATE TABLE or statements at the top other than INSERTs, which prep the way, leave them. Then break at 20000 inserts. Then create a 2nd file with the next 20000 inserts, and so on. Execute them one after the other.
0
 
dsackerContract ERP Admin/ConsultantCommented:
Just guessing here: If you have embedded INSERT statements, why not change your processing to load your data into a text file and BCP it into SQL.

I've been in DBA and SQL work for over 35 years, and I have yet to write a SQL file that has reached 1MB. What are you doing that must make this 50MB?
0
 
Kent DyerIT Security Analyst SeniorCommented:
Couple of things that come to mind..

Split the script into "bite-sized" pieces.

50 MB SQL File?  Are you inserting or creating a ton of data?  It would be recommended to insert or link to the other database if you are using temp tables.

SELECT TOP 1 from Table
would trim down the results.

HTH,

Kent
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
arthurh88Author Commented:
yes they are 80,000 insert statements.   i dont know what you mean by BCP it or how to load it into a text file, other than renaming it to .txt....?

it is a ZIP CODE database that i just purchased for 140 bucks.
0
 
arthurh88Author Commented:
i guess i could cut and paste chunks of it at a time, but PLEASE tell me there is an easier way lol
0
 
dsackerContract ERP Admin/ConsultantCommented:
Did you get your database (that you purchased) by chance just like that SQL, or did they also give you an Access version of it? Because if you got an Access database of it, you can export that right into SQL Server.
0
 
Kent DyerIT Security Analyst SeniorCommented:
BCP would be good..  Another way would be OSQL..  For example:

osql -SINSTANCE\SERVERS -Uuser -Ppassword -n -iyour_sql_file.sql -oresult.log -w250

Open in new window


You could do it as part of a DTSX package to import.

HTH,

Kent
0
 
dsackerContract ERP Admin/ConsultantCommented:
If OSQL blows, try SQLCMD. Use everything kdyer showed you in his code, just substitute sqlcmd for osql.
0
 
arthurh88Author Commented:
this is exactly what im doing, and its working...although....i dont like the manual work lol.  but yes, thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.