INSERT VALUES WHERE NOT EXISTS
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.
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
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);
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 <-- 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 <-- duplicate (row inserted by session #2)