check the key value before inserting data [modified]---ALREADY SOLVED---

[Niladri_Biswas]

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