Filed under SQL
I find myself quite often in the following situation: I add a couple of tables in a development database, add some data in that table
and then I need to script the changes and apply them to the staging and then to the production db.
Option 1. Generating data scripts through SQL Server Management Studio
In Sql Server Management Studio 2008 it is possible to export all data, or data to selected tables as scripts.
In order to accomplish this, from SQL Server Management studio, select your target database that you would like to export and right
click on it.
From there, select Tasks > Generate Scripts...
If this is the first time you start this wizzard you will be greeted with a welcome screen. Simply click next on that to move onto
the scripting options.
Before you export, make sure to click on advanced.
In order to script your data, you will need to ensure that the option Shema and data is selected from the types of data
to script option. The default is Schema only, so if you do not change it, your data will not be scripted.
You can also see that there are also other useful options such as, which version of sql server should the exported script target.
If collation and logons should also be scripted (which are switched off by default)
Option 2. Generating data scripts with an SQL Script
There are two reasons that you might favour this approach
- You do not have access to your DB via SQL Server management studio
- Rather than exporting all your data, you would rather generate data scripts based on a where clause to selectively
include rows in the script.
For these reasons you might prefer to use the following SQL Script:
The script works on SQL 2005 since it makes use of the coalesce function
Here you go:
CREATE Proc dbo.ScriptInsertStatements(
@WhereClause varchar(400) = Null
DECLARE @sql varchar(Max)
DECLARE @sqlColumns varchar(Max)
DECLARE @SqlColumnValues varchar(Max)
SELECT @sqlColumns = Coalesce(@SqlColumns + ',','') + Column_Name from Information_Schema.Columns where Table_name = @TableName
SELECT @SqlColumnValues = Coalesce(@SqlColumnValues + '+ '','' + ','') +
WHEN Data_Type in ('varchar','nvarchar', 'char', 'nchar', 'datetime' ) THEN 'IsNull(QuoteName(' + Column_Name + ', ''''''''), ''NULL'')'
ELSE 'IsNull(Cast(' + Column_Name + ' as varchar(MAX)), ''NULL'')'
Information_Schema.Columns where Table_name = @TableName
SELECT @sql = 'Select ''Insert Into ' + @TableName + ' (' + @SqlColumns + ') Values ('' + ' + @SqlColumnValues +' + '')'' FROM ' + @TableName + IsNull(' WHERE ' + @WhereClause, '')
To use the above stored proc simply call it passing the Table Name
Exec dbo.ScriptInsertStatements 'Users'
You may also want to partially script a table based on a condition. You can supply that condition in the whereclause like so:
--only script insert statements of Users Table for records with field 'Active' set to 1
Exec dbo.ScriptInsertStatements 'Users', 'Active=1'