Saturday, February 8, 2020

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
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.