我想在我的表中插入数据,但只插入我的数据库中不存在的数据!
这是我的代码:
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
错误是:
消息156,级别15,状态1,过程EmailsRecebidosInsert,第11行
关键字'WHERE'附近的语法不正确.
Imran Ali Kh.. 289
而不是代码
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,以获得有关为什么即使在事务中包装也无法解决此问题的答案.
对于那些寻找最快速方式的人来说,我最近遇到了这些基准测试,显然使用"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
我会使用合并:
create PROCEDURE [dbo].[EmailsRecebidosInsert] (@_DE nvarchar(50), @_ASSUNTO nvarchar(50), @_DATA nvarchar(30) ) AS BEGIN with data as (select @_DE as de, @_ASSUNTO as assunto, @_DATA as data) merge EmailsRecebidos t using data s on s.de = t.de and s.assunte = t.assunto and s.data = t.data when not matched by target then insert (de, assunto, data) values (s.de, s.assunto, s.data); END
试试下面的代码
ALTER PROCEDURE [dbo].[EmailsRecebidosInsert] (@_DE nvarchar(50), @_ASSUNTO nvarchar(50), @_DATA nvarchar(30) ) AS BEGIN INSERT INTO EmailsRecebidos (De, Assunto, Data) select @_DE, @_ASSUNTO, @_DATA EXCEPT SELECT De, Assunto, Data from EmailsRecebidos END
而不是代码
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,以获得有关为什么即使在事务中包装也无法解决此问题的答案.
该INSERT
命令没有WHERE
子句 - 你必须这样写:
ALTER PROCEDURE [dbo].[EmailsRecebidosInsert] (@_DE nvarchar(50), @_ASSUNTO nvarchar(50), @_DATA nvarchar(30) ) AS 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
除了IF EXISTS之外,您还可以使用MERGE,具体取决于您的SQL Server版本(2012?):
ALTER PROCEDURE [dbo].[EmailsRecebidosInsert] ( @_DE nvarchar(50) , @_ASSUNTO nvarchar(50) , @_DATA nvarchar(30)) AS BEGIN MERGE [dbo].[EmailsRecebidos] [Target] USING (VALUES (@_DE, @_ASSUNTO, @_DATA)) [Source]([De], [Assunto], [Data]) ON [Target].[De] = [Source].[De] AND [Target].[Assunto] = [Source].[Assunto] AND [Target].[Data] = [Source].[Data] WHEN NOT MATCHED THEN INSERT ([De], [Assunto], [Data]) VALUES ([Source].[De], [Source].[Assunto], [Source].[Data]); END
我使用SQL SERVER 2012做了同样的事情并且它起作用了
Insert into #table1 With (ROWLOCK) (Id, studentId, name) SELECT '18769', '2', 'Alex' WHERE not exists (select * from #table1 where Id = '18769' and studentId = '2')