Generate Insert Query in SQL

,

Sometimes we need to generate the INSERT script in sql server. When, we need to insert bulk of data from one table to another table we can easily achieve it by USING SELECT * INTO TABLE NAME. But sometimes  we have scenarios like for some Master tables, we want to insert Master data and that data will not change, or when we need to deploy our SQL objects with default values. So at these times we need to write the generic SP or dump data  into some excel, csv file and then we can import the data from that.

To handle such situations, I have created one generic SP which will take the table name as a input parameter in a form of xml <table></table> and based on the input parameter will generate the insert script. It also take care of the identity column like setting "Off" and setting "On" identity.

 

CREATE PROCEDURE [uspGenerate_InsertQuery]

@tables XML=NULL

AS

BEGIN

-- Variable Declaration

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(MAX) --data types RETURNed for respective columns

DECLARE @schemaName VARCHAR(25)

DECLARE @tableName VARCHAR(100)

DECLARE @identiyValue INT

DECLARE @SelectedTables TABLE (TableName VARCHAR(100))

--INSERT INTO @SelectedPolicies (PolicyId)

INSERT INTO @SelectedTables

SELECT Tbl.Col.value('.','VARCHAR(100)') as TableName

FROM @tables.nodes('//Table') Tbl( Col )

CREATE TABLE #TableList (ID INT IDENTITY(1,1), TableName VARCHAR(100))

CREATE TABLE #TEMP (ID BIGINT IDENTITY(1,1), Query NVARCHAR(MAX))

IF EXISTS (SELECT 1 FROM @SelectedTables)

INSERT INTO #TableList (TableName)

SELECT TableName FROM Dev.ufnGet_AllTablesInDependancyOrder() A WHERE EXISTS (SELECT 1 FROM @SelectedTables S WHERE ISNULL(S.TableName,'') = ISNULL(A.TableName,''))

ORDER BY SortOrder

ELSE

INSERT INTO #TableList (TableName)

SELECT TableName FROM Dev.ufnGet_AllTablesInDependancyOrder()

--WHERE TABLENAME = 'Notification.MessageTemplate'

ORDER BY SortOrder

DECLARE curTab CURSOR FOR

SELECT TableName FROM #TableList ORDER BY ID

OPEN curTab

FETCH NEXT FROM curTab INTO @tableName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @schemaName = SUBSTRING(@tableName,1,CHARINDEX('.',@tableName,1)-1)

SET @tableName = SUBSTRING(@tableName,CHARINDEX('.',@tableName,1)+1, LEN(@tableName))

INSERT INTO #TEMP (Query)

VALUES (' '),

(' /*********' + @schemaName + '.' + @tableName + '********/'),

(char(13))

SET @identiyValue = NULL

SET @string = ' SET @identiyValueOut = ' + 'IDENT_SEED(''' + @schemaName + '.' + @tableName + ''')'

EXEC SP_EXECUTESQL @string, N'@identiyValueOut INT OUTPUT', @identiyValueOut = @identiyValue OUTPUT

IF @identiyValue IS NOT NULL

BEGIN

INSERT INTO #TEMP (Query)

VALUES (' -- Making Identity OFF'),

(' SET IDENTITY_INSERT ' + @schemaName + '.' + @tableName + ' ON'),

(char(13))

END

INSERT INTO #TEMP (Query)

VALUES (' -- Inserting records into "' + @schemaName + '.' + @tableName + '"'),

(' ALTER TABLE ' + @schemaName + '.' + @tableName + ' NOCHECK CONSTRAINT ALL')

--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

OPEN cursCol

SET @string = ' INSERT INTO ' + @schemaName + '.' + @tableName + '('

SET @stringData = ''

DECLARE @colName NVARCHAR(50)

FETCH NEXT FROM cursCol INTO @colName,@dataType

WHILE @@FETCH_STATUS = 0

BEGIN

IF @dataType in ('VARCHAR','CHAR','NCHAR','NVARCHAR','DATE')

BEGIN

SET @stringData = @stringData + '

CASE WHEN ' + @colName + ' IS NOT NULL THEN '''''''' + REPLACE(' + @colName + ','''''''','''''''''''') + '''''''' ELSE ''NULL'' END + '','' + '

END

ELSE

IF @dataType in ('TEXT','NTEXT') --if the datatype

--is TEXT or something else

BEGIN

SET @stringData = @stringData + ''''''''' +

CASE WHEN ' + @colName + ' IS NOT NULL THEN CAST(' + @colName + ' as NVARCHAR(MAX)) ELSE ''NULL'' END + '','' + '

END

ELSE

IF @dataType = 'MONEY' --because MONEY doesn't get converted

--from VARCHAR implicitly

BEGIN

SET @stringData = @stringData + '

CASE WHEN ' + @colName + ' IS NOT NULL THEN '''''''' + CONVERT(VARCHAR(200), ' + @colName + ') + '''''''' ELSE ''NULL'' END + '','' + '

END

ELSE

IF @dataType = 'datetime'

BEGIN

/*

SET @stringData = @stringData + '

CASE WHEN ' + @colName + ' IS NOT NULL THEN '''''''' + CONVERT(VARCHAR(200), ' + @colName + ') + '''''''' ELSE ''NULL'' END + '','' + '

*/

SET @stringData = @stringData + '

CASE WHEN ' + @colName + ' IS NOT NULL THEN '''''''' + CONVERT(VARCHAR(200), ' + @colName + ') + '''''''' ELSE ''GETUTCDATE()'' END + '','' + '

END

ELSE

IF @dataType = 'image'

BEGIN

SET @stringData = @stringData + '

CASE WHEN ' + @colName + ' IS NOT NULL THEN '''''''' + CONVERT(VARCHAR(6),CONVERT(VARBINARY,' + @colName + ')) + '''''''' ELSE ''NULL'' END + '','' + '

END

ELSE

IF @dataType IN ('hierarchyid', 'VARBINARY')

BEGIN

SET @stringData = @stringData + ' ''NULL'' + '','' + '

END

ELSE --presuming the data type is int,bit,numeric,decimal

BEGIN

SET @stringData = @stringData + '

CASE WHEN ' + @colName + ' IS NOT NULL THEN CAST(' + @colName + ' as VARCHAR(200)) ELSE ''NULL'' END + '','' +'

END

SET @string = @string + @colName + ','

FETCH NEXT FROM cursCol INTO @colName,@dataType

END

SET @string = SUBSTRING(@string,1,LEN(@string)-1)

DECLARE @Query NVARCHAR(max) -- provide for the whole query,

-- you may increase the size

SET @query = 'INSERT INTO #TEMP(Query) SELECT ''' + substring(@string,1,len(@string)) + ')

VALUES('' + ' + substring(@stringData,1,len(@stringData)-4) + ''' + '')''

FROM ' + @schemaName + '.' + @tableName

-- PRINT @query

exec sp_executesql @query --load and run the built query

IF @identiyValue IS NOT NULL

BEGIN

INSERT INTO #TEMP (Query)

VALUES (' -- Making Identity On'),

(' SET IDENTITY_INSERT ' + @schemaName + '.' + @tableName + ' OFF'),

(char(13))

END

INSERT INTO #TEMP (Query)

VALUES (' -- Making all FKs enable'),

(' ALTER TABLE ' + @schemaName + '.' + @tableName + ' CHECK CONSTRAINT ALL')

CLOSE cursCol

DEALLOCATE cursCol

FETCH NEXT FROM curTab INTO @tableName

END

CLOSE curTab

DEALLOCATE curTab

SELECT Query FROM #TEMP ORDER BY ID


 

 

 

Rate

Share

Share

Rate