INSERT statement will not let me use IF NOT EXISTS

Question

I have an insert statement that I can't get to work the way I want it to. It's on a vb.net page. This is on a VB.net page and I'm using SQL Server 2005 for my database.

Dim strSQL As String = "IF NOT EXISTS
(SELECT Title From Picklist) 
BEGIN INSERT INTO Picklist (Title, Data) 
VALUES (@Title, @Data);

INSERT INTO Marketing 
(ProductID, MarketingTypeID, MarketingTitle, MarketingData) 
VALUES (@ProductID, 9, 'Video', scope_identity()) END"

I don't get an error and nothing gets inserted into the database. If I try putting the END at the end of the first INSERT statement then I get an error saying that MarketingData is NULL and cannot be inserted.

But if I take out the IF NOT EXISTS from the statement, everything gets inserted perfectly. What am I doing wrong here?

UPDATE: Is it correct to write the statement like this?

INSERT INTO Marketing
SELECT (@ProductID, @MarketingTypeID, @MarketingTitle, @MarketingData)
WHERE NOT EXISTS
(SELECT * FROM Marketing)

Is the Picklist table empty? If not, your IF NOT EXISTS condition will be false. — Ian Nelson, Sep 30 '11 at 20:11
@jlg - And what are the desired semantics? You are trying to insert a row into `Picklist` only if it doesn't already exist? And if so what constitutes it already existing? The `Title` already exists? Or the `Title, Data` combination already exists? — Martin Smith, Sep 30 '11 at 20:18
@IanNelson the Picklist table has a lot of data in it, this query actually works on other MarketingTitles (Video is just one of the types of Titles in the database - there is also Documents, Images, blah blah blah) The IF NOT EXISTS query works when I try to insert an image into the database. — Jamie, Sep 30 '11 at 20:20
@MartinSmith I was going for only if the Title already exists, I had the statement as `IF NOT EXISTS SELECT Title from Picklist` but somewhere along the way I must've changed it, dumb error. I will change that back though, thanks for pointing that out. — Jamie, Sep 30 '11 at 20:22
Did you add a BEGIN with your END? (In addition to the issues already mentioned with the IF EXISTS query.) — HardCode, Sep 30 '11 at 20:22
It's definitely the IF NOT EXISTS, I must be writing it wrong or something. I have tried using it on other pages in this site but it won't insert anything. `IF NOT EXISTS(SELECT column FROM table) BEGIN INSERT INTO table columns END` Is there a reason that this would NOT work? — Jamie, Oct 04 '11 at 14:18

Martin Smith · Accepted Answer · 2011-09-30T20:30:08.703

4

Your IF NOT EXISTS(SELECT * FROM Picklist) will skip the insert if any rows at all exist in Picklist.

From your description of what happens when you change the position of the END it seems there are rows in the table.

I assume in fact you are trying to do an UPSERT. What version of SQL Server are you on? If 2008 look into MERGE

;WITH Source(Title, Data) AS
(
SELECT @Title, @Data
)
MERGE Picklist AS T
USING Source S
ON (T.Title = S.Title)


WHEN NOT MATCHED BY TARGET THEN
    INSERT (Title, Data)
    VALUES (@Title, @Data)
    ;

IF (@@ROWCOUNT <> 0)
INSERT INTO Marketing 
            (ProductID, MarketingTypeID, MarketingTitle, MarketingData) 
     VALUES (@ProductID, 9, 'Video', scope_identity())

edited Sep 30 '11 at 20:30

answered Sep 30 '11 at 20:11

Martin Smith

438,706
87
741
845

@jlg - I've added a `MERGE` version that gives the semantics you state in the comments. – Martin Smith Sep 30 '11 at 20:26
Dangit, someone edited my tags! This is on a vb.net page by the way. Does that matter? I wouldn't think it would because it's still an sql statement – Jamie Sep 30 '11 at 20:33
@jlg - No. Shouldn't do. I made some fixes to it from the original post so make sure you use the current version not any previous one. – Martin Smith Sep 30 '11 at 20:35
I'm getting an error saying `Incorrect syntax near 'MERGE'. Cannot assign a default value to a local variable.` – Jamie Sep 30 '11 at 20:39
That doesn't sound like you are on 2008 then – Martin Smith Sep 30 '11 at 20:41
What is the result of `select @@version, compatibility_level from sys.databases where database_id=DB_ID()` – Martin Smith Sep 30 '11 at 20:50
Oh, my boss told me wrong then. It says SQL server 2005 compatibility level 90. Sorry about that, I wasn't aware that I had misleading information. – Jamie Sep 30 '11 at 20:52

INSERT statement will not let me use IF NOT EXISTS

1 Answers1