generate INSERT statements for a SQL Server table?
We are writing a new application, and while testing, we will need a bunch of dummy data. I've added that data by using MS Access to dump excel files into the relevant tables.
Every so often, we want to "refresh" the relevant tables, which means dropping them all, re-creating them, and running a saved MS Access append query.
The first part (dropping & re-creating) is an easy sql script, but the last part makes me cringe. I want a single setup script that has a bunch of INSERTs to regenerate the dummy data.
I have the data in the tables now. What is the best way to automatically generate a big list of INSERT statements from that dataset?
I'm thinking of something like in TOAD (for Oracle) where you can right-click on a grid and click Save As->Insert Statements, and it will just dump a big sql script wherever you want.
The only way I can think of doing it is to save the table to an excel sheet and then write an excel formula to create an INSERT for every row, which is surely not the best way.
I'm using the 2008 Management Studio to connect to a SQL Server 2005 database.
Microsoft should advertise this functionality of SSMS 2008. The feature you are looking for is built into the Generate Script utility, but the functionality is turned off by default and must be enabled when scripting a table.
This is a quick run through to generate the INSERT statements for all of the data in your table, using no scripts or add-ins to SQL Management Studio 2008:
You will then get the CREATE TABLE statement and all of the INSERT statements for the data straight out of SSMS.
We use this stored procedure - it allows you to target specific tables, and use where clauses. You can find the text here.
For example, it lets you do this: To generate INSERT statements for table 'titles':
EXEC sp_generate_inserts 'titles'
As mentioned by @Mike Ritacco but updated for SSMS 2008 R2
You will then get all of the INSERT statements for the data straight out of SSMS.
EDIT 2016-10-25 SQL Server 2016/SSMS 13.0.15900.1
Right click on the database name
Choose Tasks > Generate scripts
Depending on your settings the intro page may show or not
Choose 'Select specific database objects',
Expand the tree view and check the relevant tables
Click Next
Click Advanced
Under General section, choose the appropriate option for 'Types of data to script'
Click OK
Pick whether you want the output to go to a new query, the clipboard or a file
Click Next twice
Your script is prepared in accordance with the settings you picked above
Click Finish