How to Script the data of an SQL table using a Stored proc

<< Joel On Software | Making your ASP:Repeater a little bit DRYer >>

How to Script the data of an SQL table using a Stored proc

Filed under SQL

The Problem:
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.

The Solution:

CREATE Proc dbo.ScriptInsertStatements(
@TableName varchar(128),
@WhereClause varchar(400) = Null

)
AS

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 + '+ '','' + ','') +
CASE
WHEN Data_Type in ('varchar','nvarchar', 'char', 'nchar', 'datetime' ) THEN 'IsNull(QuoteName(' + Column_Name + ', ''''''''), ''NULL'')'
ELSE 'IsNull(Cast(' + Column_Name + ' as varchar(MAX)), ''NULL'')'
END
FROM
Information_Schema.Columns where Table_name = @TableName

SELECT @sql = 'Select ''Insert Into ' + @TableName + ' (' + @SqlColumns + ') Values ('' + ' + @SqlColumnValues +' + '')'' FROM ' + @TableName + IsNull(' WHERE ' + @WhereClause, '')

Exec(@sql)

To use the above stored proc simply call it passing the Table Name
e.g.
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'

Beyond SoundEx - Functions for Fuzzy Searching in MS SQL ServerQuite often we come across a requirement where we may need to perform some sort of fuzzy string grou...SQL Server script to auto-create indexes on all Foreign Key ColumnsIf you want a quick way to set some default indexes up in your database the best candidates for your...Regular Expressions In MS SQL Server using CLRIn this post I'll show just how easy it is to make your SQL Server 2005 database support Regular Exp...

Comments (7) -

9/10/2007 4:35:07 PM #

Man you don't even know how long I've waited for this since disabling my own Movable Type widget (that doesn't work since Haloscan bypasses that code).

THANK YOU!

Tim

2/19/2008 11:20:49 AM #

You really rocks!!!!!! I too was in need of such thing.

THANK YOU VERY VERY MUCH

Kiran Tikare

6/6/2008 2:53:08 PM #

The following is an updated script that handles null values:

ALTER Proc dbo.kpm_ScriptInsertStatements(
      @TableName varchar(128),
      @WhereClause varchar(400) = Null

)
AS

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

Print @sqlColumns

SELECT @SqlColumnValues = Coalesce(@SqlColumnValues + '+  '','' + ','') +
      CASE
            WHEN Data_Type in ('varchar','nvarchar', 'char', 'nchar', 'datetime' ) THEN  'ISNULL(QuoteName(' + Column_Name + ', ''''''''), ''NULL'')'
            ELSE 'ISNULL(Cast(' + Column_Name + ' as varchar(MAX)), ''NULL'')'
      END
FROM
      Information_Schema.Columns where Table_name = @TableName

SELECT @sql = 'Select ''Insert Into ' + @TableName + ' (' + @SqlColumns + ') Values ('' + ' + @SqlColumnValues +' + '')'' FROM ' + @TableName + IsNull(' WHERE ' + @WhereClause, '')

exec(@sql)

Jeremy

6/12/2008 8:36:52 AM #

Thanks for posting your updated version Jeremy, much more practical, I have also been using an updated version of the script, I should have posted the changes back to the blog!