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.

Answer 1

Use the Import/Export Data option in the Tasks menu for your database. You can then script you tables, sprocs, and other database objects.

Answer 2

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.

Answer 3

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.

Answer 4

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