Insérer un enregistrement uniquement si l'enregistrement n'existe pas déjà dans le tableau
Je me demande s'il existe un moyen d'insérer un enregistrement dans une table uniquement si la table ne contient pas déjà cet enregistrement?
Est-il une requête qui va le faire, ou ai-je besoin d'une procédure stockée?
4 réponses
Vous ne dites pas quelle version de SQL Server. Si SQL Server 2008 vous pouvez utiliser MERGE
NB: il est habituel d'utiliser Merge pour un Upsert qui est ce que je pensais à l'origine que la question se posait mais elle est valide sans la clause WHEN MATCHED et juste avec une clause WHEN NOT MATCHED donc fonctionne aussi pour ce cas. Exemple D'Utilisation.
CREATE TABLE #A(
[id] [int] NOT NULL PRIMARY KEY CLUSTERED,
[C] [varchar](200) NOT NULL)
MERGE #A AS target
USING (SELECT 3, 'C') AS source (id, C)
ON (target.id = source.id)
/*Uncomment for Upsert Semantics
WHEN MATCHED THEN
UPDATE SET C = source.C */
WHEN NOT MATCHED THEN
INSERT (id, C)
VALUES (source.id, source.C);
En termes de coûts d'exécution, les deux semblent à peu près égaux lorsqu'un Insert doit être fait...
Lien pour planifier des images pour first exécuter
Mais à la deuxième manche quand il n'y a pas d'insertion à faire, la réponse de Matthew semble moins coûteuse. Je ne suis pas sûr s'il y a un moyen d'améliorer cela.
Lien vers les images de plan pour la deuxième exécution
Script De Test
select *
into #testtable
from master.dbo.spt_values
CREATE UNIQUE CLUSTERED INDEX [ix] ON #testtable([type] ASC,[number] ASC,[name] ASC)
declare @name nvarchar(35)= 'zzz'
declare @number int = 50
declare @type nchar(3) = 'A'
declare @low int
declare @high int
declare @status int = 0;
MERGE #testtable AS target
USING (SELECT @name, @number, @type, @low, @high, @status) AS source (name, number, [type], low, high, [status])
ON (target.[type] = source.[type] AND target.[number] = source.[number] and target.[name] = source.[name] )
WHEN NOT MATCHED THEN
INSERT (name, number, [type], low, high, [status])
VALUES (source.name, source.number, source.[type], source.low, source.high, source.[status]);
set @name = 'yyy'
IF NOT EXISTS
(SELECT *
FROM #testtable
WHERE [type] = @type AND [number] = @number and name = @name)
BEGIN
INSERT INTO #testtable
(name, number, [type], low, high, [status])
VALUES (@name, @number, @type, @low, @high, @status);
END
IF NOT EXISTS
(SELECT {Columns}
FROM {Table}
WHERE {Column1 = SomeValue AND Column2 = SomeOtherVale AND ...})
INSERT INTO {Table} {Values}
En bref, vous avez besoin d'une table garantie pour vous permettre de retourner une ligne:
Insert dbo.Table (Col1, Col2, Col3....
Select 'Value1', 'Value2', 'Value3',....
From Information_Schema.Tables
Where Table_Schema = 'dbo'
And Table_Name = 'Table'
And Not Exists (
Select 1
From dbo.Table
Where Col1 = 'Foo'
And Col2 = 'Bar'
And ....
)
J'ai également vu cette variation dans la nature:
Insert Table (Col1, Col2, Col3....
Select 'Value1', 'Value2', 'Value3'....
From (
Select 1 As Num
) As Z
Where Not Exists (
Select 1
From Table
Where Col1 = Foo
And Col2 = Bar
And ....
)
Je dois voter pour l'ajout d'un CONSTRAINT. C'est la réponse la plus simple et la plus robuste. Je veux dire, en regardant à quel point les autres réponses sont compliquées, je dirais qu'elles sont beaucoup plus difficiles à obtenir (et à garder à droite).
Les inconvénients: [1] Il n'est pas évident en lisant le code que l'unicité est appliquée dans la base de données [2] le code client doit savoir pour attraper une exception. En d'autres termes, le gars à venir après vous pourriez vous demander "comment est-ce travailler?"
Cela mis à part: je m'inquiétais que lancer / attraper l'exception était un succès de performance mais j'ai fait quelques tests (sur SQL Server 2005) et ce n'était pas significatif.