This is just a sql puzzle which raised during a long wait for the QA results on a build day.The puzzle seems simple.
- Need to insert the records with out duplicates.
- The query should consider all the fields.
- 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', 'firstname.lastname@example.org',1
WHERE Name = 'joy' and email@example.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)