SSMS import large table

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:

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.

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

Check more discussion of this question.

Related posts:

1. How do I export/backup data from a SQL table, add a nullable column to the table and import/restore the data?
2. Fast Bulk Import of a Large Dataset into MySQL
3. Export SSMS database connections to a new version
4. Can I use the export/import data in SQL 2008 with FK constrained tables?
5. sharepoint 3.0 site restore/import trouble