SQL 如何在插入之前检查记录是否存在以防止重复？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28037539/
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
how to check if the record exists before insert to prevent duplicates?
提问by grace
I want to select CustomerID, OrderTypeID, and LoanNumberfrom tblOrderand insert it with a new OrderTypeIDbut same CustomerIDand LoanNumber.
My query does that but it duplicates the values, every time the query is executed it duplicates the rows.
我想选择CustomerID,OrderTypeID和LoanNumberfromtblOrder并用一个新的OrderTypeID但相同的CustomerIDand插入它LoanNumber。我的查询会这样做，但它会复制值，每次执行查询时都会复制行。
insert into tblOrder (CustomerID, OrderTypeID, LoanNumber)
Select o.CustomerID,3, o.LoanNumber
from tblOrder as o
where o.OrderTypeID = 1;
this is what the query does:
这就是查询的作用：
OrderID 9 - 12 are duplicates or OrderID 5 - 8. The query was executed 2 times hence the records got duplicated.
OrderID 9 - 12 是重复的或 OrderID 5 - 8。查询执行了 2 次，因此记录被重复。
The ideal result should be:
理想的结果应该是：
回答by Solomon Rutzky
The first, and most important, issue here is notthat your query inserts duplicates; it is that your table allowsit to happen in the first place. Hence, you first need to create a UNIQUE INDEX on those 3 fields to disallow duplicates.
这里的第一个也是最重要的问题不是您的查询插入了重复项；首先是你的桌子允许它发生。因此，您首先需要在这 3 个字段上创建一个 UNIQUE INDEX 以禁止重复。
The second issue is how to handle the situation of when an operation attempts to insert a duplicate. You have two main choices:
第二个问题是如何处理操作尝试插入重复项的情况。您有两个主要选择：
You can check for the record's existence first and skip the INSERT if it is found, or
You can set the UNIQUE INDEX to "ignore" duplicates in which case you don't need to check first as the operation will silently fail, with just a warning that the duplicate was not inserted.
您可以先检查记录是否存在，如果找到则跳过 INSERT，或者
您可以将 UNIQUE INDEX 设置为“忽略”重复项，在这种情况下，您无需先检查，因为操作将无声地失败，只显示未插入重复项的警告。
If you pick Option #1(check first), then:
如果您选择选项 #1（首先检查），则：
CREATE UNIQUE NONCLUSTERED INDEX [UIX_tblOrder_CustomerID_OrderTypeID_LoanNumber]
ON [tblOrder]
( [CustomerID] ASC, [OrderTypeID] ASC, [LoanNumber] ASC );
And then:
进而：
INSERT INTO tblOrder (CustomerID, OrderTypeID, LoanNumber)
SELECT o.CustomerID, 3, o.LoanNumber
FROM tblOrder as o
WHERE o.OrderTypeID = 1
AND NOT EXISTS (SELECT *
FROM tblOrder tmp
WHERE tmp.CustomerID = o.CustomerID
AND tmp.OrderTypeID = 3
AND tmp.LoanNumber = o.LoanNumber);
If you pick Option #2(don't check), then:
如果您选择选项 #2（不勾选），则：
CREATE UNIQUE NONCLUSTERED INDEX [UIX_tblOrder_CustomerID_OrderTypeID_LoanNumber]
ON [tblOrder]
( [CustomerID] ASC, [OrderTypeID] ASC, [LoanNumber] ASC )
WITH (IGNORE_DUP_KEY = ON);
And then:
进而：
INSERT INTO tblOrder (CustomerID, OrderTypeID, LoanNumber)
SELECT o.CustomerID, 3, o.LoanNumber
FROM tblOrder as o
WHERE o.OrderTypeID = 1;
Example of how IGNORE_DUP_KEYbehaves:
IGNORE_DUP_KEY行为方式示例：
CREATE TABLE #IgnoreDuplicateTest (Col1 INT);
CREATE UNIQUE NONCLUSTERED INDEX [UIX_#IgnoreDuplicateTest_Col1]
ON #IgnoreDuplicateTest
( [Col1] ASC )
WITH (IGNORE_DUP_KEY = ON);
INSERT INTO #IgnoreDuplicateTest (Col1) VALUES (1);
-- (1 row(s) affected)
INSERT INTO #IgnoreDuplicateTest (Col1) VALUES (1);
-- Duplicate key was ignored.
-- (0 row(s) affected)
INSERT INTO #IgnoreDuplicateTest (Col1)
SELECT tmp.val
FROM (VALUES (1),(2)) AS tmp(val);
-- Duplicate key was ignored.
-- (1 row(s) affected)
SELECT * FROM #IgnoreDuplicateTest;
-- Col1
-- 1
-- 2
回答by koushik veldanda
this may helps you
这可能会帮助你
Here I am checking the values present(which are in variables) are present in table or not, if present leave else insert
在这里，我正在检查表中存在的值（在变量中）是否存在，如果存在，则保留其他插入
Declare @claimid int, @subscriber int, @qualifyinginformation int
set @claimid = '1000008'
set @subscriber = '1'
set @qualifyinginformation = '1'
If Exists (Select * from test1 where claimid = @claimid and subscriber=@subscriber and qualifyinginformation=@qualifyinginformation )
begin
print ('The Value already Exist')
end
else
begin
Insert into test1 (claimid,subscriber,qualifyinginformation) values (@claimid,@subscriber,@qualifyinginformation)
select * from test1
end
回答by Felix Pamittan
Is this what you want?
这是你想要的吗？
CREATE TABLE tblOrder(
OrderID INT IDENTITY(1, 1),
CustomerID INT,
OrderTypeID INT,
LoanNumber INT
)
INSERT INTO tblOrder VALUES
(1, 1, 45584565),
(1, 1, 45566856),
(1, 1, 45565584),
(1, 1, 45588545)
INSERT INTO tblOrder( CustomerID, OrderTypeID, LoanNumber)
SELECT
o.CustomerID,
3,
o.LoanNumber
FROM tblOrder o
WHERE NOT EXISTS(
SELECT 1
FROM tblOrder
WHERE
CustomerID = o.CustomerID
AND OrderTypeID = 3
AND LoanNumber = o.LoanNumber
)
回答by DARSHIL JOSHI
ALTER PROCEDURE [dbo].[InsertName] (
@empname varchar(25),
@email varchar(25)
)
AS
IF EXISTS (
SELECT
'True'
FROM
Employee
WHERE
EmployeeName = @empname
)
BEGIN
SELECT 'This record already exists!'
END
ELSE
BEGIN
INSERT into Employee(EmployeeName, EmailId) VALUES(@empname,@email)
END
EXEC [InsertName]
'Darshil','darshil@gmail'
回答by pony_147
After you set the primary key on some column name, it will check for duplicate entries on that column name and if found simply ignore the duplicate data.
在某些列名上设置主键后，它将检查该列名上的重复条目，如果发现则忽略重复数据。
INSERT IGNORE INTO db_name.table_name(col1, col2, col3) VALUES (%s,%s,%s)
回答by Krish KvR
Is this what you look for ? Make sure
这是你要找的吗？确保
Create table #temp(OrderID int, CustomerID int, OrderTypeID int, LoanNumber int)
INsert into #temp values(1 , 1 , 1 , 45584565)
INsert into #temp values(2 , 1 , 1 , 45566856)
INsert into #temp values(3 , 1 , 1 , 45565584)
INsert into #temp values(4 , 1 , 1 , 45588545)
select * from #temp
UPDATE TE
SET CustomerID=tab.CustomerID,OrderTypeID=3,LoanNumber=tab.LoanNumber
FROM #temp TE inner join
(Select o.OrderID,o.CustomerID,3 tt, o.LoanNumber
from #temp as o
where o.OrderTypeID = 1)tab
ON TE.OrderID = tab.OrderID
select * from #temp
drop table #temp
