sql-server - with - stored procedure to generate insert script
What is the best way to auto-generate INSERT statements for a SQL Server table? (13)
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?
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.
GenerateData is an amazing tool for this. It's also very easy to make tweaks to it because the source code is available to you. A few nice features:
- Name generator for peoples names and places
- Ability to save Generation profile (after it is downloaded and set up locally)
- Ability to customize and manipulate the generation through scripts
- Free. But consider donating if you find the software useful :).
As mentioned by @Mike Ritacco but updated for SSMS 2008 R2
- 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'
- Complete the wizard
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
Under General section, choose the appropriate option for 'Types of data to script'
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
Don't use inserts, use BCP
I have also researched lot on this, but I could not get the concrete solution for this. Currently the approach I follow is copy the contents in excel from SQL Server Managment studio and then import the data into Oracle-TOAD and then generate the insert statements
I used this script which I have put on my blog (How-to generate Insert statement procedures on sql server).
So far has worked for me, although they might be bugs I have not discovered yet .
I'm using SSMS 2008 version 10.0.5500.0. In this version as part of the Generate Scripts wizard, instead of an Advanced button, there is the screen below. In this case, I wanted just the data inserted and no create statements, so I had to change the two circled properties
Jane Dallaway's stored procedure: http://docs.google.com/leaf?id=0B_AkC4ZdTI9tNWVmZWU3NzAtMWY1My00NjgwLWI3ZjQtMTY1NDMxYzBhYzgx&hl=en_GB. Documentation is a series of blog posts: https://www.google.com/search?q=spu_generateinsert&as_sitesearch=http%3A%2F%2Fjane.dallaway.com
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:
- Right-click on the database and go to Tasks > Generate Scripts.
- Select the tables (or objects) that you want to generate the script against.
- Go to Set scripting options tab and click on the Advanced button.
- In the General category, go to Type of data to script
- There are 3 options: Schema Only, Data Only, and Schema and Data. Select the appropriate option and click on OK.
You will then get the
CREATE TABLE statement and all of the
INSERT statements for the data straight out of SSMS.
Not sure, if I understand your question correctly.
If you have data in MS-Access, which you want to move it to SQL Server - you could use DTS.
And, I guess you could use SQL profiler to see all the INSERT statements going by, I suppose.
Perhaps you can try the SQL Server Publishing Wizard http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en
It has a wizard that helps you script insert statements.
This can be done using
Visual Studio too (at least in version 2013 onwards).
In VS 2013 it is also possible to filter the list of rows the inserts statement are based on, this is something not possible in SSMS as for as I know.
Perform the following steps:
- Open the "SQL Server Object Explorer" window (menu: /View/SQL Server Object Explorer)
- Open / expand the database and its tables
- Right click on the table and choose "View data" from context menu
- This will display the data in the main area
- Optional step: Click on the filter icon "Sort and filter data set" (the fourth icon from the left on the row above the result) and apply some filter to one or more columns
- Click on the "Script" or "Script to File" icons (the icons on the right of the top row, they look like little sheets of paper)
This will create the (conditional) insert statements for the selected table to the active window or file.
The "Filter" and "Script" buttons Visual Studio 2013:
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:
EXEC sp_generate_inserts 'titles'
why not just backup the data before your work with it, then restore when you want it to be refreshed?
if you must generate inserts try: http://vyaskn.tripod.com/code.htm#inserts