2012-04-02

Code Contribution: T-SQL INSERT Generator

Good news everybody! Today, I am releasing a helpful T-SQL Script that I use for generating test data from another environment. You'll run this SQL Script against an existing database table with data that you'd like to replicate. It'll generate an INSERT statement that inserts data using a collection of SELECT, UNION ALL clauses.

Here are the important things to know when using this Script:

  1. It will run for all data in your table. Large tables can take forever. I've tested this for ~8000 or so records with success (slow, but success). I've added some safeguards to minimize any database performance hits including the use of NOLOCK, and prompting you with a a nice warning before executing.
  2. At the very top, is @TableSchema and @TableName, you'll want to assign those as appropriate. I use INFORMATION_SCHEMA.COLUMNS and INFORMATION_SCHEMA.TABLES heavily in this script, so any objects that you'll want to tinker with will need to be available in those system views.
  3. There's a @Debug variable that when active, turns on a few extra datasets and print statements. This may be helpful if you're doing any major work to this.
  4. By default, this PRINTs out the INSERT script. You'll want to copy and paste this into another window, most likely.
  5. There are a handful of data types that I have not tested/supported. Here are the ones that I have tested with:
    'char', 'nchar', 'nvarchar', 'varchar', 'bigint', 'decimal', 'float', 'int', 'money', 'numeric', 'smallint', 'tinyint', 'real', 'uniqueidentifier', 'datetime', 'smalldatetime', 'bit'
    Some notable exceptions include text, ntext, varbinary, and timestamp, and I'm sure there are others out there.
  6. The output can be large. You might be better served by running the INSERT script from a command prompt rather than SQL Management Studio. There was one 2gb file that I could not run from anything but the command prompt.
  7. This is released with no defined license, warranty, or EULA. Use it how you'd like, as you'd like, and modify it as you may. I won't / can't support it, but I hope that my example proves helpful to you.
Conversely, I'd like to at some point add support to SQL 2008's upsert functionality...

Here's the code!
SET NOCOUNT ON
DECLARE @TableSchema VARCHAR(50)
, @TableName VARCHAR(50)
, @DEBUG BIT

-- SETUP
SET @TableSchema = 'dbo'
SET @TableName = 'CustomObject'
SET @DEBUG = 0



-------------- DO NOT MODIFY ANYTHING BELOW THIS LINE --------------
IF OBJECT_ID('tempdb..#DataExtractWarning') IS NULL
BEGIN
-------------- Warning Message --------------
CREATE TABLE #DataExtractWarning ( RecordCount BIGINT )
EXECUTE ('INSERT INTO #DataExtractWarning ( RecordCount ) SELECT COUNT(*) FROM [' + @TableSchema + '].[' + @TableName + ']')
DECLARE @RecordCount BIGINT
SELECT @RecordCount = RecordCount
FROM #DataExtractWarning
PRINT 'WARNING: You are trying to extra data from [' + @TableSchema + '].[' + @TableName + ']'
PRINT 'This will query approximately ' + CAST(@RecordCount AS VARCHAR(50)) + ' records, and will likely involve quite a bit of waiting'
PRINT 'Recommendation: Specify Results to File'
PRINT 'Proceed with EXTREME caution'
PRINT '... Press F5 to Continue ...'
END
ELSE
BEGIN
-------------- Actual work --------------
DECLARE @ColumnCount BIGINT
, @FirstColumn VARCHAR(50)

SELECT @ColumnCount = MAX(col.ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS col WITH (NOLOCK)
WHERE col.TABLE_SCHEMA = @TableSchema
AND col.TABLE_NAME = @TableName

SELECT TOP 1
@FirstColumn = col.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS col WITH (NOLOCK)
WHERE col.TABLE_SCHEMA = @TableSchema
AND col.TABLE_NAME = @TableName
AND col.ORDINAL_POSITION = 1

DECLARE @Selects TABLE
(
tsql VARCHAR(MAX)
)

INSERT INTO @Selects
( tsql )
SELECT 'SELECT '
UNION ALL
SELECT 'ROW_NUMBER() OVER (ORDER BY [' + @FirstColumn + ']) AS RowNumber'
UNION ALL
SELECT
CASE WHEN col.DATA_TYPE IN ( 'char', 'nchar', 'nvarchar', 'varchar', 'bigint', 'decimal', 'float', 'int', 'money', 'numeric', 'smallint', 'tinyint', 'real', 'uniqueidentifier', 'datetime', 'smalldatetime', 'bit' )
THEN 
' ' + CASE WHEN col.ORDINAL_POSITION > 1 THEN '+ ' ELSE ',''SELECT '' + ' END +
CASE
WHEN col.IS_NULLABLE = 'NO'
THEN
CASE
WHEN col.DATA_TYPE IN ( 'char', 'nchar', 'nvarchar', 'varchar' )
THEN ''''''''' + REPLACE([' + col.COLUMN_NAME + '], '''''''', '''''''''''') + '''''''''
WHEN col.DATA_TYPE IN ( 'bigint', 'decimal', 'float', 'int', 'money', 'numeric', 'smallint', 'tinyint', 'real' )
THEN 'CAST([' + col.COLUMN_NAME +'] AS NVARCHAR(50))'
WHEN col.DATA_TYPE IN ( 'image', 'varbinary' )
THEN ''
WHEN col.DATA_TYPE IN ( 'uniqueidentifier' )
THEN ''''''''' + CAST([' + col.COLUMN_NAME + '] AS NVARCHAR( 50)) + '''''''''
WHEN col.DATA_TYPE IN ( 'datetime', 'smalldatetime' )
THEN ''''''''' + CONVERT(varchar, [' + col.COLUMN_NAME + '], 126) + '''''''''
WHEN col.DATA_TYPE IN ( 'bit' )
THEN 'CAST([' + col.COLUMN_NAME + '] AS NVARCHAR(50))'
WHEN col.DATA_TYPE IN ( 'text', 'ntext', 'xml' )
then ''
ELSE
CASE
WHEN @DEBUG = 1
THEN '/*' + col.DATA_TYPE + '*/'
ELSE ''
END
END
WHEN col.IS_NULLABLE = 'YES'
THEN
CASE
WHEN col.DATA_TYPE IN ( 'char', 'nchar', 'nvarchar', 'varchar' ) --'text', 'ntext', 'xml' 
THEN 'CASE WHEN [' + col.COLUMN_NAME +'] IS NOT NULL THEN '''''''' + REPLACE([' + col.COLUMN_NAME + '], '''''''', '''''''''''') + '''''''' ELSE ''NULL'' END'
WHEN col.DATA_TYPE IN ( 'bigint', 'decimal', 'float', 'int', 'money', 'numeric', 'smallint', 'tinyint', 'real' )
THEN 'CASE WHEN [' + col.COLUMN_NAME +'] IS NOT NULL THEN CAST([' + col.COLUMN_NAME +'] AS NVARCHAR(50)) ELSE ''NULL'' END'
WHEN col.DATA_TYPE IN ( 'image', 'varbinary' )
THEN ''
WHEN col.DATA_TYPE IN ( 'uniqueidentifier' )
THEN 'CASE WHEN [' + col.COLUMN_NAME +'] IS NOT NULL THEN '''''''' + CAST([' + col.COLUMN_NAME +'] AS NVARCHAR(50)) + '''''''' ELSE ''NULL'' END'
WHEN col.DATA_TYPE IN ( 'datetime', 'smalldatetime' )
THEN 'CASE WHEN [' + col.COLUMN_NAME +'] IS NOT NULL THEN '''''''' + CONVERT(varchar, [' + col.COLUMN_NAME + '], 126) + '''''''' ELSE ''NULL'' END'
WHEN col.DATA_TYPE IN ( 'bit' )
THEN 'CASE WHEN [' + col.COLUMN_NAME +'] IS NOT NULL THEN CAST([' + col.COLUMN_NAME +'] AS NVARCHAR(50)) ELSE ''NULL'' END'
WHEN col.DATA_TYPE IN ( 'text', 'ntext', 'xml' )
then ''
ELSE 
CASE
WHEN @DEBUG = 1
THEN '/*' + col.DATA_TYPE + '*/'
ELSE ''
END
END
ELSE ''
END + 
CASE WHEN col.ORDINAL_POSITION < @ColumnCount THEN ' + '', '' + ' ELSE ' AS [select]' END
ELSE
''
END
FROM INFORMATION_SCHEMA.COLUMNS col
WHERE col.TABLE_SCHEMA = @TableSchema
AND col.TABLE_NAME = @TableName

UNION ALL

SELECT 'FROM [' + tab.TABLE_SCHEMA + '].[' + tab.TABLE_NAME + ']  WITH (NOLOCK)'
FROM INFORMATION_SCHEMA.TABLES tab WITH (NOLOCK)
WHERE tab.TABLE_SCHEMA = @TableSchema
AND tab.TABLE_NAME = @TableName

--SELECT ')'

DECLARE @tsql VARCHAR(MAX) 
SELECT @tsql = COALESCE(@tsql + ' 
', '') + tsql
FROM @Selects

IF @DEBUG = 1
BEGIN
SELECT * FROM @Selects
PRINT @tsql
END

DELETE FROM @Selects

INSERT INTO @Selects

SELECT 'INSERT INTO [' + tab.TABLE_SCHEMA + '].[' + tab.TABLE_NAME + ']'
FROM INFORMATION_SCHEMA.TABLES tab WITH (NOLOCK)
WHERE tab.TABLE_SCHEMA = @TableSchema
AND tab.TABLE_NAME = @TableName
UNION ALL
SELECT '('
UNION ALL
SELECT
CASE WHEN col.DATA_TYPE IN ( 'char', 'nchar', 'nvarchar', 'varchar', 'bigint', 'decimal', 'float', 'int', 'money', 'numeric', 'smallint', 'tinyint', 'real', 'uniqueidentifier', 'datetime', 'smalldatetime', 'bit' )
THEN ''
ELSE '-- [Currently Unable to Export ' + col.DATA_TYPE + ']'
END + 
CASE WHEN col.ORDINAL_POSITION > 1 THEN ',' ELSE '' END + ' [' + col.COLUMN_NAME + ']'
FROM INFORMATION_SCHEMA.COLUMNS col WITH (NOLOCK)
WHERE col.TABLE_SCHEMA = @TableSchema
AND col.TABLE_NAME = @TableName
UNION ALL
SELECT ')'

IF OBJECT_ID('tempdb..#T1') IS NOT NULL
BEGIN
DROP TABLE #T1
END
CREATE TABLE #T1 ( rownum BIGINT , tsql varchar(MAX) )
INSERT INTO #T1
EXECUTE (@tsql)

DECLARE @MaxValue BIGINT
SELECT @MaxValue = MAX(rownum) FROM #T1 WITH (NOLOCK)

INSERT INTO @Selects
( tsql )
SELECT 
CASE
WHEN rownum <> @MaxValue
THEN tsql + ' UNION ALL'
ELSE tsql
END
FROM #T1 WITH (NOLOCK)


IF @DEBUG = 1
BEGIN
SELECT * FROM @Selects
END

--SELECT * FROM @Selects

DECLARE @Output VARCHAR(MAX)

DECLARE outputcursor CURSOR FOR 
SELECT tsql
FROM @SELECTS
    
OPEN outputcursor;
FETCH NEXT FROM outputcursor INTO @Output;

IF @@FETCH_STATUS <> 0 
PRINT '         <>' ;

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @Output
FETCH NEXT FROM outputcursor INTO @Output;
END

CLOSE outputcursor
DEALLOCATE outputcursor

IF OBJECT_ID('tempdb..#DataExtractWarning') IS NOT NULL
BEGIN
DROP TABLE #DataExtractWarning
END
END
Enjoy! Zoidberg away!

1 comment:

Oleksandr Dubelewskyj said...

Good job!
I added Filter parameter to script required rows only and now its functionality is much better than built in SSMS