CODEWITHSUNDEEP
sql-server-2005importexport
Bob Smith
Bob Smith

Reputation:

Exporting data from a table to a SQL script

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.

Upvotes: 3

Views: 13344

Answers (6)

Mandoleen
Mandoleen

Reputation: 2661

try SQL Formatter Add-In for SSMS

Upvotes: 0

Dillie-O
Dillie-O

Reputation: 29725

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.

Upvotes: 2

Dillie-O
Dillie-O

Reputation: 29725

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.

Upvotes: 8

Kosta
Kosta

Reputation: 518

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

Upvotes: 2

Ken Yao
Ken Yao

Reputation: 1516

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.

Upvotes: 1

TheTXI
TheTXI

Reputation: 37875

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

Upvotes: 0

Related Questions