Exporting data from a table to a SQL script

Question

In SQL Server 2005, is there a way I can create a INSERT script from a table that has several rows? A lot of times, while developing, I need to drop the table and recreate it. I end up having to manually add the rows that existed before. I have used SELECT FOR XML AUTO before dropping the table but it still needs some manual intervention before inserting the rows to the new table.

Solution 1

Grab the SSMS Tools Pack, which has a bunch of add-ons to SQL Server Management Studio. One of them includes an INSERT script generation, which should be right up your alley.

Solution 2

SELECT 'insert into table1 (fielda,fieldb,fieldc) values (''' + fielda + ''',''' + fieldb + ''',''' + fieldc + ''')' AS InsertCmd 
FROM table1

Solution 3

Another tool to consider is the MS Database Publishing Wizard. It is designed more for large scale database exports, but it also includes a SQL generation wizard.

Solution 4

I recommend MyGeneration. It is free code generation tool and it has a template to generate insert from database. You can select single or multiple tables.

Another method is a little more complex but it doesn't need additional tool. You can export data to another database and then use the import/export task to import them back when the test database is recreated. You can save the import task as SSIS package so you can just run the pacakge again and again.

View more solutions

Comments

[cdmdotnet]

The link you provided returns an error page, but the domain is right. ssmstoolspack.com