Feb 6, 2013

Generate the insert query script of given query in sql server


How to generate the script of insert of given table of given sql query by using stored procedure in sql server to export data from one table or server to another?

I am writing a stored procedure in sql server which will generate the insert query script in very efficient way. It will work fine event if table has too many records:

CREATE PROC uspGenerateInsertQuery(
     @vcSqlQuery AS NVARCHAR(MAX),
     @vcTableName AS VARCHAR(500),
     @vcDestinationTableName AS VARCHAR(500) = NULL,
     @btIncludeIdentity AS BIT = 0
)
AS
BEGIN
    
     DECLARE @vcInsertQuery AS VARCHAR(MAX)
     DECLARE @vcInsertTable AS VARCHAR(MAX)
     DECLARE @vcInsertColumn AS VARCHAR(MAX)
     DECLARE @vcQuery AS NVARCHAR(MAX)
     DECLARE @vcIdentityColumn AS VARCHAR(200)
    
     BEGIN TRY
    
          --Deleting the table ##tblTempQuery if it is already present
          IF OBJECT_ID('tempdb..##tblTempQuery') IS NOT NULL
              DROP TABLE ##tblTempQuery

          --Preparing query to insert query data into ##tblTempQuery
          SET @vcSqlQuery = STUFF(@vcSqlQuery,
                                      CHARINDEX(' FROM ',
                                                     @vcSqlQuery),
                                      0,
                                      ' INTO ##tblTempQuery ')         
         
          --Finding out is there any identity column present in this table or not.
          IF @vcDestinationTableName IS NULL OR @btIncludeIdentity = 0 BEGIN
         
              IF OBJECT_ID(@vcTableName) IS NULL
                        RAISERROR('Invalid table name',16,1)
             
              SELECT @vcIdentityColumn = [name] 
              FROM sys.columns
              WHERE [Object_Id] = OBJECT_ID(@vcTableName)
                   AND is_identity = 1
                  
          END
         
          --Setting destination table
          IF @vcDestinationTableName IS NULL
               SET @vcDestinationTableName = @vcTableName 
         
          --Inserting record from query to ##tblTempQuery            
          EXECUTE SP_EXECUTESQL @vcSqlQuery
         
          --Preparing query to generate insert statement
          SELECT
              @vcInsertTable =  ISNULL(@vcInsertTable + ',','') + QUOTENAME(name),
              @vcInsertColumn =  ISNULL(@vcInsertColumn + ' + '','' + ','') +
              CASE WHEN user_type_id IN(48,52,56,59,60,62,104,106,108,122,127)
              THEN '' ELSE ''''''''' + ' END  +  'REPLACE((CAST(' + QUOTENAME(name)
              + 'AS VARCHAR(MAX))),'''''''','''''''''''')' +  CASE
              WHEN user_type_id IN(48,52,56,59,60,62,104,106,108,122,127) THEN ''
              ELSE '  + ''''''''' END
          FROM tempdb.sys.columns
          WHERE [object_id] =  OBJECT_ID('tempdb..##tblTempQuery')
              AND ((@vcIdentityColumn IS NULL) OR ((@vcIdentityColumn IS NOT NULL AND name <> @vcIdentityColumn)) )
             
          SET @vcInsertTable = '''INSERT INTO ' + @vcDestinationTableName + '(' + @vcInsertTable + ') VALUES('' + '
          SET @vcInsertTable =  @vcInsertTable +  @vcInsertColumn + '+ '')'''
          SET @vcQuery = 'SELECT ' + @vcInsertTable + ' FROM  ##tblTempQuery'
         
          --Generating insert script
          EXECUTE(@vcQuery)
         
          --Droping the the table ##tblTempQuery
          IF OBJECT_ID('tempdb..##tblTempQuery') IS NOT NULL
              DROP TABLE ##tblTempQuery
         
     END TRY
     BEGIN CATCH
    
          SELECT
              ERROR_MESSAGE() AS ErrMsg,
              ERROR_LINE() AS ErrLine 
             
     END CATCH
    
END

To test it:

--To execute it
EXECUTE uspGenerateInsertQuery
      'SELECT TOP(5) * FROM tblEmployee WHERE ntEmployeeID > 10',
      'tblEmployee',
      'tblDestination'

Sample output:


INSERT INTO tblDestination([vcEmpName],[vcPhoneNumber],[ntAge],[moSalary]) VALUES('Emp21','0211033',20,1033.00)
INSERT INTO tblDestination([vcEmpName],[vcPhoneNumber],[ntAge],[moSalary]) VALUES('Emp21','0211033',20,1033.00)
INSERT INTO tblDestination([vcEmpName],[vcPhoneNumber],[ntAge],[moSalary]) VALUES('Emp21','0211033',20,1033.00)
INSERT INTO tblDestination([vcEmpName],[vcPhoneNumber],[ntAge],[moSalary]) VALUES('Emp21','0211033',20,1033.00)
INSERT INTO tblDestination([vcEmpName],[vcPhoneNumber],[ntAge],[moSalary]) VALUES('Emp21','0211033',20,1033.00)

No comments:

Post a Comment