October 25, 2016 at 11:01 am
I have a scenario where I have to create a replication of a target table along with source Id field.
If I have Target Table with columns :
Target_ID,
name,
createdate which is getdate()
Then my target replication table will be:
Target_ID,
name,
createdate which is getdate(),
source_ID
Once the record hits the target table , I want the record to be inserted into replication table along with source Id. I can do this and it is working. but I would like to include the condition that only if record is inserted in target then insert in replication table else do not do anything.As of now I am picking up the latest record from target and inserting into replication table. What if there is no record inserted , even then my code would pick the latest record and insert into replication table.
Any help is appreciated!
October 27, 2016 at 11:09 pm
Take a look at this link hope this will help you: http://stackoverflow.com/questions/8560285/how-to-select-the-record-from-table-and-insert-into-another-table
October 27, 2016 at 11:43 pm
hegdesuchi (10/25/2016)
I have a scenario where I have to create a replication of a target table along with source Id field.If I have Target Table with columns :
Target_ID,
name,
createdate which is getdate()
Then my target replication table will be:
Target_ID,
name,
createdate which is getdate(),
source_ID
Once the record hits the target table , I want the record to be inserted into replication table along with source Id. I can do this and it is working. but I would like to include the condition that only if record is inserted in target then insert in replication table else do not do anything.As of now I am picking up the latest record from target and inserting into replication table. What if there is no record inserted , even then my code would pick the latest record and insert into replication table.
Any help is appreciated!
There are two very straight forward ways of doing this, first one which is using the output clause, is applicable if you control the inserts into the source table. The second one is for when you don't control the inserts and that is to use a trigger.
😎
Here is a quick example of the two methods
USE TEEST;
GO
SET NOCOUNT ON;
-- Source Table, anything inserted into this table will be replicated to the Target Table
IF OBJECT_ID('dbo.TBL_SOURCE_TABLE') IS NOT NULL DROP TABLE dbo.TBL_SOURCE_TABLE;
CREATE TABLE dbo.TBL_SOURCE_TABLE
(
ST_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_SOURCE_TABLE_ST_ID PRIMARY KEY CLUSTERED
,ST_TS DATETIME NOT NULL CONSTRAINT DFLT_DBO_TBL_SOURCE_TABLE_TT_TS DEFAULT (GETDATE())
,ST_NAME VARCHAR(100) NOT NULL
);
-- Target Table, a replication of the Source Table
IF OBJECT_ID('dbo.TBL_TARGET_TABLE') IS NOT NULL DROP TABLE dbo.TBL_TARGET_TABLE;
CREATE TABLE dbo.TBL_TARGET_TABLE
(
TT_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TARGET_TABLE_TT_ID PRIMARY KEY CLUSTERED
,TT_TS DATETIME NOT NULL CONSTRAINT DFLT_DBO_TBL_TARGET_TABLE_TT_TS DEFAULT (GETDATE())
,ST_ID INT NOT NULL
,ST_NAME VARCHAR(100) NOT NULL
);
---- Using the output clause to replicate the inserted values
---- to the Target table.
INSERT INTO dbo.TBL_SOURCE_TABLE ( ST_NAME )
OUTPUT
inserted.ST_ID
,inserted.ST_NAME
INTO dbo.TBL_TARGET_TABLE(ST_ID,ST_NAME)
VALUES ('FIRST_NAME'),('NEXT_NAME')
-- Check the Target Table
SELECT
TT_ID
,TT_TS
,ST_ID
,ST_NAME
FROM dbo.TBL_TARGET_TABLE TT;
---- A trigger that replicates the inserted values
---- to the Target table.
DECLARE @CREATE_TRIGGER_SQL NVARCHAR(MAX) = N'
CREATE TRIGGER dbo.TRG_TBL_SOURCE_TABLE_INSERTS
ON dbo.TBL_SOURCE_TABLE
FOR INSERT
AS
INSERT INTO dbo.TBL_TARGET_TABLE ( ST_ID,ST_NAME )
SELECT
I.ST_ID
,I.ST_NAME
FROM inserted I
ORDER BY I.ST_ID ASC;';
EXEC (@CREATE_TRIGGER_SQL);
-- Add two values to the Source Table
INSERT INTO dbo.TBL_SOURCE_TABLE ( ST_NAME )
VALUES ('THIRD_NAME'),('FOURTH_NAME');
-- Check the Target Table
SELECT
TT_ID
,TT_TS
,ST_ID
,ST_NAME
FROM dbo.TBL_TARGET_TABLE TT;
October 28, 2016 at 8:32 am
hi,
Thank you!!
we have to insert source table via webservice and I am doing this in a script task. after the source table is inserted via web service, then I have to pick the latest and insert in target.
So Can I insert source table in script task and add the below trigger in execute sql task immediately after script task?
IF OBJECT_ID('TRG_InsertSyncEmp') IS NOT NULL
DROP TRIGGER TRG_InsertSyncEmp
GO
CREATE TRIGGER TRG_InsertSyncEmp
ON dbo.EMPLOYEE
AFTER INSERT AS
BEGIN
INSERT INTO EMPLOYEE_BACKUP
SELECT * FROM INSERTED
END
G
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply