SQL Server의 INSERT 또는 UPDATE 솔루션
의 테이블 구조를 가정합니다 MyTable(KEY, datafield1, datafield2...).
종종 기존 레코드를 업데이트하거나 존재하지 않는 경우 새 레코드를 삽입하고 싶습니다.
본질적으로 :
IF (key exists)
run update command
ELSE
run insert command
이것을 작성하는 가장 좋은 방법은 무엇입니까?
거래를 잊지 마세요. 성능은 좋지만 간단한 (존재하는 경우 ..) 접근 방식은 매우 위험합니다.
여러 스레드가 삽입 또는 업데이트를 수행하려고하면 쉽게 기본 키 위반이 발생할 수 있습니다.
@Beau Crawford 및 @Esteban에서 제공하는 솔루션은 일반적인 아이디어를 보여 주지만 오류가 발생하기 쉽습니다.
교착 상태 및 PK 위반을 방지하려면 다음과 같이 사용할 수 있습니다.
begin tran
if exists (select * from table with (updlock,serializable) where key = @key)
begin
update table set ...
where key = @key
end
else
begin
insert into table (key, ...)
values (@key, ...)
end
commit tran
또는
begin tran
update table with (serializable) set ...
where key = @key
if @@rowcount = 0
begin
insert into table (key, ...) values (@key,..)
end
commit tran
매우 유사한 이전 질문에 대한 자세한 답변을 참조하십시오.
@Beau Crawford 's 는 SQL 2005 이하에서 좋은 방법이지만 담당자에게 권한을 부여하는 경우 첫 번째 사람에게 가야 합니다 . 유일한 문제는 삽입의 경우 여전히 두 개의 IO 작업이라는 것입니다.
MS Sql2008 merge은 SQL : 2003 표준을 도입 했습니다.
merge tablename with(HOLDLOCK) as target
using (values ('new value', 'different value'))
as source (field1, field2)
on target.idfield = 7
when matched then
update
set field1 = source.field1,
field2 = source.field2,
...
when not matched then
insert ( idfield, field1, field2, ... )
values ( 7, source.field1, source.field2, ... )
이제는 실제로 하나의 IO 작업이지만 끔찍한 코드 :-(
UPSERT 수행 :
MyTable SET FieldA = @ FieldA WHERE Key = @ Key 업데이트 @@ ROWCOUNT = 0 인 경우 MyTable (FieldA) 값 (@FieldA)에 삽입
http://en.wikipedia.org/wiki/Upsert
많은 사람들이을 (를) 사용하도록 제안 MERGE하지만 사용 하지 않도록주의합니다. 기본적으로 여러 명령문보다 더 이상 동시성 및 경쟁 조건으로부터 사용자를 보호하지는 않지만 다른 위험을 초래합니다.
http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/
이 "간단한"구문을 사용할 수 있어도이 방법을 선호합니다 (간결성을 위해 오류 처리 생략).
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
UPDATE dbo.table SET ... WHERE PK = @PK;
IF @@ROWCOUNT = 0
BEGIN
INSERT dbo.table(PK, ...) SELECT @PK, ...;
END
COMMIT TRANSACTION;
많은 사람들이 다음과 같이 제안합니다.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
IF EXISTS (SELECT 1 FROM dbo.table WHERE PK = @PK)
BEGIN
UPDATE ...
END
ELSE
INSERT ...
END
COMMIT TRANSACTION;
그러나이 모든 작업은 업데이트 할 행을 찾기 위해 테이블을 두 번 읽어야 할 수도 있습니다. 첫 번째 샘플에서는 행을 한 번만 찾으면됩니다. (두 경우 모두 초기 읽기에서 행이 발견되지 않으면 삽입이 발생합니다.)
다른 사람들은 다음과 같이 제안합니다.
BEGIN TRY
INSERT ...
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 2627
UPDATE ...
END CATCH
그러나 거의 모든 삽입이 실패하는 드문 시나리오를 제외하고는 SQL Server가 처음에 방지 할 수있는 예외를 포착하도록하는 것 외에 다른 이유가없는 경우 문제가됩니다. 나는 여기서 많이 증명한다.
- http://www.mssqltips.com/sqlservertip/2632/checking-for-potential-constraint-violations-before-entering-sql-server-try-and-catch-logic/
- http://www.sqlperformance.com/2012/08/t-sql-queries/error-handling
IF EXISTS (SELECT * FROM [Table] WHERE ID = rowID)
UPDATE [Table] SET propertyOne = propOne, property2 . . .
ELSE
INSERT INTO [Table] (propOne, propTwo . . .)
편집하다:
아아, 내 자신에게 해롭지 만, 선택하지 않고이 작업을 수행하는 솔루션이 더 적은 단계로 작업을 수행하기 때문에 더 나은 것 같다는 것을 인정해야합니다.
한 번에 둘 이상의 레코드를 UPSERT하려는 경우 ANSI SQL : 2003 DML 문 MERGE를 사용할 수 있습니다.
MERGE INTO table_name WITH (HOLDLOCK) USING table_name ON (condition)
WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...]
WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...])
SQL Server 2005에서 Mimicking MERGE 문을 확인하십시오 .
이것에 대해 언급하기에는 꽤 늦었지만 MERGE를 사용하여 더 완전한 예제를 추가하고 싶습니다.
이러한 Insert + Update 문은 일반적으로 "Upsert"문이라고하며 SQL Server에서 MERGE를 사용하여 구현할 수 있습니다.
아주 좋은 예가 여기에 있습니다 : http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx
위의 내용은 잠금 및 동시성 시나리오도 설명합니다.
나는 참조를 위해 같은 것을 인용 할 것이다.
ALTER PROCEDURE dbo.Merge_Foo2
@ID int
AS
SET NOCOUNT, XACT_ABORT ON;
MERGE dbo.Foo2 WITH (HOLDLOCK) AS f
USING (SELECT @ID AS ID) AS new_foo
ON f.ID = new_foo.ID
WHEN MATCHED THEN
UPDATE
SET f.UpdateSpid = @@SPID,
UpdateTime = SYSDATETIME()
WHEN NOT MATCHED THEN
INSERT
(
ID,
InsertSpid,
InsertTime
)
VALUES
(
new_foo.ID,
@@SPID,
SYSDATETIME()
);
RETURN @@ERROR;
/*
CREATE TABLE ApplicationsDesSocietes (
id INT IDENTITY(0,1) NOT NULL,
applicationId INT NOT NULL,
societeId INT NOT NULL,
suppression BIT NULL,
CONSTRAINT PK_APPLICATIONSDESSOCIETES PRIMARY KEY (id)
)
GO
--*/
DECLARE @applicationId INT = 81, @societeId INT = 43, @suppression BIT = 0
MERGE dbo.ApplicationsDesSocietes WITH (HOLDLOCK) AS target
--set the SOURCE table one row
USING (VALUES (@applicationId, @societeId, @suppression))
AS source (applicationId, societeId, suppression)
--here goes the ON join condition
ON target.applicationId = source.applicationId and target.societeId = source.societeId
WHEN MATCHED THEN
UPDATE
--place your list of SET here
SET target.suppression = source.suppression
WHEN NOT MATCHED THEN
--insert a new line with the SOURCE table one row
INSERT (applicationId, societeId, suppression)
VALUES (source.applicationId, source.societeId, source.suppression);
GO
테이블 및 필드 이름을 필요한 것으로 바꾸십시오. 사용 ON 상태에 주의하십시오 . 그런 다음 DECLARE 행의 변수에 적절한 값 (및 유형)을 설정하십시오.
건배.
MERGEStatement 를 사용할 수 있습니다 .이 명령문은 데이터가 없으면 데이터를 삽입하거나 존재하면 업데이트하는 데 사용됩니다.
MERGE INTO Employee AS e
using EmployeeUpdate AS eu
ON e.EmployeeID = eu.EmployeeID`
UPDATE if-no-rows-updated 후 INSERT 경로를 수행하는 경우 경쟁 조건을 방지하기 위해 먼저 INSERT를 수행하는 것이 좋습니다 (중간 DELETE가 없다고 가정).
INSERT INTO MyTable (Key, FieldA)
SELECT @Key, @FieldA
WHERE NOT EXISTS
(
SELECT *
FROM MyTable
WHERE Key = @Key
)
IF @@ROWCOUNT = 0
BEGIN
UPDATE MyTable
SET FieldA=@FieldA
WHERE Key=@Key
IF @@ROWCOUNT = 0
... record was deleted, consider looping to re-run the INSERT, or RAISERROR ...
END
Apart from avoiding a race condition, if in most cases the record will already exist then this will cause the INSERT to fail, wasting CPU.
Using MERGE probably preferable for SQL2008 onwards.
That depends on the usage pattern. One has to look at the usage big picture without getting lost in the details. For example, if the usage pattern is 99% updates after the record has been created, then the 'UPSERT' is the best solution.
After the first insert (hit), it will be all single statement updates, no ifs or buts. The 'where' condition on the insert is necessary otherwise it will insert duplicates, and you don't want to deal with locking.
UPDATE <tableName> SET <field>=@field WHERE key=@key;
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO <tableName> (field)
SELECT @field
WHERE NOT EXISTS (select * from tableName where key = @key);
END
MS SQL Server 2008 introduces the MERGE statement, which I believe is part of the SQL:2003 standard. As many have shown it is not a big deal to handle one row cases, but when dealing with large datasets, one needs a cursor, with all the performance problems that come along. The MERGE statement will be much welcomed addition when dealing with large datasets.
In SQL Server 2008 you can use the MERGE statement
Before everyone jumps to HOLDLOCK-s out of fear from these nafarious users running your sprocs directly :-) let me point out that you have to guarantee uniqueness of new PK-s by design (identity keys, sequence generators in Oracle, unique indexes for external ID-s, queries covered by indexes). That's the alpha and omega of the issue. If you don't have that, no HOLDLOCK-s of the universe are going to save you and if you do have that then you don't need anything beyond UPDLOCK on the first select (or to use update first).
Sprocs normally run under very controlled conditions and with the assumption of a trusted caller (mid tier). Meaning that if a simple upsert pattern (update+insert or merge) ever sees duplicate PK that means a bug in your mid-tier or table design and it's good that SQL will yell a fault in such case and reject the record. Placing a HOLDLOCK in this case equals eating exceptions and taking in potentially faulty data, besides reducing your perf.
Having said that, Using MERGE, or UPDATE then INSERT is easier on your server and less error prone since you don't have to remember to add (UPDLOCK) to first select. Also, if you are doing inserts/updates in small batches you need to know your data in order to decide whether a transaction is appropriate or not. It it's just a collection of unrelated records then additional "enveloping" transaction will be detrimental.
Does the race conditions really matter if you first try an update followed by an insert? Lets say you have two threads that want to set a value for key key:
Thread 1: value = 1
Thread 2: value = 2
Example race condition scenario
- key is not defined
- Thread 1 fails with update
- Thread 2 fails with update
- Exactly one of thread 1 or thread 2 succeeds with insert. E.g. thread 1
The other thread fails with insert (with error duplicate key) - thread 2.
- Result: The "first" of the two treads to insert, decides value.
- Wanted result: The last of the 2 threads to write data (update or insert) should decide value
But; in a multithreaded environment, the OS scheduler decides on the order of the thread execution - in the above scenario, where we have this race condition, it was the OS that decided on the sequence of execution. Ie: It is wrong to say that "thread 1" or "thread 2" was "first" from a system viewpoint.
When the time of execution is so close for thread 1 and thread 2, the outcome of the race condition doesn't matter. The only requirement should be that one of the threads should define the resulting value.
For the implementation: If update followed by insert results in error "duplicate key", this should be treated as success.
Also, one should of course never assume that value in the database is the same as the value you wrote last.
I had tried below solution and it works for me, when concurrent request for insert statement occurs.
begin tran
if exists (select * from table with (updlock,serializable) where key = @key)
begin
update table set ...
where key = @key
end
else
begin
insert table (key, ...)
values (@key, ...)
end
commit tran
You can use this query. Work in all SQL Server editions. It's simple, and clear. But you need use 2 queries. You can use if you can't use MERGE
BEGIN TRAN
UPDATE table
SET Id = @ID, Description = @Description
WHERE Id = @Id
INSERT INTO table(Id, Description)
SELECT @Id, @Description
WHERE NOT EXISTS (SELECT NULL FROM table WHERE Id = @Id)
COMMIT TRAN
NOTE: Please explain answer negatives
If you use ADO.NET, the DataAdapter handles this.
If you want to handle it yourself, this is the way:
Make sure there is a primary key constraint on your key column.
Then you:
- Do the update
- If the update fails because a record with the key already exists, do the insert. If the update does not fail, you are finished.
You can also do it the other way round, i.e. do the insert first, and do the update if the insert fails. Normally the first way is better, because updates are done more often than inserts.
Doing an if exists ... else ... involves doing two requests minimum (one to check, one to take action). The following approach requires only one where the record exists, two if an insert is required:
DECLARE @RowExists bit
SET @RowExists = 0
UPDATE MyTable SET DataField1 = 'xxx', @RowExists = 1 WHERE Key = 123
IF @RowExists = 0
INSERT INTO MyTable (Key, DataField1) VALUES (123, 'xxx')
I usually do what several of the other posters have said with regard to checking for it existing first and then doing whatever the correct path is. One thing you should remember when doing this is that the execution plan cached by sql could be nonoptimal for one path or the other. I believe the best way to do this is to call two different stored procedures.
FirstSP: If Exists Call SecondSP (UpdateProc) Else Call ThirdSP (InsertProc)
Now, I don't follow my own advice very often, so take it with a grain of salt.
Do a select, if you get a result, update it, if not, create it.
참고URL : https://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server
'Development Tip' 카테고리의 다른 글
| JSON의 여러 줄 문자열 (0) | 2020.10.03 |
|---|---|
| Windows Forms 응용 프로그램에서 응용 프로그램 설정을 저장하는 방법은 무엇입니까? (0) | 2020.10.03 |
| Django에서 디버깅하는 방법, 좋은 방법? (0) | 2020.10.03 |
| Linux, Bash의 Epoch 이후 현재 시간을 초 단위로 가져옵니다. (0) | 2020.10.03 |
| Node.js로 명령 줄 바이너리 실행 (0) | 2020.10.03 |