SQL Server의 INSERT 또는 UPDATE 솔루션

IT story

SQL Server의 INSERT 또는 UPDATE 솔루션

hot-time 2020. 2. 9. 19:24

SQL Server의 INSERT 또는 UPDATE 솔루션

의 테이블 구조를 가정합니다 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)

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가 예외를 포착하도록 허용하는 것 외에 다른 이유가 없다면 훨씬 더 비쌉니다. 나는 여기에서 많은 것을 증명합니다.

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;

/*
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 행의 변수에 적절한 값 (및 유형)을 설정하십시오.

건배.

MERGE명령문 을 사용할 수 있습니다 .이 명령문은 존재하지 않는 경우 데이터를 삽입하거나 존재하는 경우 업데이트하는 데 사용됩니다.

MERGE INTO Employee AS e
using EmployeeUpdate AS eu
ON e.EmployeeID = eu.EmployeeID`

행이 업데이트되지 않은 경우 UPDATE 행으로 이동 한 다음 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

경쟁 조건을 피하는 것 외에도 대부분의 경우 레코드가 이미 존재하면 INSERT가 실패하여 CPU가 낭비됩니다.

SQL2008 이후에는 MERGE를 사용하는 것이 좋습니다.

사용 패턴에 따라 다릅니다. 세부 사항에서 길을 잃지 않고 사용 큰 그림을 봐야합니다. 예를 들어, 레코드가 작성된 후 사용 패턴이 99 % 업데이트 인 경우 'UPSERT'가 최상의 솔루션입니다.

첫 번째 삽입 (적중) 후에는 모든 단일 명령문 업데이트가되며 if 또는 buts는 없습니다. 인서트의 'where'조건이 필요합니다. 그렇지 않으면 복제본이 삽입되므로 잠금을 처리하고 싶지 않습니다.

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에는 MERGE 문이 도입되었으며 이는 SQL : 2003 표준의 일부라고 생각합니다. 많은 사람들이 보여 주듯이 한 행 사례를 처리하는 것은 큰 문제가 아니지만 큰 데이터 세트를 처리 할 때 발생하는 모든 성능 문제와 함께 커서가 필요합니다. MERGE 문은 큰 데이터 세트를 처리 할 때 추가로 환영받을 것입니다.

SQL Server 2008에서는 MERGE 문을 사용할 수 있습니다

모든 사람들이 sprocs를 직접 실행하는 악의적 인 사용자로부터 두려움으로부터 HOLDLOCK-s로 뛰어 들기 전에 :-) 디자인 (신분 키, Oracle의 시퀀스 생성기, 고유 인덱스)에 의해 새로운 PK의 고유성을 보장해야 한다고 지적했습니다. 외부 ID, 색인으로 처리 된 쿼리). 이것이 문제의 알파와 오메가입니다. 당신이 그것을 가지고 있지 않다면, 우주의 HOLDLOCK-s가 당신을 구하지 않을 것이고 당신이 그것을 가지고 있다면 당신은 첫 번째 선택에서 UPDLOCK 이외의 것을 필요로하지 않습니다 (또는 먼저 업데이트를 사용하기 위해).

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.

MERGE 또는 UPDATE를 사용하면 서버에서 INSERT가 더 쉬우 며 먼저 선택할 때 (UPDLOCK)을 추가 할 필요가 없으므로 오류가 덜 발생합니다. 또한 작은 배치로 삽입 / 업데이트를 수행하는 경우 트랜잭션이 적절한 지 여부를 결정하기 위해 데이터를 알아야합니다. 그것은 단지 관련없는 레코드의 모음 일뿐입니다. 그러면 추가적인 "봉투"트랜잭션은 해로울 것입니다.

업데이트 후 삽입을 시도하면 경쟁 조건이 실제로 중요합니까? 키 키 의 값을 설정하려는 두 개의 스레드가 있다고 가정 해 보겠습니다 .

스레드 1 : 값 = 1
스레드 2 : 값 = 2

경쟁 조건 시나리오 예

키 가 정의되지 않았습니다
스레드 1이 업데이트 실패
스레드 2가 업데이트 실패
스레드 1 또는 스레드 2 중 정확히 하나가 삽입에 성공합니다. 예를 들어 실 1
다른 스레드는 삽입 (오류 중복 키)-스레드 2로 실패합니다.
- 결과 : 삽입 할 두 트레드의 "첫 번째"값을 결정합니다.
- 원하는 결과 : 데이터 쓰기 (업데이트 또는 삽입)를위한 마지막 2 개의 스레드가 값을 결정해야합니다.

그러나; 다중 스레드 환경에서 OS 스케줄러는 스레드 실행 순서를 결정합니다. 위의 시나리오에서이 경쟁 조건이있는 경우에는 실행 순서를 결정한 OS였습니다. 즉, "스레드 1"또는 "스레드 2"가 시스템 관점에서 "먼저"라고 말하는 것은 잘못입니다.

스레드 1과 스레드 2에 대해 실행 시간이 너무 가까워지면 경쟁 조건의 결과는 중요하지 않습니다. 스레드 중 하나가 결과 값을 정의해야한다는 것이 유일한 요구 사항입니다.

구현의 경우 : update 다음에 insert로 인해 "중복 키"오류가 발생하면 성공으로 간주해야합니다.

또한 데이터베이스의 값이 마지막으로 쓴 값과 동일하다고 가정해서는 안됩니다.

나는 아래 해결책을 시도했고 insert 문에 대한 동시 요청이 발생하면 나를 위해 일한다.

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

이 쿼리를 사용할 수 있습니다. 모든 SQL Server 버전에서 작동합니다. 간단하고 명확합니다. 그러나 두 개의 쿼리를 사용해야합니다. 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

참고 : 답변 부정적인 것을 설명하십시오

ADO.NET을 사용하면 DataAdapter가이를 처리합니다.

직접 처리하려면 다음과 같이하십시오.

키 열에 기본 키 제약 조건이 있는지 확인하십시오.

그럼 당신은 :

업데이트를
키가있는 레코드가 이미 존재하여 업데이트에 실패하면 삽입을 수행하십시오. 업데이트가 실패하지 않으면 완료된 것입니다.

다른 방법으로도 할 수 있습니다. 즉, 먼저 삽입을 수행하고 삽입이 실패하면 업데이트를 수행하십시오. 일반적으로 첫 번째 방법은 삽입보다 업데이트가 더 자주 수행되므로 더 좋습니다.

if 존재하는 경우 ... else ...는 최소 두 개의 요청 (하나는 확인하고 하나는 조치를 취해야 함)을 수행합니다. 다음 방법은 레코드가 존재하는 곳에 하나만 필요하며, 삽입이 필요한 경우에는 두 가지가 필요합니다

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')

나는 보통 기존의 포스터를 먼저 확인하고 올바른 경로가 무엇이든간에 다른 포스터 중 몇 가지가 말한 것을 수행합니다. 이 작업을 수행 할 때 기억해야 할 것은 sql에 의해 캐시 된 실행 계획이 한 경로 나 다른 경로에 대해 최적이 아닐 수 있다는 것입니다. 가장 좋은 방법은 두 가지 저장 프로 시저를 호출하는 것입니다.

FirstSP :
존재하는 경우
   SecondSP 호출 (UpdateProc)
그밖에
   ThirdSP 호출 (InsertProc)

지금, 나는 종종 내 자신의 조언을 따르지 않으므로 소금 한 알을 가져 가십시오.

선택하면 결과가 나오면 업데이트하고 그렇지 않은 경우 업데이트하십시오.

참고 URL : https://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server

'IT story' 카테고리의 다른 글

JSON의 여러 줄 문자열 (0)	2020.02.09
Windows Forms 응용 프로그램에서 응용 프로그램 설정을 저장하는 방법은 무엇입니까? (0)	2020.02.09
장고에서 디버깅하는 좋은 방법? (0)	2020.02.09
Linux, Bash의 Epoch 이후 현재 시간 (초)을 가져옵니다. (0)	2020.02.09
Node.js로 명령 행 바이너리 실행 (0)	2020.02.09

현재글SQL Server의 INSERT 또는 UPDATE 솔루션

hot-time

SQL Server의 INSERT 또는 UPDATE 솔루션

SQL Server의 INSERT 또는 UPDATE 솔루션

'IT story' 카테고리의 다른 글

'IT story'의 다른글

티스토리툴바

SQL Server의 INSERT 또는 UPDATE 솔루션

SQL Server의 INSERT 또는 UPDATE 솔루션

'IT story' 카테고리의 다른 글

'IT story'의 다른글

관련글

티스토리툴바