Scribd
Upload
27 views1 page

Copy Rows From One Table To Another

The document contains SQL commands to transfer data from the 'MCPayloadDestinations' table in the 'db_Original' database to the 'db_Deleted' database. It ensures that only non-existing records in 'db_Deleted' are inserted, while managing identity insert settings. Additionally, a dynamic SQL query is constructed and executed to perform the same operation using variable database names.

Uploaded by

Shivgopal Gautam
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
27 views1 page

Copy Rows From One Table To Another

The document contains SQL commands to transfer data from the 'MCPayloadDestinations' table in the 'db_Original' database to the 'db_Deleted' database. It ensures that only non-existing records in 'db_Deleted' are inserted, while managing identity insert settings. Additionally, a dynamic SQL query is constructed and executed to perform the same operation using variable database names.

Uploaded by

Shivgopal Gautam
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 1

USE db_Deleted

GO

SET IDENTITY_INSERT db_Deleted.dbo.MCPayloadDestinations ON

INSERT INTO db_Deleted.dbo.MCPayloadDestinations (PayloadDestinationID,


PayloadID,ReportSectionTypeID,TestID)
SELECT PayloadDestinationID, PayloadID,ReportSectionTypeID,TestID
FROM db_Original.dbo.MCPayloadDestinations
WHERE NOT EXISTS(SELECT * from db_Deleted.dbo.MCPayloadDestinations WHERE
(db_Original.dbo.MCPayloadDestinations.PayloadDestinationID=db_Deleted.dbo.MCPayloa
dDestinations.PayloadDestinationID))

SET IDENTITY_INSERT db_Deleted.dbo.MCPayloadDestinations OFF


GO

----------------------
DECLARE @FromDB AS VARCHAR (max)
DECLARE @ToDB AS VARCHAR (max)
BEGIN
SET @FromDB='db_Original.dbo'
SET @ToDB='db_Deleted.dbo'
DECLARE @query nvarchar(max)
set @query= N'SET IDENTITY_INSERT '+@ToDB+'.MCPayloadDestinations ON'

+' INSERT INTO '+ @ToDB +'.MCPayloadDestinations (PayloadDestinationID,


PayloadID,ReportSectionTypeID,TestID)'
+ ' SELECT PayloadDestinationID, PayloadID,ReportSectionTypeID,TestID'
+ ' FROM '+ @FromDB +'.MCPayloadDestinations'
+ ' WHERE NOT EXISTS(SELECT * from '+ @ToDB +'.MCPayloadDestinations WHERE ('+
@FromDB +'.MCPayloadDestinations.PayloadDestinationID='+ @ToDB
+'.MCPayloadDestinations.PayloadDestinationID))'

+ 'SET IDENTITY_INSERT '+ @ToDB +'.MCPayloadDestinations OFF'


EXEC sp_executesql @query
END
--------------------

You might also like