Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm trying to move several tables of data between 2 MSSQL 2008 databases, I have generated data insert scripts for one table which is quite large (around 3.5M rows) The script is around 3GB. When I try and open the script with SSMS I get the following error:

enter image description here

Is there a way to get SSMS to let me open this script or to export the data into some other format which is easier to import.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

Your first big problem is that you're trying to load 3GB of data as a script into SSMS. I'm not sure how clever SSMS is but I bet it's trying to load the whole file into memory which will cause it to blow up because it's a 32 bit app - 32 bit apps have a max address space of 4GB of which 2GB is available to the process.

You'd be better off using the bulk import/export tools for this job, they're more suited for moving large blobs of data around:

About Bulk Import and Bulk Export Operations

You probably also don't want (I suspect) to run this as a transactional insert either:

Optimizing Bulk Import Performance

Prerequisites for Minimal Logging in Bulk Import

share|improve this answer
    
I think you're right, I'm going about it wrong exporting as a script, ill take a look at bulk import/export stuff and try that way –  Luke McGregor Apr 23 '12 at 0:53
    
bcp ftw​​​​​​ –  Mark Henderson Apr 23 '12 at 1:56

You can try running this script using sqlcmd from the command line.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.