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 GrootDeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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:
Abiel de GrootDeveloperAuthor Commented:
many thanks. I will do it that way.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.