CODEWITHSUNDEEP
sqlsql-serversql-server-2005
cdub
cdub

Reputation: 25701

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.

Upvotes: 3

Views: 5519

Answers (1)

Danny T.
Danny T.

Reputation: 1140

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.

Upvotes: 2

Related Questions