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

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

  1. You do not have access to your DB via SQL Server management studio
  2. Rather than exporting all your data, you would rather generate data scripts based on a where clause to selectively include rows in the script.
  3. For these reasons you might prefer to use the following SQL Script:
    The Solution:
    The script works on SQL 2005 since it makes use of the coalesce function
    Here you go:

    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'  

Comments (7) -

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

Tim

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 #

Kiran Tikare

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 #

Jeremy

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! Smile

Anastasiosyal

8/4/2008 6:37:09 PM #

Scott

OK I am an idiot - I get no output.  It runs no errors - no output.  What am I missing????

Scott

8/4/2008 6:43:50 PM #

Scott

Never mind - I dod not realize the table name did not need the traditional 'dbo.' in front of it.  Sorry - I am an idiot.

Scott

11/9/2008 10:22:27 PM #

Jane

this statement is also helpful

select * into <new table> from <tablesource>


it will insert all the data to the new created table..

Jane

Comments are closed