Generate INSERT statements from a SQL Server Table

问题:

I have a table of 3.3 million records and don't want to copy the entire thing from dev to prod (on a client controlled machine and can't get the linked server working correctly).

I only want to copy 300 or so of these records. How do I generate the 300 insert statements?

My select SQL that I want the inserts for is:

select * from data where ID > 9000;

I want a query that will print out all the INSERTS so that I can copy and run it on the production box.

回答1:

I see you tagged your post SQL-Server-2005, that's too bad because version 2008 has a wizard tool for that.

You could build the insert statements out of concatenated strings.

If field1 is a string, field2 a numeric:

select 'insert into data (field1, field2) values('' || field1 || '', ' || char(field2) ||');' from data where ID < 9000;

Obviously that can be time-consuming if you have lots columns, considering that the strings needs quotes. You may have to convert the numeric columns using char() too.

That should give you a list of insert statements, like this:

insert into data (field1, field2) values('A', 10);
insert into data (field1, field2) values('B', 20);
insert into data (field1, field2) values('C', 30);

Maybe that's not the most elegant way to do this, but it works.

回答2:

insert into dev.data
select top 300 * from prod.data where ID > 9000;

or name your columns if you want to or have to (because of auto-increment columns)

insert into dev.data (col1, col2, col3)
select top 300 col1, col2, col3 from prod.data where ID > 9000;