Wednesday, April 25, 2007 10:40 PM,
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.
If you dont create everything by script SQL Server Management Studio can script the Create Table for us, but there is no way to script the data! For this reason I created a stored procedure, which yeah you guessed it, creates all the INSERT statements containing the data of the table.
The Solution:
The following script works on SQL 2005 since it makes use of the coalesce function (sql 2000 fix follows script)
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 'QuoteName(' + Column_Name + ', '''''''')'
ELSE 'Cast(' + Column_Name + ' as varchar(MAX))'
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'