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