How to check if a record exists otherwise Insert into a Table in SQL Server

How to use a Trigger to check if a record exists otherwise Insert into a Table in SQL Server

Scenario:

Suppose we have to tables as , [dbo].[Sample_Data] , [dbo].[Master_Data]

Whenever, we insert a new record into the table [dbo].[Sample_Data] , a Insert Trigger need to fire, which has to check whether these records already exists in [Master_Data] or not.

If not exists, only those new records should insert into the [Master_Data] table, and a copy of all these records should go to an [dbo].[Sample_Archive] table as well.

In this Scenario, I have taken the similar structure for each table.

Select * From [dbo].[Master_Data]

here, Order_Id is the Primary Key column.

Now Define Trigger on the table [dbo].[Sample_Data] , to fill fill the above Scenario.

CREATE TRIGGER [dbo].[trg_AfterInsert] ON [dbo].[Sample_Data]

FOR INSERT

AS

BEGIN

--Checking if the New records already existed in Master_Data table or Not

IF EXISTS(

SELECT Distinct [Order_Id]  From Inserted

EXCEPT

SELECT Distinct [Order_Id]  From [dbo].[Master_Data]

)

Begin 

   -- The new records from Sample_Data table to Master_Data table

INSERT INTO [dbo].[Master_Data] 

Select * From Inserted T1 Where T1.[Order_Id] 

NOT IN (Select Distinct [Order_Id] from [dbo].[Master_Data])

   --Archiving the a Copy of all records from Inserted

Insert Into  [dbo].[Sample_Archive] Select * From Inserted

End

END

GO

Now we will Insert some Records into the [dbo].[Sample_Data], and will check how the Master_Data table and Sample_Archive tables are getting updated.

Insert Into [dbo].[Sample_Data] 

([Order_Id],[ProdName],[RegionName],[FiscalQtr],[NetSales])

Values (118,'Laptop','South','Q2-2016',3659),

(127, 'Printer', 'East', 'Q1-2016', 7956),

(129, 'Scanner', 'West', 'Q1-2016', 5763),

(113, 'Printer' ,'East', 'Q4-2016', 6054)

Output :

Select * From [dbo].[Master_Data]

Notes:

The 123,127,129 are already exists in the Master_Data table, so that those records were skipped while inserting from Sample_Data table.

Select * From [dbo].[Sample_Archive]

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------