This is a dumping ground of software development topics that I've run across and found interesting (mainly .NET development). Many topics are covered more thoroughly in other places, and much of the information is gleaned from other places, and I'll try to credit those sources when possible.

Wednesday, August 05, 2009

SQL Server - Scripting table data

Scripting data is handy for lookup tables and I found just what I needed in this stored procedure and discussion. I copied the enhanced procedure below, just in case the link ever dies.


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

No comments:

Followers