INSERT VALUES WHERE NOT EXISTS

Question

OK so I m trying to improve my asp data entry page to ensure that the entry going into my data table is unique   So in this table I have SoftwareName and SoftwareType  I m trying to get it so if the entry page sends an insert query with parameters that match whats in the table  so same title and type  then an error is thrown up and the Data isn t entered   Something like this   INSERT INTO tblSoftwareTitles               SoftwareName                SoftwareSystemType               VALUES  SoftwareName  SoftwareType               WHERE NOT EXISTS  SELECT SoftwareName              FROM tblSoftwareTitles              WHERE Softwarename    SoftwareName              AND SoftwareType    Softwaretype    So this syntax works great for selecting columns from one table into another without duplicates being entered but doesn t seem to want to work with a parametrized insert query  Can anyone help me out with this   Edit   Here s the code I m using in my ASP insert method      private void ExecuteInsert string name  string type          Creates a new connection using the HWM string     using  SqlConnection HWM   new SqlConnection GetConnectionStringHWM                     Creates a sql string with parameters         string sql     INSERT INTO tblSoftwareTitles                           SoftwareName                            SoftwareSystemType                           SELECT                           SoftwareName                            SoftwareType                          WHERE   NOT EXISTS                             SELECT  1                          FROM tblSoftwareTitles                          WHERE Softwarename    SoftwareName                          AND SoftwareSystemType    Softwaretype                          Opens the connection         HWM Open            try                         Creates a Sql command             using  SqlCommand addSoftware   new SqlCommand                  CommandType   CommandType Text                  Connection   HWM                  CommandTimeout   300                  CommandText   sql                                   adds parameters to the Sql command                 addSoftware Parameters Add   SoftwareName   SqlDbType NVarChar  200  Value   name                  addSoftware Parameters Add   SoftwareType   SqlDbType Int  Value   type                    Executes the Sql                 addSoftware ExecuteNonQuery                              Alert Show  Software title saved                       catch  System Data SqlClient SqlException ex                        string msg    Insert Error                msg    ex Message              throw new Exception msg

User · Answer

There is a great solution for this problem ,You can use the Merge Keyword of Sql

Merge MyTargetTable hba
USING (SELECT Id = 8, Name = 'Product Listing Message') temp 
ON temp.Id = hba.Id
WHEN NOT matched THEN 
INSERT (Id, Name) VALUES (temp.Id, temp.Name);

You can check this before following, below is the sample

IF OBJECT_ID ('dbo.TargetTable') IS NOT NULL
    DROP TABLE dbo.TargetTable
GO

CREATE TABLE dbo.TargetTable
    (
    Id   INT NOT NULL,
    Name VARCHAR (255) NOT NULL,
    CONSTRAINT PK_TargetTable PRIMARY KEY (Id)
    )
GO



INSERT INTO dbo.TargetTable (Name)
VALUES ('Unknown')
GO

INSERT INTO dbo.TargetTable (Name)
VALUES ('Mapping')
GO

INSERT INTO dbo.TargetTable (Name)
VALUES ('Update')
GO

INSERT INTO dbo.TargetTable (Name)
VALUES ('Message')
GO

INSERT INTO dbo.TargetTable (Name)
VALUES ('Switch')
GO

INSERT INTO dbo.TargetTable (Name)
VALUES ('Unmatched')
GO

INSERT INTO dbo.TargetTable (Name)
VALUES ('ProductMessage')
GO


Merge MyTargetTable hba
USING (SELECT Id = 8, Name = 'Listing Message') temp 
ON temp.Id = hba.Id
WHEN NOT matched THEN 
INSERT (Id, Name) VALUES (temp.Id, temp.Name);

User · Answer

More of a comment link for suggested further reading   A really good blog article which benchmarks various ways of accomplishing this task can be found here   They use a few techniques   Insert Where Not Exists    Merge  statement   Insert Except   and your typical  left join  to see which way is the fastest to accomplish this task    The example code used for each technique is as follows  straight copy paste from their page     INSERT INTO  table1  Id  guidd  TimeAdded  ExtraData  SELECT Id  guidd  TimeAdded  ExtraData FROM  table2 WHERE NOT EXISTS  Select Id  guidd From  table1 WHERE  table1 id    table2 id  ----------------------------------- MERGE  table1 as  Target  USING   select Id  guidd  TimeAdded  ExtraData from  table2  as  Source   id  guidd  TimeAdded  ExtraData      on  Target  id   Source  id WHEN NOT MATCHED THEN     INSERT  id  guidd  TimeAdded  ExtraData      VALUES   Source  id   Source  guidd   Source  TimeAdded   Source  ExtraData   ------------------------------ INSERT INTO  table1  id  guidd  TimeAdded  ExtraData  SELECT id  guidd  TimeAdded  ExtraData from  table2 EXCEPT SELECT id  guidd  TimeAdded  ExtraData from  table1 ------------------------------ INSERT INTO  table1  id  guidd  TimeAdded  ExtraData  SELECT  table2 id   table2 guidd   table2 TimeAdded   table2 ExtraData FROM  table2 LEFT JOIN  table1 on  table1 id    table2 id WHERE  table1 id is null   It s a good read for those who are looking for speed  On SQL 2014  the Insert-Except method turned out to be the fastest for 50 million or more records

User · Answer

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

User · Answer

Ingnoring the duplicated unique constraint isn t a solution   INSERT IGNORE INTO tblSoftwareTitles

User · Answer

This isn t an answer  I just want to show that IF NOT EXISTS      INSERT method isn t safe  You have to execute first Session  1 and then Session  2  After v  2 you will see that without an UNIQUE index you could  get duplicate pairs  SoftwareName SoftwareSystemType   Delay from session  1 is used to give you enough time to execute the second script  session  2   You could reduce this delay   Session  1  SSMS   New Query   F5  Execute    CREATE DATABASE DemoEXISTS  GO USE DemoEXISTS  GO CREATE TABLE dbo Software      SoftwareID INT PRIMARY KEY      SoftwareName NCHAR 400  NOT NULL        SoftwareSystemType NVARCHAR 50  NOT NULL    GO  INSERT INTO dbo Software SoftwareID SoftwareName SoftwareSystemType  VALUES  1  Dynamics AX 2009   ERP    INSERT INTO dbo Software SoftwareID SoftwareName SoftwareSystemType  VALUES  2  Dynamics NAV 2009   SCM    INSERT INTO dbo Software SoftwareID SoftwareName SoftwareSystemType  VALUES  3  Dynamics CRM 2011   CRM    INSERT INTO dbo Software SoftwareID SoftwareName SoftwareSystemType  VALUES  4  Dynamics CRM 2013   CRM    INSERT INTO dbo Software SoftwareID SoftwareName SoftwareSystemType  VALUES  5  Dynamics CRM 2015   CRM    GO    CREATE UNIQUE INDEX IUN Software SoftwareName SoftareSystemType ON dbo Software SoftwareName SoftwareSystemType   GO     -- Session  1 BEGIN TRANSACTION      UPDATE  dbo Software     SET     SoftwareName  Dynamics CRM               SoftwareSystemType  CRM          WHERE   SoftwareID 5       WAITFOR DELAY  00 00 15  -- 15 seconds delay  you have less than 15 seconds to switch SSMS window to session  2      UPDATE  dbo Software     SET     SoftwareName  Dynamics AX               SoftwareSystemType  ERP      WHERE   SoftwareID 1  COMMIT --ROLLBACK PRINT  Session  1 results    SELECT   FROM dbo Software    Session  2  SSMS   New Query   F5  Execute    USE DemoEXISTS  GO -- Session  2 DECLARE       SoftwareName NVARCHAR 100          SoftwareSystemType NVARCHAR 50   SELECT      SoftwareName N Dynamics AX        SoftwareSystemType N ERP    PRINT  Session  2 results    IF NOT EXISTS SELECT       FROM dbo Software s     WHERE s SoftwareName  SoftwareName      AND s SoftwareSystemType  SoftwareSystemType  BEGIN     PRINT  Session  2  INSERT        INSERT INTO dbo Software SoftwareID SoftwareName SoftwareSystemType      VALUES  6  SoftwareName  SoftwareSystemType   END  PRINT  Session  2  FINISH   SELECT     FROM    dbo Software    Results   Session  1 results  SoftwareID  SoftwareName      SoftwareSystemType ----------- ----------------- ------------------ 1           Dynamics AX       ERP 2           Dynamics NAV 2009 SCM 3           Dynamics CRM 2011 CRM 4           Dynamics CRM 2013 CRM 5           Dynamics CRM      CRM  Session  2 results  Session  2  INSERT Session  2  FINISH SoftwareID  SoftwareName      SoftwareSystemType ----------- ----------------- ------------------ 1           Dynamics AX       ERP  lt -- duplicate  row updated by session  1  2           Dynamics NAV 2009 SCM 3           Dynamics CRM 2011 CRM 4           Dynamics CRM 2013 CRM 5           Dynamics CRM      CRM 6           Dynamics AX       ERP  lt -- duplicate  row inserted by session  2

[sql] INSERT VALUES WHERE NOT EXISTS

Examples related to sql

Examples related to sql-server