insert if not exists in sql

Question

I am using ms visual studio 2012 and ms sql 2012.

On my asp.net VB page I have a 4 text boxes that a user can enter values and click insert. These values are then passed along with the date, users name and an integer to sql via subroutine that does the first text box value then the second and so on.

My problem is with the stored procedure. I used an IF NOT exist to look at the current data in the table and if there is no data matching the date and the integer then it will insert the record. The VB sub will then pass the second group of data in and it will again look to see if the date is there along with the integer and so on. The stored procedure is as follows:

@price money, @datesubmitted datetime, @commodityID int, @submitted_By nvarchar(10) As begin if not exists ((select * from dailyPricing where (convert(date, datesubmitted, 103) = convert(date, @datesubmitted, 103) and commodityID = 1) or (convert(date, datesubmitted, 103) = convert(date, @datesubmitted, 103) and commodityID = 2) or (convert(date, datesubmitted, 103) = convert(date, @datesubmitted, 103) and commodityID = 3) or (convert(date, datesubmitted, 103) = convert(date, @datesubmitted, 103) and commodityID = 4))) Begin INSERT INTO dailyPricing (price, datesubmitted, commodityID, submitted_By) values(@price, @datesubmitted, @commodityID, @submitted_By) end end

The result of the above is that it only enters the first group of values and not the second, third or fourth. I have debugged my VB code and it is working correctly I just think I haven't formed the SQL correctly.

You need to use MERGE statement, like this: stackoverflow.com/a/10219581/1734130 — mvp
– mvp, Commented Oct 25, 2013 at 10:43
In your exists you always compare @commodityID. Did you intend to compare your database field commodityID instead? — fan711
– fan711, Commented Oct 25, 2013 at 10:46
it should look to see if the date in the database matches @datesubmitted and is there a commodityID that matches either 1, 2, 3 or 4. if there is do nothing, if there isn't then insert the row. Ive also edited my code above to remove the @ preceeding commodityID. — Silentbob
– Silentbob, Commented Oct 25, 2013 at 10:56

शेखर · Accepted Answer · 2013-10-25 11:17:28Z

I think you need to rewrite that "not exists" for every check try something like this, and i think you need to update the brackets they might be wrong:

if not exists ((select * from dailyPricing where (convert(date, datesubmitted, 103) = convert(date, @datesubmitted, 103) and commodityID = 1)

or

if not exists ((select * from dailyPricing where (convert(date, datesubmitted, 103) = convert(date, @datesubmitted, 103) and commodityID = 2)

or ....

thanks Shekhar I used your example but I removed the if' or the last 3 statements so it just started not exists

Collectives™ on Stack Overflow

insert if not exists in sql

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related