의 테이블 구조를 가정합니다 MyTable(KEY, datafield1, datafield2...).
기존 레코드를 업데이트하거나 존재하지 않는 경우 새 레코드를 삽입하려고합니다.
본질적으로 :
IF (key exists)
run update command
ELSE
run insert command이것을 작성하는 가장 좋은 방법은 무엇입니까?
답변
거래를 잊지 마십시오. 성능은 좋지만 단순 (IF EXISTS ..) 접근 방식은 매우 위험합니다.
여러 스레드가 삽입 또는 업데이트를 수행하려고 할 때 기본 키 위반을 쉽게 얻을 수 있습니다.
@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 설정 업데이트 FieldA = @ FieldA WHERE Key = @ Key @@ ROWCOUNT = 0 인 경우 MyTable에 삽입 (FieldA) 값 (@FieldA)
답변
많은 사람들이 당신에게 사용을 제안 할 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에서 예외를 포착하도록 허용하는 것 외에 다른 이유가 없다면 훨씬 더 비쌉니다. 나는 여기에서 많은 것을 증명합니다.
답변
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에서 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;