IF object_id('dbo.up_GenerateInserts') IS NULL
EXEC ('create procedure dbo.up_GenerateInserts as set nocount on')
GO
ALTER PROCEDURE dbo.up_GenerateInserts
@TableSchema sysname
,@TableName sysname
,@IDField sysname
,@IDMin SQL_VARIANT = NULL
,@IDMax SQL_VARIANT = NULL
,@IncludeIdent BIT = 0
,@IncludeIfNot BIT = 1
AS
SET NOCOUNT ON
DECLARE
@InsertSQL NVARCHAR(MAX)
,@SelectSQL NVARCHAR(MAX)
,@IFNotSQL NVARCHAR(MAX)
,@ObjectId INT
,@IDFieldQuote BIT
,@IDFieldDT sysname
SET @ObjectId = object_id(QUOTENAME(@tableSchema) + '.' + QUOTENAME(@TableName))
IF @ObjectId IS NULL
BEGIN
RAISERROR('Table [%s].[%s] not found.',16,1,@TableSchema,@TableName)
RETURN -1
END
SELECT @IDFieldDT = c.DATA_TYPE
FROM INFORMATION_SCHEMA.columns c WHERE c.TABLE_SCHEMA = @TableSchema AND c.TABLE_NAME = @TableName AND c.COLUMN_NAME = @IDField
IF @IDFieldDT IS NULL
BEGIN
RAISERROR('Column [%s].[%s].[%s] not found.',16,1,@TableSchema,@TableName,@IDField)
RETURN -1
END
IF @IDFieldDT IN ('VARCHAR','TEXT','char','datetime','ntext','nvarchar','nchar')
SET @IDFieldQuote = 1
SET @IFNotSQL = 'IF NOT EXISTS (select 1 from ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) + ' where ' + QUOTENAME(@IDField) + CASE WHEN @IDFieldQuote = 1 THEN ' = ''%IDValue%'')' ELSE ' = %IDValue%)' END + CHAR(13) + CHAR(10)
SET @SelectSQL = 'SELECT replace(@IFNotSQL,''%IDValue%'',replace(' + @IDField + ','''''''','''''''''''')) + '' @@InsertSQL@@
VALUES('' + '
SET @InsertSQL = N'insert into ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) + '('
SELECT @SelectSQL = @SelectSQL
+ 'isnull('
+ CASE WHEN c.DATA_TYPE IN ('VARCHAR','TEXT','char','datetime','ntext','nvarchar','nchar') THEN '''''''''+' ELSE '' END
+ 'REPLACE('
+ 'cast('
+ QUOTENAME(c.COLUMN_NAME)
+ ' as nvarchar(max))'
+ ','''''''','''''''''''')'
+ CASE WHEN c.DATA_TYPE IN ('VARCHAR','TEXT','char','datetime','ntext','nvarchar','nchar') THEN '+''''''''' ELSE '' END
+ ',''NULL'')'
+ '+'',''+'
, @InsertSQL = @InsertSQL + QUOTENAME(c.COLUMN_NAME) + ','
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = @TableSchema
AND c.TABLE_NAME = @TableName
AND (@IncludeIdent = 1 OR (@IncludeIdent = 0 AND COLUMNPROPERTY(@ObjectId,c.Column_Name,'IsIdentity')=0))
ORDER BY c.ORDINAL_POSITION
SET @InsertSQL = LEFT(@InsertSQL,LEN(@InsertSQL)-1) + ')'
SET @SelectSQL = LEFT(@SelectSQL,LEN(@SelectSQL)-5) + '+'')'''
SET @SelectSQL = REPLACE(@SelectSQL, '@@InsertSQL@@', @InsertSQL)
SET @SelectSQL = @SelectSQL
+ ' from ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName)
+ ' where ' + QUOTENAME(@IDField) + ' BETWEEN @IDMin and @IDMax '
+ ' order by 1,' + QUOTENAME(@IDField)
EXEC sp_executesql
@SelectSQL
,N'@IDMin sql_variant,@IDMax sql_variant, @IFNotSQL nvarchar(max), @InsertSQL nvarchar(max)'
,@IDMin = @IDMin
,@IDMax = @IDMax
,@IFNotSQL = @IFNotSQL
,@InsertSQL = @InsertSQL
GO
exec dbo.up_GenerateInserts
@TableSchema = 'dbo'
,@TableName = 'PurchasedGoods'
,@IDField = 'PurchasedGoods_Id'
,@IDMin = 1
,@IDMax = 100
,@IncludeIdent = 1
,@IncludeIfNot = 1
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (1)
Commented:
Thanks for sharing, this is brilliant! I wish I had had something like this available the few times I had had to script out the complete recreation of a table, including the values :)
Patrick