Anastasiosyal.com

# re: How to Script the data of an SQL table using a Stored proc, Posted by Tim on 9/10/2007 10:35 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!

# re: How to Script the data of an SQL table using a Stored proc, Posted by Kiran Tikare on 2/19/2008 5:20 PM

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

THANK YOU VERY VERY MUCH

# re: How to Script the data of an SQL table using a Stored proc, Posted by Jeremy on 6/6/2008 8:53 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)

# re: How to Script the data of an SQL table using a Stored proc, Posted by Anastasiosyal on 6/12/2008 2:36 PM

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! :)

# re: How to Script the data of an SQL table using a Stored proc, Posted by Scott on 8/5/2008 12:37 AM

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

# re: How to Script the data of an SQL table using a Stored proc, Posted by Scott on 8/5/2008 12:43 AM

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

# re: How to Script the data of an SQL table using a Stored proc, Posted by Jane on 11/10/2008 4:22 AM

this statement is also helpful

select * into <new table> from <tablesource>


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

# re: How to Script the data of an SQL table using a Stored proc, Posted by santoo on 1/29/2009 7:53 AM

Hi All,

Really you are greate yar,

Thank you very much for sharing this.

# re: How to Script the data of an SQL table using a Stored proc, Posted by Michael Newell on 2/24/2009 8:55 PM

This is a great and useful stored procedure. I use it quite a bit in order to copy tables containing static data from one database to another. In order to make things easier, I have modified the procedure to work with Identity columns and also provide a way to sort the data for copying.

CREATE PROCEDURE dbo.spScriptInsertStatements (
@TableName varchar(100),
@WhereClause varchar(1000) = NULL,
@OrderBy varchar(1000) = NULL
)
AS

DECLARE @sql nvarchar(MAX)
DECLARE @sqlColumns varchar(MAX)
DECLARE @sqlColumnValues varchar(MAX)
DECLARE @hasIdentity bit
DECLARE @tbl TABLE (
SortOrder int IDENTITY(1, 1) NOT NULL,
SQLText varchar(MAX) NULL
)

SET @hasIdentity = 0

SELECT @sqlColumns = COALESCE(@SqlColumns + ', ','') + COLUMN_NAME,
@hasIdentity = CASE @hasIdentity
WHEN 0 THEN COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity')
ELSE 1 END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND (@OrderBy IS NULL OR COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 0)

SELECT @SqlColumnValues = COALESCE(@SqlColumnValues + '+ '', '' + ','') + CASE
WHEN DATA_TYPE IN ('varchar','nvarchar', 'char', 'nchar', 'datetime' )
THEN 'COALESCE(QuoteName(' + Column_Name + ', ''''''''), ''NULL'')'
ELSE 'COALESCE(CAST(' + Column_Name + ' AS varchar(MAX)), ''NULL'')' END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND (@OrderBy IS NULL OR COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 0)

SELECT @sql = 'SELECT ''INSERT INTO ' + @TableName + ' (' + @SqlColumns + ') ' +
'VALUES ('' + ' + @SqlColumnValues +' + '')'' AS SQLText FROM ' + @TableName +
COALESCE(' WHERE ' + @WhereClause, '') + COALESCE(' ORDER BY ' + @OrderBy, '')

-- If table contains an Identity column and sorting was not specified
IF @hasIdentity = 1 AND @OrderBy IS NULL
BEGIN
INSERT INTO @tbl (SQLText) VALUES ('SET IDENTITY_INSERT ' + @TableName + ' ON ')
INSERT INTO @tbl (SQLText) VALUES ('GO ')
END

INSERT INTO @tbl (SQLText)
EXEC sp_executesql N'EXEC sp_executesql @statement = @sql', N'@sql nvarchar(MAX)', @sql

-- If table contains an Identity column and sorting was not specified
IF @hasIdentity = 1 AND @OrderBy IS NULL
BEGIN
INSERT INTO @tbl (SQLText) VALUES ('GO ')
INSERT INTO @tbl (SQLText) VALUES ('SET IDENTITY_INSERT ' + @TableName + ' OFF ')
INSERT INTO @tbl (SQLText) VALUES ('GO ')
END

SELECT SQLText FROM @tbl ORDER BY SortOrder

-- Unit Test
--
-- EXEC spScriptInsertStatements @TableName = 'glsBatchType'
-- EXEC spScriptInsertStatements @TableName = 'glsBatchType', @WhereClause = 'glsTypeSeq < 3'
-- EXEC spScriptInsertStatements @TableName = 'glsBatchType', @OrderBy = 'glsTypeCode DESC'
--

GO

# re: How to Script the data of an SQL table using a Stored proc, Posted by Bithika on 3/27/2009 8:11 AM

You guys saved a great deal of my time. Thanks so much.
:)

# re: How to Script the data of an SQL table using a Stored proc, Posted by Andre St-Clair Simon on 4/10/2009 4:55 PM

Great utility!
Thank you for sharing this.

# re: How to Script the data of an SQL table using a Stored proc, Posted by Phil Koay on 12/13/2009 10:50 PM

Thanks for sharing guys! this saved me a bundle of time. In addition to the Identity and sort order (above) I added 'ignore timestamp',turned off Count and also added the brakets around table and field names.

ALTER PROCEDURE [dbo].[spScript_Data](
@TableName varchar(128),
@WhereClause varchar(400) = Null,
@OrderByClause varchar(400) = Null
)
AS
SET NOCOUNT ON
DECLARE @sql varchar(Max)
DECLARE @sqlColumns varchar(Max)
DECLARE @SqlColumnValues varchar(Max)
DECLARE @HasIdentity bit

--Determine Columns
SELECT @sqlColumns = Coalesce(@SqlColumns + ',','') + '['+Column_Name+']' from Information_Schema.Columns where Table_name = @TableName AND Data_Type<>'Timestamp'

--Determine Values (representation/casting)
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 AND Data_Type<>'Timestamp'


--Build SQL String
SELECT @sql = 'Select ''Insert Into [' + @TableName + '] (' + @SqlColumns + ') Values ('' + ' + @SqlColumnValues +' + '')'' FROM [' + @TableName +']'+ IsNull(' WHERE ' + @WhereClause, '')+ IsNull(' ORDER BY ' + @OrderByClause, '')

-- Determine if Identity field exists
SELECT @HasIdentity=MAX(COLUMNPROPERTY(OBJECT_ID(@TableName),Column_Name,'IsIdentity')) from Information_Schema.Columns where Table_name = @TableName AND Data_Type<>'Timestamp'


--Output
PRINT 'SET NOCOUNT ON'
IF @HasIdentity=1 PRINT 'SET IDENTITY_INSERT '+@TableName+' ON'
PRINT 'PRINT '' ADDING Data to '+@TableName+' '' '
exec(@sql)
IF @HasIdentity=1 PRINT 'SET IDENTITY_INSERT '+@TableName+' OFF'