SQL Server如果不存在则插入
SQL Server如果不存在则插入
我想向我的表中插入数据,但只插入数据库中不存在的数据。
这是我的代码:
ALTER PROCEDURE [dbo].[EmailsRecebidosInsert] (@_DE nvarchar(50), @_ASSUNTO nvarchar(50), @_DATA nvarchar(30) ) AS 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
错误信息是:
Msg 156, Level 15, State 1, Procedure EmailsRecebidosInsert, Line 11
关键字“WHERE”附近的语法不正确。
admin 更改状态以发布 2023年5月23日
对于那些寻求最快方法的人,我最近找到了这些基准测试,显然使用“INSERT SELECT... EXCEPT SELECT...”对于5000万条记录或更多是最快的。
这是一篇文章中的一些示例代码(第3个代码块是最快的):
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
替代以下代码:
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
用以下代码替换:
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
更新:(感谢@Marc Durdin指出)
请注意,在高负载下,这仍然可能会失败,因为第二个连接可以在第一个连接执行INSERT之前通过IF NOT EXISTS测试,即竞争条件。请参见stackoverflow.com/a/3791506/1836776,其中详细解释了即使在交易中包装也无法解决这个问题的原因。