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', '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:
Post a Comment