How to Do an Insert 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 - 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 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.

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  |
+----+---------+-----------+------+

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.

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;

MySQL: insert where not exists

Use insert . . . select:

INSERT INTO USER (name, email)
    SELECT 'John', 'john@mmm.com'
    WHERE NOT EXISTS
        (SELECT id FROM USER WHERE email = 'john@mmm.com');

I would write this as:

INSERT INTO USER (name, email)
    SELECT name, email
    FROM (SELECT 'John' as name, 'john@mmm.com' as email) t
    WHERE NOT EXISTS (SELECT 1 FROM USER u WHERE u.email = t.email);

But a better approach is probably to just put in a unique index so the database protects the data:

create unique index idx_users_email on user(email);