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)