Thursday, July 28, 2011

Conditional insert query without duplicates

This is just a sql puzzle which raised during a long wait for the QA results on a build day.The puzzle seems simple.

  1. Need to insert the records with out duplicates.
  2. The query should consider all the fields.
  3. The query should be single line.ie single statement of query execution.

At first it feels simple as a simple where query. But when we start writing we realize that can we write a where clause in a insert query? Simply speaking how can we write a conditional insert query in sql.After we play with the sql server and queries we automatically come to the below query.

INSERT INTO Person (Name , EMail,Id) 
select 'joy', 'joymon@gmail.com',1
WHERE (
SELECT COUNT(*)
FROM Person
WHERE Name = 'joy' and email='joymon@gmail.com' and id=1) = 0;


The table can be created using.

CREATE TABLE [dbo].[Person](
[Name] [nchar](50) NULL,
[Id] int NOT NULL,
[EMail] [nchar](50) NULL)

No comments: