我想向表中插入数据,但只插入数据库中不存在的数据。
这是我的代码:
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附近的语法不正确。
而不是以下代码
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
而不是以下代码
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。
如下面的代码所解释:执行以下查询并自行验证。
CREATE TABLE `table_name` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
`address` varchar(255) NOT NULL,
`tele` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
插入一个记录:
INSERT INTO table_name (name, address, tele)
SELECT * FROM (SELECT 'Nazir', 'Kolkata', '033') AS tmp
WHERE NOT EXISTS (
SELECT name FROM table_name WHERE name = 'Nazir'
) LIMIT 1;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
SELECT * FROM `table_name`;
+----+--------+-----------+------+
| id | name | address | tele |
+----+--------+-----------+------+
| 1 | Nazir | Kolkata | 033 |
+----+--------+-----------+------+
现在,尝试再次插入相同的记录:
INSERT INTO table_name (name, address, tele)
SELECT * FROM (SELECT 'Nazir', 'Kolkata', '033') AS tmp
WHERE NOT EXISTS (
SELECT name FROM table_name WHERE name = 'Nazir'
) LIMIT 1;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
+----+--------+-----------+------+
| id | name | address | tele |
+----+--------+-----------+------+
| 1 | Nazir | Kolkata | 033 |
+----+--------+-----------+------+
插入一条不同的记录:
INSERT INTO table_name (name, address, tele)
SELECT * FROM (SELECT 'Santosh', 'Kestopur', '044') AS tmp
WHERE NOT EXISTS (
SELECT name FROM table_name WHERE name = 'Santosh'
) LIMIT 1;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
SELECT * FROM `table_name`;
+----+--------+-----------+------+
| id | name | address | tele |
+----+--------+-----------+------+
| 1 | Nazir | Kolkata | 033 |
| 2 | Santosh| Kestopur | 044 |
+----+--------+-----------+------+