Scripting large tables to .sql file

Question

Don't ask me why, but I have to migrate a database from 2008 to 2005. This is not an issue of itself, but I have a very large table.

When I script the tables contents (using Generate Scripts), the .sql file made is over 4gb. This is more memory than the server has available in RAM.

Is there anyway to generate insert commands that splits into multiple files?

Or is there a way to split a file into multiple files with the expectation that the file is the larger than the amount of RAM available?

@OMG Ponies -- Are you thinking of DTS instead of scripting? — LittleBobbyTables - Au Revoir
– LittleBobbyTables - Au Revoir, Commented Sep 3, 2010 at 23:56
This isn't really a programming question. I think serverfault.com is a better venue for this question. — Gabe
– Gabe, Commented Sep 4, 2010 at 0:11
You could make this into a programming project, but it's really a sysadmin/DBA question. — Gabe
– Gabe, Commented Sep 4, 2010 at 1:08

gbn · Accepted Answer · 2010-09-04 12:20:34Z

1

Why script the data out?

I'd use SSIS or some other programmatical method after scripting/generating my schema.

Or use something like Red Gate Compare tools

I've almost never generated DML scripts this way.

However, SSMS tools pack does offer batched INSERT generation and it's free

answered Sep 4, 2010 at 12:20

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

+1: I was looking to see if Linked Servers could be used...