SQL Server Insert If Not Exists

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 - Insert Where Not Exists

Your problem comes from WHERE being valid for UPDATE/SELECT but INSERT just doesn’t understand what it means.

But you can get around this. Change your code to be like:

BEGIN
   INSERT INTO [dbo].[Contact_Categories](Contact_Category_ID, Description)
   SELECT 1, 'Internal'
   WHERE NOT EXISTS( SELECT * FROM [dbo].[Contact_Categories] WHERE Contact_Category_ID = 1)
END

SQL Server Insert if not exist

Combine the EXISTS with INSERT

eg

INSERT INTO [tbl_myTable] (...) 
SELECT @val1, @val2 ...
WHERE NOT EXISTS
(
    SELECT 1 FROM [tbl_myTable] WITH (UPDLOCK, SERIALIZABLE)
    WHERE [MyNotKeyField] = 'myValue'
);

Aaron Bertrand has a great post on anti UPSERT patterns

SQL - INSERT IF NOT EXISTS based on COUNT

If you change your WHERE to:

WHERE 5 > (SELECT count(*) FROM day_count_table b WHERE b.day1 = a.day1);

Where 5 is the number you want to be less than - wouldn't that catch both circumstances... ie: If there's no rows, count(*) would be 0, if there are fewer than your limit, you're good to.

SQL Server insert if not exists best practice

Semantically you are asking "insert Competitors where doesn't already exist":

INSERT Competitors (cName)
SELECT DISTINCT Name
FROM CompResults cr
WHERE
   NOT EXISTS (SELECT * FROM Competitors c
              WHERE cr.Name = c.cName)

SQL Insert Into Where Record Not Exists

In SQL Server, you would use except. Assuming the tables have the same columns:

insert into [Database]..[Table2]
    select Distinct t1.*
    from [Database]..[Table1] t1
    except
    select t2.*
    from [Database]..[Table2] t2;

Your not exists clause is not correlated to the data in table1, so it is not doing what you expect.

How can I do 'insert if not exists' in MySQL?

Use INSERT IGNORE INTO table.

There's also INSERT … ON DUPLICATE KEY UPDATE syntax, and you can find explanations in 13.2.6.2 INSERT ... ON DUPLICATE KEY UPDATE Statement.

---

Post from bogdan.org.ua according to Google's webcache:

18th October 2007

To start: as of the latest MySQL, syntax presented in the title is not
possible. But there are several very easy ways to accomplish what is
expected using existing functionality.

There are 3 possible solutions: using INSERT IGNORE, REPLACE, or
INSERT … ON DUPLICATE KEY UPDATE.

Imagine we have a table:

CREATE TABLE `transcripts` (
`ensembl_transcript_id` varchar(20) NOT NULL,
`transcript_chrom_start` int(10) unsigned NOT NULL,
`transcript_chrom_end` int(10) unsigned NOT NULL,
PRIMARY KEY (`ensembl_transcript_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Now imagine that we have an automatic pipeline importing transcripts
meta-data from Ensembl, and that due to various reasons the pipeline
might be broken at any step of execution. Thus, we need to ensure two
things:

1. repeated executions of the pipeline will not destroy our
   > database

2. repeated executions will not die due to ‘duplicate
   > primary key’ errors.

Method 1: using REPLACE

It’s very simple:

REPLACE INTO `transcripts`
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;

If the record exists, it will be overwritten; if it does not yet
exist, it will be created. However, using this method isn’t efficient
for our case: we do not need to overwrite existing records, it’s fine
just to skip them.

Method 2: using INSERT IGNORE Also very simple:

INSERT IGNORE INTO `transcripts`
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;

Here, if the ‘ensembl_transcript_id’ is already present in the
database, it will be silently skipped (ignored). (To be more precise,
here’s a quote from MySQL reference manual: “If you use the IGNORE
keyword, errors that occur while executing the INSERT statement are
treated as warnings instead. For example, without IGNORE, a row that
duplicates an existing UNIQUE index or PRIMARY KEY value in the table
causes a duplicate-key error and the statement is aborted.”.) If the
record doesn’t yet exist, it will be created.

This second method has several potential weaknesses, including
non-abortion of the query in case any other problem occurs (see the
manual). Thus it should be used if previously tested without the
IGNORE keyword.

Method 3: using INSERT … ON DUPLICATE KEY UPDATE:

Third option is to use INSERT … ON DUPLICATE KEY UPDATE
syntax, and in the UPDATE part just do nothing do some meaningless
(empty) operation, like calculating 0+0 (Geoffray suggests doing the
id=id assignment for the MySQL optimization engine to ignore this
operation). Advantage of this method is that it only ignores duplicate
key events, and still aborts on other errors.

As a final notice: this post was inspired by Xaprb. I’d also advise to
consult his other post on writing flexible SQL queries.

---

Related Topics