CODEWITHSUNDEEP
sqlsql-server
JoJo
JoJo

Reputation: 4933

How to code INSERT Subquery correctly?

I tested each part of the SQL and it works fine.

Something is Wrong with my WHERE clause however.. not getting much detail setting breakpoints in VS 2012 however...

How should this be code for an INSERT statement with a subquery using MS SQL SERVER?

INSERT INTO Emails (email, insertDate) 
VALUES (@Email, @DateToday) 
WHERE NOT EXISTS (SELECT Emails.email 
                  FROM Emails WHERE Emails.email = @Email);

Upvotes: 1

Views: 94

Answers (4)

praba
praba

Reputation: 49

INSERT INTO Emails (email, insertDate) 
SELECT Email, DateToday
WHERE NOT EXISTS (SELECT Emails.email 
                  FROM Emails WHERE Emails.email = Email)

It will work set email as a not null column or unique index.

Upvotes: 0

Yubaraj
Yubaraj

Reputation: 3988

Try to use like following :

"INSERT INTO Emails (email, insertDate) VALUES ('@Email', '@DateToday') WHERE NOT EXISTS (SELECT Emails.email FROM Emails WHERE Emails.email = '@Email');"

Upvotes: 2

podiluska
podiluska

Reputation: 51514

This will work

INSERT INTO Emails (email, insertDate) 
SELECT @Email, @DateToday
WHERE NOT EXISTS (SELECT Emails.email 
                  FROM Emails WHERE Emails.email = @Email)

But a better solution may be a unique index on the email column

Upvotes: 2

Lamak
Lamak

Reputation: 70678

INSERT INTO Emails (email, insertDate) 
SELECT @Email, @DateToday
WHERE NOT EXISTS (SELECT Emails.email 
                  FROM Emails WHERE Emails.email = @Email);

Upvotes: 5

Related Questions