Generate Insert Statements Easily - Updated
This script will generate insert statements for the given tables. You can pass the tables names, separated by commas, into sp_DataAsInsCommand stored procedure as in the example below:
EXEC sp_DataAsInsCommand 'employee,titleauthor,pub_info'
Updated on 5/25/01 to correct an issue with columns that are short like a column defined as char(1), to correct another issue with the Identity column (before you had to supply a list of columns in the insert statement), and to add the following two changes: 1) to print the name of the table in a comment before the insert commands, and 2) to expand the size of the parameter so a larger list of tables could be submitted to the stored procedure.
Updated on 5/5/05 to make it working again.
alter PROC sp_DataAsInsCommand
(@TableList varchar (8000)
)
AS
SET NOCOUNT ON
DECLARE @position int, @exec_str varchar (2000), @TableName varchar (50)
DECLARE @name varchar(128), @xtype int, @status tinyint, @IsIdentity tinyint
DECLARE @ColumnList varchar(8000)
SET @ColumnList=''
SELECT @TableList = @TableList + ','
SELECT @IsIdentity = 0
SELECT @position = PATINDEX('%,%', @TableList)
WHILE (@position <> 0)
BEGIN
SELECT @TableName = SUBSTRING(@TableList, 1, @position - 1)
SELECT @TableList = STUFF(@TableList, 1, PATINDEX('%,%', @TableList),'')
SELECT @position = PATINDEX('%,%', @TableList)
SELECT @exec_str = 'DECLARE fetch_cursor CURSOR FOR SELECT a.name, a.xtype, a.status FROM syscolumns a, sysobjects b WHERE a.id = b.id and b.name = ''' + @TableName + ''''
EXEC (@exec_str)
--print @exec_str
OPEN fetch_cursor
FETCH fetch_cursor INTO @name, @xtype, @status
IF (@status & 0x80) <> 0
BEGIN
SELECT @IsIdentity = 1
SELECT 'SET IDENTITY_INSERT ' + @TableName + ' ON'
SELECT 'GO'
END
SET @ColumnList=@ColumnList + @name +','
--SELECT @exec_str = 'SELECT ''INSERT INTO ' + @TableName + ' VALUES ('' + '
SET @exec_str = ''
Select ' -- The table name is: ' + @TableName
--text or ntext
IF (@xtype = 35) OR (@xtype = 99)
SELECT @exec_str = @exec_str + '''"None yet"'''
ELSE
--image
IF (@xtype = 34)
SELECT @exec_str = @exec_str + '"' + '0xFFFFFFFF' + '"'
ELSE
--smalldatetime or datetime
IF (@xtype = 58) OR (@xtype = 61)
SELECT @exec_str = @exec_str + 'Coalesce( + '''''''' + CONVERT(varchar,' + @name + ',109)' + ' + ''''''''' + ',''null'')'
ELSE
--varchar or char or nvarchar or nchar
IF (@xtype = 167) OR (@xtype = 175) OR (@xtype = 231) OR (@xtype = 239)
SELECT @exec_str = @exec_str + 'Coalesce(' + ''''''''' + ' + @name + ' + ''''''''' + ',''null'')'
ELSE
--uniqueidentifier
IF (@xtype = 36)
SELECT @exec_str = @exec_str + ' + Coalesce('''''''' + ' + ' + CONVERT(varchar(255),' + @name + ')' + ' + ''''''''' + ',''null'')'
ELSE
--binary or varbinary
IF (@xtype = 173) OR (@xtype = 165)
SELECT @exec_str = @exec_str + '"' + '0x0' + '"'
ELSE
SELECT @exec_str = @exec_str + 'Coalesce(CONVERT(varchar,' + @name + '), ''null'')'
WHILE @@FETCH_STATUS <> -1
BEGIN
FETCH fetch_cursor INTO @name, @xtype, @status
IF (@@FETCH_STATUS = -1) BREAK
IF (@status & 0x80) <> 0
BEGIN
SELECT @IsIdentity = 1
SELECT 'SET IDENTITY_INSERT ' + @TableName + ' ON'
SELECT 'GO'
END
SET @ColumnList=@ColumnList + @name +','
--text or ntext
IF (@xtype = 35) OR (@xtype = 99)
SELECT @exec_str = @exec_str + ' + '',''' + ' + ''"None yet"'''
ELSE
--image
IF (@xtype = 34)
SELECT @exec_str = @exec_str + ' + '','' + ' + '"' + '0xFFFFFFFF' + '"'
ELSE
--smalldatetime or datetime
IF (@xtype = 58) OR (@xtype = 61)
SELECT @exec_str = @exec_str + ' + '',''' + ' + Coalesce('''''''' + ' + ' + CONVERT(varchar,' + @name + ',109)' + ' + ''''''''' + ',''null'')'
ELSE
--varchar or char or nvarchar or nchar
IF (@xtype = 167) OR (@xtype = 175) OR (@xtype = 231) OR (@xtype = 239)
SELECT @exec_str = @exec_str + ' + '',''' + ' + Coalesce('''''''' + ' + @name + ' + ''''''''' + ',''null'')'
ELSE
--uniqueidentifier
IF (@xtype = 36)
SELECT @exec_str = @exec_str + ' + '',''' + ' + Coalesce('''''''' + ' + ' + CONVERT(varchar(255),' + @name + ')' + ' + ''''''''' + ',''null'')'
ELSE
--binary or varbinary
IF (@xtype = 173) OR (@xtype = 165)
SELECT @exec_str = @exec_str + ' + '','' + ' + '"' + '0x0' + '"'
ELSE
SELECT @exec_str = @exec_str + ' + '',''' + ' + Coalesce(CONVERT(varchar,' + @name + '), ''null'')'
END
CLOSE fetch_cursor
DEALLOCATE fetch_cursor
SELECT @exec_str = 'SELECT ''INSERT INTO ' + @TableName +'('+substring(@ColumnList,1,len(@ColumnList)-1) + ')
VALUES ('' + ' + @exec_str + '+ '')'' FROM ' + @TableName
EXEC(@exec_str)
--print (@exec_str)
SELECT 'GO'
IF @IsIdentity = 1
BEGIN
SELECT @IsIdentity = 0
SELECT 'SET IDENTITY_INSERT ' + @TableName + ' OFF'
SELECT 'GO'
END
END
--print @ColumnList 
