SQL Server 如果不存在则插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20971680/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server Insert if not exists
提问by Francisco Carvalho
I want to insert data into my table, but insert only data that doesn't already exist in my database.
我想将数据插入到我的表中，但只插入我的数据库中尚不存在的数据。
Here is my code:
这是我的代码：
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
And the error is:
错误是：
Msg 156, Level 15, State 1, Procedure EmailsRecebidosInsert, Line 11
Incorrect syntax near the keyword 'WHERE'.
消息 156，级别 15，状态 1，过程 EmailsRecebidosInsert，第 11 行
关键字“WHERE”附近的语法不正确。
回答by Imran Ali Khan
instead of below Code
而不是下面的代码
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
replace with
用。。。来代替
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
Updated :(thanks to @Marc Durdin for pointing)
更新：（感谢@Marc Durdin 的指点）
Note that under high load, this will still sometimes fail, because a second connection can pass the IF NOT EXISTS test before the first connection executes the INSERT, i.e. a race condition. See stackoverflow.com/a/3791506/1836776for a good answer on why even wrapping in a transaction doesn't solve this.
请注意，在高负载下，这有时仍会失败，因为第二个连接可以在第一个连接执行 INSERT 之前通过 IF NOT EXISTS 测试，即竞争条件。请参阅stackoverflow.com/a/3791506/1836776以获得关于为什么即使包装在事务中也不能解决此问题的一个很好的答案。
回答by Francisco Carvalho
For those looking for the fastest way, I recently came across these benchmarkswhere apparently using "INSERT SELECT... EXCEPT SELECT..." turned out to be the fastest for 50 million records or more.
对于那些寻找最快方式的人，我最近遇到了这些基准测试，其中显然使用“INSERT SELECT... EXCEPT SELECT...”对于 5000 万条记录或更多记录来说是最快的。
Here's some sample code from the article (the 3rd block of code was the fastest):
这是文章中的一些示例代码（第三块代码是最快的）：
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
回答by Brett Schneider
I would use a merge:
我会使用合并：
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
回答by SaravanaC
Try below code
试试下面的代码
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
回答by Hovhannes Babayan
I did the same thing with SQL Server 2012 and it worked
我对 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')
回答by marc_s
The INSERTcommand doesn't have a WHEREclause - you'll have to write it like this:
该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
回答by Don
Depending on your version (2012?) of SQL Server aside from the IF EXISTS you can also use MERGElike so:
除了 IF EXISTS 之外，根据您的 SQL Server 版本（2012？），您还可以像这样使用MERGE：
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
回答by Malcolm Swaine
Different SQL, same principle. Only insert if the clause in where not exists fails
不同的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')
回答by vadiraj jahagirdar
As explained in below code:Execute below queries and verify yourself.
如以下代码所述：执行以下查询并验证自己。
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 a record:
插入一条记录：
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 |
+----+--------+-----------+------+
Now, try to insert the same record again:
现在，再次尝试插入相同的记录：
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 a different record:
插入不同的记录：
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 |
+----+--------+-----------+------+
回答by mljm
You could use the GOcommand. That will restart the execution of SQL statements after an error. In my case I have a few 1000 INSERT statements, where a handful of those records already exist in the database, I just don't know which ones.
I found that after processing a few 100, execution just stops with an error message that it can't INSERTas the record already exists. Quite annoying, but putting a GOsolved this. It may not be the fastest solution, but speed was not my problem.
你可以使用GO命令。这将在出错后重新启动 SQL 语句的执行。在我的例子中，我有几个 1000 INSERT 语句，其中一些记录已经存在于数据库中，我只是不知道是哪些。我发现在处理了几个 100 之后，执行只是停止并显示一条错误消息，INSERT因为记录已经存在，所以它不能。很烦人，但放一个GO解决了这个问题。它可能不是最快的解决方案，但速度不是我的问题。
GO
INSERT INTO mytable (C1,C2,C3) VALUES(1,2,3)
GO
INSERT INTO mytable (C1,C2,C3) VALUES(4,5,6)
etc ...
