how to insert newly inserted value in the target table

Question

How do i insert the newly created value in my target table as compared to the stage table

in a first step you check if the PersonID is already in the table.

If not that add the PersonID to the dbo.user table

Answer 1

just an example please update

INSERT INTO dbo.user (PersonID, Name, Age)
OUTPUT INSERTED.PersonID
SELECT s.PersonID, s.Name, s.Age
FROM stg_user s
WHERE NOT EXISTS (SELECT 1 FROM dbo.user u WHERE u.PersonID = s.PersonID)

This query will insert the rows from "stg_user" that are not already present in "dbo.user", and will also return the newly inserted "PersonID" values from "dbo.user". You can then use these values in subsequent queries or store them in a variable for further processing.

Answer 2

Hi @Naresh y

How about an After Insert Trigger.

Please check this sample:

CREATE TABLE stage_table(PersonID int, Name VARCHAR(20))
CREATE TABLE [user](PersonID INT)

IF OBJECT_ID('tri_AfterInsert')IS NOT NULL
DROP TRIGGER tri_AfterInsert
GO

CREATE TRIGGER tri_AfterInsert ON stage_table
AFTER INSERT 
AS
IF (SELECT COUNT(1) FROM stage_table S JOIN inserted I ON S.PersonID=I.PersonID) = 1
BEGIN
   INSERT INTO [user](PersonID) SELECT PersonID FROM inserted
END

--TEST
INSERT INTO stage_table VALUES (111,'AAA')
INSERT INTO stage_table VALUES (222,'BBB')

SELECT * FROM stage_table
SELECT * FROM [user]

DROP TABLE stage_table,[user]

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.