CODEWITHSUNDEEP
asp.netsqlvb.netsql-server-2005
Jamie
Jamie

Reputation: 1679

INSERT statement will not let me use IF NOT EXISTS

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)

Upvotes: 1

Views: 744

Answers (1)

Martin Smith
Martin Smith

Reputation: 453142

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())

Upvotes: 4

Related Questions