Hello SayamiSuchi, This problem can be solved in many ways. Method 1: As has already been answered by UNCRushFan
IF NOT EXISTS (SELECT DISTINCT keyname FROM <table_name>)
BEGIN
INSERT INTO.....
END
Method 2: Another traditional way [ Count(ColumnName) approach ]
Declare @tblSource table([ID] int identity,[Name] varchar(20),[Key] varchar(10))
insert into @tblSource Select 'Sam', 'Sam1' Union all Select 'Joy','Joy2'
--Select * from @tblSource
Declare @key as varchar(10)
set @key = 'Joy2'
if(( Select count([KEY]) from @tblSource where [key] = @key) = 1)
begin
insert into @tblSource Select 'NewName', 'NewKey'
end
Select * from @tblSource
Method 3: Merge statement of Sql Server 2008 Consider the below statement first
Declare @tblSource table([ID] int identity,[Name] varchar(20),[Key] varchar(10))
insert into @tblSource Select 'Sam', 'Sam1' Union all Select 'Joy','Joy2'
Declare @tblDestination table([ID] int identity,[Name] varchar(20),[Key] varchar(10))
MERGE INTO @tblDestination AS Target
USING (SELECT [Name],[Key] FROM @tblSource) AS Source
ON Target.[Key] = Source.[Key]
WHEN NOT MATCHED BY TARGET THEN
INSERT (Name, [Key]) VALUES (Source.Name, Source.[Key]);
Select * from @tblDestination
I have a source table and a destination table. I am inserting the record in the destination table by checking whether the Key of the source table exists in the destination or not. In this case no key will be found in the destination and hence the output will be
ID Name Key
1 Sam Sam1
2 Joy Joy2
Now let us insert a record (with an already existing key) in the source table as
insert into @tblSource Select 'Joy','Joy2'
At this time the records in the source table will be
ID Name Key
1 Sam Sam1
2 Joy Joy2
3 Joy Joy2
Now if we run the above Merge statement query
MERGE INTO @tblDestination AS Target
USING (SELECT [Name],[Key] FROM @tblSource) AS Source
ON Target.[Key] = Source.[Key]
WHEN NOT MATCHED BY TARGET THEN
INSERT (Name, [Key]) VALUES (Source.Name, Source.[Key]);
Select * from @tblDestination
The output in the destination table will be
ID Name Key
1 Sam Sam1
2 Joy Joy2
because the key is already present in the destination table and hence it has been ignored. But if we have the source table as
insert into @tblSource Select 'NewName',
