SQL Server 2008 - If Not Exists Insert Else Update

SQL Server Insert if not exists

instead of below Code

BEGIN
   INSERT INTO EmailsRecebidos (De, Assunto, Data)
   VALUES (@_DE, @_ASSUNTO, @_DATA)
   WHERE NOT EXISTS ( SELECT * FROM EmailsRecebidos 
                   WHERE De = @_DE
                   AND Assunto = @_ASSUNTO
                   AND Data = @_DATA);
END

replace with

BEGIN
   IF NOT EXISTS (SELECT * FROM EmailsRecebidos 
                   WHERE De = @_DE
                   AND Assunto = @_ASSUNTO
                   AND Data = @_DATA)
   BEGIN
       INSERT INTO EmailsRecebidos (De, Assunto, Data)
       VALUES (@_DE, @_ASSUNTO, @_DATA)
   END
END

Updated : (thanks to @Marc Durdin for pointing)

Note that under high load, this will still sometimes fail, because a second connection can pass the IF NOT EXISTS test before the first connection executes the INSERT, i.e. a race condition. See stackoverflow.com/a/3791506/1836776 for a good answer on why even wrapping in a transaction doesn't solve this.

SQL IF NOT EXISTS INSERT Else Update, based on column ID

Your insert syntax is wrong.
It should be something like this:

using (SqlCommand cmd = new SqlCommand("IF NOT EXISTS(SELECT 1from Distributor WHERE fpt_num = @FTP_num)" +
                        " insert into FTP_Info (IP, Port, UN, PW, Folder, FTP_num) VALUES(@IP, @Port, @UN, @PW, @Folder @ftp_num)" +
                        " else" +
                        " update FTP_Info set IP=@IP, Port=@Port, UN=@UN, PW=@PW, Folder=@Folder where FTP_num = @ftp_num", con))

Insert value if not exists in SQL Server 2008 R2

Try to use Transact-SQL statement IF..THEN..ELSE

IF EXISTS(SELECT * FROM #TableA) 
BEGIN 
   insert into #tableD  select * from #tableA;       
END
ELSE IF EXISTS(SELECT * FROM #TableB) 
BEGIN
  insert into #tableD  select * from #tableB;
END 
ELSE
BEGIN
  insert into #tableD  select * from #tableC;
END

Microsoft SQL Server - best way to 'Update if exists, or Insert'

Every one of them has different purpose, pros and cons.

Option 1 is good for multi row inserts/updates. However It only checks primary key constraints.

Option 2 is good for small sets of data. Single record insertion/update. It is more like script.

Option 3 is best for big queries. Lets say, reading from one table and inserting/updating to another accordingly. You can define which condition to be satisfied for insertion and/or update. You are not limited to primary key/unique constraint.

Solutions for INSERT OR UPDATE on SQL Server

don't forget about transactions. Performance is good, but simple (IF EXISTS..) approach is very dangerous.

When multiple threads will try to perform Insert-or-update you can easily
get primary key violation.

Solutions provided by @Beau Crawford & @Esteban show general idea but error-prone.

To avoid deadlocks and PK violations you can use something like this:

begin tran
if exists (select * from table with (updlock,serializable) where key = @key)
begin
   update table set ...
   where key = @key
end
else
begin
   insert into table (key, ...)
   values (@key, ...)
end
commit tran

or

begin tran
   update table with (serializable) set ...
   where key = @key

   if @@rowcount = 0
   begin
      insert into table (key, ...) values (@key,..)
   end
commit tran

sql server - insert if not exist else update

If I'm understanding your question correctly, a SQL Server EXCEPT query will help.

As already pointed out in the comments, here's how to get the matrix of items and flavors:

SELECT Items.Item, Flavors.Flavor
FROM Items
CROSS JOIN Flavors

Here's how to get the matrix of items and flavors, omitting the combinations that are already in your other table.

SELECT Items.Item, Flavors.Flavor
  FROM Items
  CROSS JOIN Flavors
EXCEPT SELECT Item, Flavor
  FROM Table_A

So the INSERT becomes:

INSERT INTO Table_A (Item, Flavor)
SELECT Items.Item, Flavors.Flavor
  FROM Items
  CROSS JOIN Flavors
EXCEPT SELECT Item, Flavor
  FROM Table_A

This query is untested because I'm not 100% sure about the question. If you post more detail I'll test it.

---

Related Topics