/****** Object: StoredProcedure [dbo].[sp_ssi_InsertGenerator] Script Date: 12/18/2015 14:00:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[sp_ssi_InsertGenerator]
(
@schemaName varchar(100)
,@tableName varchar(100)
,@IncludePrimaryKeyIdentity bit=0
,@IncludeNonPrimaryKeyIdentity bit=1
,@IncludeIfNotExists bit=0
)
AS
DECLARE @string nvarchar(MAX) --for storing the first half of INSERT statement
DECLARE @stringData nvarchar(MAX) --for storing the data (VALUES) related statement
DECLARE @dataType nvarchar(1000) --data types returned for respective columns
DECLARE @IfNotExistsString nvarchar(MAX) --for storing the NOT EXISTS string and be concatenated on each loop
DECLARE @IdentityExists bit -- flag to see if need to turn on IDENTITY_INSERT"
--Declare a cursor to retrieve column specific information for the specified table
DECLARE cursCol CURSOR FAST_FORWARD FOR
SELECT column_name,data_type
FROM information_schema.columns
WHERE table_name = @tableName AND Table_Schema=@schemaName
AND column_name NOT IN ('InsertedOn')--Used to exclude any columns that may be standard to exclude. For Example a default timestamp field.
OPEN cursCol
SET @string='INSERT ['+@schemaName+'].['+@tableName+']('
SET @stringData=''
SET @IfNotExistsString='''IF NOT EXISTS(SELECT 1 FROM ['+@schemaName+'].['+@tableName+'] WHERE '
DECLARE @colName nvarchar(50)
FETCH NEXT FROM cursCol INTO @colName,@dataType
IF @@fetch_status<>0
begin
print 'Table '+@schemaName+'.'+@tableName+' not found, processing skipped.'
close curscol
deallocate curscol
return
END
WHILE @@FETCH_STATUS=0
BEGIN
IF (
(
--IF NOT IDENTITY THEN PROCESS
EXISTS
(
SELECT * FROM sys.columns a
INNER JOIN sys.tables b on a.object_id=b.object_id
--LEFT JOIN sys.index_columns c ON c.object_id=b.object_id and a.column_id=c.column_id
--LEFT JOIN sys.key_constraints d ON c.object_id=d.parent_object_id
--AND c.index_id = d.unique_index_id
WHERE
a.Is_Identity<>1
AND b.object_id=Object_id(@schemaName+'.'+@tableName)
AND a.name=@colName
)
)
OR
(
--IF PRIMARY KEY AND @IncludePrimaryKeyIdentity =1 THEN PROCESS IT
EXISTS
(
SELECT * FROM sys.columns a
INNER JOIN sys.tables b on a.object_id=b.object_id
INNER JOIN sys.index_columns c ON c.object_id=b.object_id and a.column_id=c.column_id
INNER JOIN sys.key_constraints d ON c.object_id=d.parent_object_id
AND c.index_id = d.unique_index_id
WHERE a.Is_Identity=1
AND b.object_id=Object_id(@schemaName+'.'+@tableName)
AND a.name=@colName
)
AND @IncludePrimaryKeyIdentity=1
)
OR
(
--IF IDENTITY COLUMN (Not-Primary key) and @IncludeNonPrimaryKeyIdentity=1 THEN PROCESS IT
EXISTS
(
SELECT * FROM sys.columns a
INNER JOIN sys.tables b on a.object_id=b.object_id
LEFT JOIN sys.index_columns c ON c.object_id=b.object_id and a.column_id=c.column_id
LEFT JOIN sys.key_constraints d ON c.object_id=d.parent_object_id
AND c.index_id = d.unique_index_id
WHERE d.parent_object_id IS NULL
AND a.Is_Identity=1
AND b.object_id=Object_id(@schemaName+'.'+@tableName)
AND a.name=@colName
)
AND @IncludeNonPrimaryKeyIdentity=1
)
)
BEGIN
IF (
(
--IF PRIMARY KEY AND @IncludePrimaryKeyIdentity =1 THEN PROCESS IT
EXISTS
(
SELECT * FROM sys.columns a
INNER JOIN sys.tables b on a.object_id=b.object_id
INNER JOIN sys.index_columns c ON c.object_id=b.object_id and a.column_id=c.column_id
INNER JOIN sys.key_constraints d ON c.object_id=d.parent_object_id
AND c.index_id = d.unique_index_id
WHERE a.Is_Identity=1
AND b.object_id=Object_id(@schemaName+'.'+@tableName)
AND a.name=@colName
)
AND @IncludePrimaryKeyIdentity=1
)
OR
(
--IF IDENTITY COLUMN (Not-Primary key) and @IncludeNonPrimaryKeyIdentity=1 THEN PROCESS IT
EXISTS
(
SELECT * FROM sys.columns a
INNER JOIN sys.tables b on a.object_id=b.object_id
LEFT JOIN sys.index_columns c ON c.object_id=b.object_id and a.column_id=c.column_id
LEFT JOIN sys.key_constraints d ON c.object_id=d.parent_object_id
AND c.index_id = d.unique_index_id
WHERE d.parent_object_id IS NULL
AND a.Is_Identity=1
AND b.object_id=Object_id(@schemaName+'.'+@tableName)
AND a.name=@colName
)
AND @IncludeNonPrimaryKeyIdentity=1
)
)
BEGIN
SET @IdentityExists=1
END
IF @dataType in ('varchar','char','nchar','nvarchar')
BEGIN
SET @stringData=@stringData+''''+'''+isnull('''''+'''''+REPLACE(['+@colName+'],'''''''','''''''''''')+'''''+''''',''NULL'')+'',''+'
SET @IfNotExistsString=@IfNotExistsString+'['+@colName+']='+'''+isnull('''''+'''''+REPLACE(['+@colName+'],'''''''','''''''''''')+'''''+''''',''NULL'')+'' and ''+'''
--PRINT @stringData
END
ELSE
IF @dataType IN ('datetime','smalldatetime','date','datetime2','time')
BEGIN
SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
SET @IfNotExistsString=@IfNotExistsString+'['+@colName+']='+'''+isnull('''''+'''''+CONVERT(VARCHAR(30),['+@colName+'],121)+'''''+''''',''NULL'')+'' and ''+'''
END
ELSE
IF @dataType IN ('tinyint','smallint','int','money','bit','decimal','numeric','smallmoney','bigint')
BEGIN
SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'
SET @IfNotExistsString=@IfNotExistsString+'['+@colName+']='+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'' and ''+'''
END
ELSE
RAISERROR ('There is a datatype present in the table that is not accounted for in the procedure',16,1)
--BUILD COLUMN LIST
SET @string=@string+'['+@colName+'],'
END
FETCH NEXT FROM cursCol INTO @colName,@dataType
END
CLOSE cursCol
DEALLOCATE cursCol
--REMOVE ENDING "AND" FROM LINE AND ADD ENDING PARENTHESIS TO CLOSE OUT "IF NOT EXISTS" STATEMENT
SET @IfNotExistsString = LEFT(@IfNotExistsString,LEN(@IfNotExistsString)-10)
SET @IfNotExistsString = @IfNotExistsString +'+'')'''
----------------------------------------------------------------------------------------------------
DECLARE @Query nvarchar(4000)
IF @IncludeIfNotExists = 1 AND @IdentityExists=1
BEGIN
SET @query ='SELECT ''SET IDENTITY_INSERT ['+@schemaName+'].['+@tableName+'] ON'' UNION ALL SELECT REPLACE('+@IfNotExistsString+'+'''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'',''=NULL'','' IS NULL'')+CHAR(13)+CHAR(10) FROM ['+@schemaName+'].['+@tableName+'] UNION ALL SELECT ''SET IDENTITY_INSERT ['+@schemaName+'].['+@tableName+'] OFF'''
END
ELSE IF @IncludeIfNotExists = 1
BEGIN
SET @query ='SELECT REPLACE('+@IfNotExistsString+'+'''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'',''=NULL'','' IS NULL'')+CHAR(13)+CHAR(10) FROM ['+@schemaName+'].['+@tableName+']'
END
IF @IncludeIfNotExists = 0 AND @IdentityExists=1
BEGIN
SET @query ='SELECT ''SET IDENTITY_INSERT ['+@schemaName+'].['+@tableName+'] ON'' UNION ALL SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM ['+@schemaName+'].['+@tableName+'] UNION ALL SELECT ''SET IDENTITY_INSERT ['+@schemaName+'].['+@tableName+'] OFF'''
END
ELSE IF @IncludeIfNotExists = 0
BEGIN
SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM ['+@schemaName+'].['+@tableName+']'
END
--PRINT @query
BEGIN TRY
exec sp_executesql @query
END TRY
BEGIN CATCH
RAISERROR ('Unexpected error occured while trying to generate the insert script.',16,1)
PRINT 'Error Message: '+ERROR_MESSAGE()
PRINT 'Error Line: '+CONVERT(VARCHAR(20),ERROR_LINE())
PRINT 'Error Number: '+CONVERT(VARCHAR(20),ERROR_NUMBER())
END CATCH
GO
Subscribe