SSMS import large table

Question

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.

score 2 · Accepted Answer · edited Jun 11 '20 at 10:02

2

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

edited Jun 11 '20 at 10:02

Community

1

answered Apr 22 '12 at 23:45

Kev

7,877
18
81
108

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 – undefined Apr 23 '12 at 00:53
`bcp` ftw – Mark Henderson Apr 23 '12 at 01:56

score 1 · Answer 2 · answered Apr 23 '12 at 00:30

1

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

answered Apr 23 '12 at 00:30

mrdenny

27,174
4
41
69

SSMS import large table

2 Answers2