Insert Values Where Not Exists

INSERT VALUES WHERE NOT EXISTS

You could do this using an IF statement:

IF NOT EXISTS 
    (   SELECT  1
        FROM    tblSoftwareTitles 
        WHERE   Softwarename = @SoftwareName 
        AND     SoftwareSystemType = @Softwaretype
    )
    BEGIN
        INSERT tblSoftwareTitles (SoftwareName, SoftwareSystemType) 
        VALUES (@SoftwareName, @SoftwareType) 
    END;

You could do it without IF using SELECT

INSERT  tblSoftwareTitles (SoftwareName, SoftwareSystemType) 
SELECT  @SoftwareName,@SoftwareType
WHERE   NOT EXISTS 
        (   SELECT  1
            FROM    tblSoftwareTitles 
            WHERE   Softwarename = @SoftwareName 
            AND     SoftwareSystemType = @Softwaretype
        );

Both methods are susceptible to a race condition, so while I would still use one of the above to insert, but you can safeguard duplicate inserts with a unique constraint:

CREATE UNIQUE NONCLUSTERED INDEX UQ_tblSoftwareTitles_Softwarename_SoftwareSystemType
    ON tblSoftwareTitles (SoftwareName, SoftwareSystemType);

Example on SQL-Fiddle

---

ADDENDUM

In SQL Server 2008 or later you can use MERGE with HOLDLOCK to remove the chance of a race condition (which is still not a substitute for a unique constraint).

MERGE tblSoftwareTitles WITH (HOLDLOCK) AS t
USING (VALUES (@SoftwareName, @SoftwareType)) AS s (SoftwareName, SoftwareSystemType) 
    ON s.Softwarename = t.SoftwareName 
    AND s.SoftwareSystemType = t.SoftwareSystemType
WHEN NOT MATCHED BY TARGET THEN 
    INSERT (SoftwareName, SoftwareSystemType) 
    VALUES (s.SoftwareName, s.SoftwareSystemType);

Example of Merge on SQL Fiddle

How can I do an insert where not exists?

INSERT INTO myTable(columns...)
Select values...
WHERE NOT EXISTS
   (SELECT *
    FROM myTable
    WHERE pk_part1 = value1,
        AND pk_part2 = value2)

Edit:
After reading martins link, If admit, that the best solution is:

BEGIN TRY
    INSERT INTO myTable(columns...)
    values( values...)
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() <> 2627
      RAISERROR etc
END CATCH;

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

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.

T-SQL Insert Where Not Exists(... VALUES() )

You could use refer to that column inside NOT EXISTS:

INSERT INTO table_name (name, address, tele)
SELECT * 
FROM (SELECT 'Nazir', 'Kolkata', '033') AS tmp(name, address, tele)
WHERE NOT EXISTS (
    SELECT name FROM table_name WHERE name = tmp.name
);

I would rewrite it with LEFT JOIN:

INSERT INTO table_name (name, address, tele)
SELECT tmp.name, tmp.address. tmp.tele
FROM (SELECT 'Nazir', 'Kolkata', '033') AS tmp(name, address, tele)
LEFT JOIN table_name t 
  ON t.name = tmp.name
WHERE t.name IS NULL;

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.

INSERT INTO ... VALUES () when not exists

Use insert . . . select and add the condition to the end of the SELECT:

INSERT INTO barrier (barrier_id, usage_id, type_id, currency_id,  modified_date, version_nr)
    SELECT barrier_seq.nextval, usage_id,
           (SELECT type_id FROM types WHERE name = 'My name'),
           5, CURRENT_TIMESTAMP, 3)
    FROM usages
    WHERE id = (SELECT id
                FROM products
                WHERE identifier =
                      (SELECT identifier
                       FROM identifiers
                       WHERE type_id =
                             (SELECT type_id
                              FROM types
                              WHERE NAME = 'New product'
                             ) AND
                             LOCATION = 'USA'
                      ) AND
                      status = 'DONE'
              ) AND
              NOT EXISTS ( . . . );

Insert record if all values do not exist in a row

If you are fine with implementing a general mechanism to avoid duplicates on columns column_a/b/c, you can simply add a composite unique constraint on both columns, like

ALTER TABLE mytable 
ADD CONSTRAINT constr_ID UNIQUE (column_a, column_b, column_c);

If anything operation happens on the table that would generate duplicates, MySQL will raise a constraint violation error. You can ignore the error with the ON DUPLICATE KEY UPDATE option :

INSERT INTO mytable(column_a, column_b, column_c)
VALUES(value_a, value_b, value_c)
ON DUPLICATE KEY UPDATE column_a = column_a;

In this demo on DB Fiddle, we are inserting 3 records with 2 duplicates, and we end up with 2 records in the table, as expected.

---

On the other hand, if you want to restrict the check on duplicate to just one query, and/or if you want to avoid wasting autoincrement sequences on duplicate keys, then you can turn to an INSERT ... SELECT statement, with a WHERE NOT EXISTS condition that does the duplicate check :

INSERT INTO mytable(column_a, column_b, column_c)
SELECT src.*
FROM (SELECT value_a column_a, value_b column_b, value_c column_c) src
WHERE NOT EXISTS (
    SELECT 1 
    FROM mytable
    WHERE 
        column_a = src.column_a 
        AND column_b = src.column_b 
        AND column_c = src.column_c
);

If you attempt to insert a duplicate, the query will do nothing (and will not generate an error or warning).

Demo on DB Fiddle.

MySQL: Insert record if not exists in table

I'm not actually suggesting that you do this, as the UNIQUE index as suggested by Piskvor and others is a far better way to do it, but you can actually do what you were attempting:

CREATE TABLE `table_listnames` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `tele` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

Insert a record:

INSERT INTO table_listnames (name, address, tele)
SELECT * FROM (SELECT 'Rupert', 'Somewhere', '022') AS tmp
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name = 'Rupert'
) LIMIT 1;

Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

SELECT * FROM `table_listnames`;

+----+--------+-----------+------+
| id | name   | address   | tele |
+----+--------+-----------+------+
|  1 | Rupert | Somewhere | 022  |
+----+--------+-----------+------+

Try to insert the same record again:

INSERT INTO table_listnames (name, address, tele)
SELECT * FROM (SELECT 'Rupert', 'Somewhere', '022') AS tmp
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name = 'Rupert'
) LIMIT 1;

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

+----+--------+-----------+------+
| id | name   | address   | tele |
+----+--------+-----------+------+
|  1 | Rupert | Somewhere | 022  |
+----+--------+-----------+------+

Insert a different record:

INSERT INTO table_listnames (name, address, tele)
SELECT * FROM (SELECT 'John', 'Doe', '022') AS tmp
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name = 'John'
) LIMIT 1;

Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

SELECT * FROM `table_listnames`;

+----+--------+-----------+------+
| id | name   | address   | tele |
+----+--------+-----------+------+
|  1 | Rupert | Somewhere | 022  |
|  2 | John   | Doe       | 022  |
+----+--------+-----------+------+

And so on...

---

Update:

To prevent #1060 - Duplicate column name error in case two values may equal, you must name the columns of the inner SELECT:

INSERT INTO table_listnames (name, address, tele)
SELECT * FROM (SELECT 'Unknown' AS name, 'Unknown' AS address, '022' AS tele) AS tmp
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name = 'Rupert'
) LIMIT 1;

Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

SELECT * FROM `table_listnames`;

+----+---------+-----------+------+
| id | name    | address   | tele |
+----+---------+-----------+------+
|  1 | Rupert  | Somewhere | 022  |
|  2 | John    | Doe       | 022  |
|  3 | Unknown | Unknown   | 022  |
+----+---------+-----------+------+

---

Related Topics