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