• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

Trouble Scripting large table

Hello All,

I have a remote SQL server (SQL Server 2000) which has a table that has nearly 60,000 records. I am able to script this table but cannot execute it locally, so as to replicate the table for local development.

I am using SQL server2008R2 Express. Thinking it was the Express version which was limiting me, I tried the same thing on a proper SQL server 2008, but still I get error when executing the script.

The script is 147MB but compresses down to 2mb when zipped.

On My Local SQL the error is “No able to execute the script”  ‘system.OutOfMemeryException’ (mscorlib)

For years I could script this table with no issues.

I have attached a script to create the table only, so as to allow you to see the structure.

Thanks in advance.
Abiel de Groot
Abiel de Groot
  • 3
  • 3
1 Solution
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
You could try to use the BCP to move the data instead:

Regards Marten
How do you actually import the table to SQL 2008? Are you using a wizard, some ETL, you scripted the structure and data?

The script you attached, which contains only the structure, worked fine on my SQL2008 version. I assume you have issues with the data.
Abiel de GrootDeveloperAuthor Commented:
I am using SSMS : Tasks > Generate script.

When I include data it will net let me execute the script. However, it did until recently.

The script, with the data is 147MB, but zips to 2mb!

Any ideas.
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Abiel de GrootDeveloperAuthor Commented:
I have sent you the script with the data. Its not particularly sensitive data but probably best not to post it here for all to see.

Kind regards

The issue is that the script is too big to be executed like that. You should create the table first and then break the INSERT statements in at least 4 parts(quarters) and try it that way.

So the idea is to execute teh create table part first and them the inserts in separate batches. Make sure you include the statements from the end of the original file, the ALTER TABLE ones.
If you have the Managemet Studio I suggest you to create the table on the SQL2008EX first and then expand the server node > right click on the database > Tasks > Import Data > folow the wizard but when you are at mapping part make sure you click on Edit Mappings and you have checked the "Enable Identity Insert" option.

If you don't have MS then download it fromn here:


It's Express version and it's free.

Install instructions here: http://www.youtube.com/watch?v=2xxa7fSBSD0
Abiel de GrootDeveloperAuthor Commented:
many thanks. I will do it that way.
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.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now