我想向表中插入数据，但只插入数据库中不存在的数据。
这是我的代码:
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，级别15，状态1，程序EmailsRecebidosInsert，第11行 关键字WHERE附近的语法不正确。
当前回答
试试下面的代码
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
其他回答
如果你的聚集索引只包含这些字段，那么简单、快速和可靠的选择是使用IGNORE_DUP_KEY
如果您使用IGNORE_DUP_KEY ON创建群集索引
然后你可以使用:
INSERT INTO EmailsRecebidos (De, Assunto, Data) VALUES (@_DE, @_ASSUNTO, @_DATA)
这在任何情况下都是安全的!
试试下面的代码
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
我会使用合并:
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
不同的SQL，相同的原理。仅当where not exists中的子句失败时才插入
INSERT INTO FX_USDJPY
(PriceDate,
PriceOpen,
PriceLow,
PriceHigh,
PriceClose,
TradingVolume,
TimeFrame)
SELECT '2014-12-26 22:00',
120.369000000000,
118.864000000000,
120.742000000000,
120.494000000000,
86513,
'W'
WHERE NOT EXISTS
(SELECT 1
FROM FX_USDJPY
WHERE PriceDate = '2014-12-26 22:00'
AND TimeFrame = 'W')
如果你想检查一个键是否存在，你可以使用:
INSERT INTO tableName (...) VALUES (...)
ON DUPLICATE KEY
UPDATE ...
使用这个，如果已经有一个特定键的条目，那么它将更新，否则，它将插入。
