복붙노트

[SQL] SQL Server에서 INSERT 또는 UPDATE를위한 솔루션

SQL

SQL Server에서 INSERT 또는 UPDATE를위한 솔루션

(KEY ..., datafield1, datafield2)을 MyTable의 테이블 구조를 가정한다.

종종 나도 갱신 기존 기록을 원하거나 새 레코드 삽입이 존재하지 않는 경우.

기본적으로 :

IF (key exists)
  run update command
ELSE
  run insert command

이 쓸 수있는 가장 좋은 수행 방법은 무엇입니까?

해결법

  1. ==============================

    1.거래에 대해 잊지 마세요. 성능이 접근 방식은 매우 위험 (IF가 존재 ..) 좋은, 그러나 간단하다. 여러 스레드가 삽입 또는 갱신을 수행하려고 할 때 당신은 쉽게 할 수 기본 키 위반을 얻는다.

    거래에 대해 잊지 마세요. 성능이 접근 방식은 매우 위험 (IF가 존재 ..) 좋은, 그러나 간단하다. 여러 스레드가 삽입 또는 갱신을 수행하려고 할 때 당신은 쉽게 할 수 기본 키 위반을 얻는다.

    @Beau 크로포드 & @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
  2. ==============================

    2.매우 유사한 앞의 질문에 내 자세한 답변을 참조하십시오

    매우 유사한 앞의 질문에 내 자세한 답변을 참조하십시오

    당신이 담당자를 부여하는 경우가 SO 그것에 최초의 남자로 이동해야하지만 @Beau 크로포드의는 아래 좋은의 SQL 2005의 방법이고. 유일한 문제는 삽입을 위해 여전히 두 개의 IO 작업 점이다.

    MS SQL2008의 소개는 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 작업,하지만 끔찍한 코드입니다 :-(

  3. ==============================

    3.UPSERT를 수행합니다 :

    UPSERT를 수행합니다 :

    UPDATE MyTable SET FieldA=@FieldA WHERE Key=@Key

IF @@ROWCOUNT = 0
   INSERT INTO MyTable (FieldA) VALUES (@FieldA)

    http://en.wikipedia.org/wiki/Upsert

  4. ==============================

    4.많은 사람들은 MERGE를 사용하는 것이 좋습니다 것입니다,하지만 난 그것에 대하여 당신을 경고한다. 기본적으로 여러 문장보다 더 동시성 및 경쟁 조건에서 당신을 보호하지 않지만, 다른 위험을 소개 않습니다 :

    많은 사람들은 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 서버 캐치 예외를시키는 것보다 다른 이유 그러나,이 문제가된다. 나는 많은 여기로 증명 :

  5. ==============================

    5.

    IF EXISTS (SELECT * FROM [Table] WHERE ID = rowID)
UPDATE [Table] SET propertyOne = propOne, property2 . . .
ELSE
INSERT INTO [Table] (propOne, propTwo . . .)

    편집하다:

    아아, 심지어 내 자신의 손해, 나는 그들이 한 적은 단계로 작업을 수행하기 때문에 더 나은 것 같다 선별하지 않고이 작업을 수행 할 솔루션을 인정해야합니다.

  6. ==============================

    6.2003 DML 문을 병합 : 당신은 한 번에 두 개 이상의 레코드를 UPSERT하려면 당신은 ANSI SQL을 사용할 수 있습니다.

    2003 DML 문을 병합 : 당신은 한 번에 두 개 이상의 레코드를 UPSERT하려면 당신은 ANSI SQL을 사용할 수 있습니다.

    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에서 모방 병합 정책을 확인하세요.

  7. ==============================

    7.그 꽤 늦게에 대한 언급을 나는 MERGE를 사용하여보다 완전한 예제를 추가 할 수 있지만.

    그 꽤 늦게에 대한 언급을 나는 MERGE를 사용하여보다 완전한 예제를 추가 할 수 있지만.

    이러한 삽입 + 업데이트 문은 일반적으로 "Upsert"문이라고 및 SQL 서버에서 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;
  8. ==============================

    8.

    /*
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 라인 변수에 대해 적절한 값 (입력)을 설정.

    건배.

  9. ==============================

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

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

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

    10.경쟁 조건을 방지하기 위해 먼저 INSERT를하고 고려, 다음 INSERT 경로를 더-행이 업데이트 된 경우-업데이트를 진행하지 않는 경우 (더는 DELETE 개입이 없다는 가정하에)

    경쟁 조건을 방지하기 위해 먼저 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를 낭비하고, 실패합니다, 경쟁 조건을 피하십시오.

    이후 SQL 2008 MERGE 가능성이 바람직 사용.

  11. ==============================

    11.즉, 사용 패턴에 따라 달라집니다. 하나는 세부 사항에서 길을 잃지 않고 사용 큰 그림을보고있다. 사용 패턴은 99 %의 업데이트 인 경우 레코드를 만든 후 예를 들어, 다음 'UPSERT는'최고의 솔루션입니다.

    즉, 사용 패턴에 따라 달라집니다. 하나는 세부 사항에서 길을 잃지 않고 사용 큰 그림을보고있다. 사용 패턴은 99 %의 업데이트 인 경우 레코드를 만든 후 예를 들어, 다음 'UPSERT는'최고의 솔루션입니다.

    첫 삽입 (히트) 한 후, 모든 하나의 문 업데이트, 아니 IFS 또는 꾸물 거리지 될 것입니다. 삽입의 '어디에'조건은 중복을 삽입합니다 그렇지 않으면 필요가있다, 당신은 잠금 처리하고 싶지 않아요.

    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
  12. ==============================

    12.MS SQL 서버 2008을 소개합니다 나는 SQL의 한 부분이라고 생각 MERGE 문 : 2003 표준. 많은 사람들이 한 행의 경우를 처리하기 위해 큰 문제가 아니지만, 대규모 데이터 세트를 처리 할 때, 사람이 따라 와서 모든 성능 문제로, 커서를 필요로 표시된 것처럼. 대규모 데이터 세트를 처리 할 때 병합 문은 더 추가를 환영합니다.

    MS SQL 서버 2008을 소개합니다 나는 SQL의 한 부분이라고 생각 MERGE 문 : 2003 표준. 많은 사람들이 한 행의 경우를 처리하기 위해 큰 문제가 아니지만, 대규모 데이터 세트를 처리 할 때, 사람이 따라 와서 모든 성능 문제로, 커서를 필요로 표시된 것처럼. 대규모 데이터 세트를 처리 할 때 병합 문은 더 추가를 환영합니다.

  13. ==============================

    13.모두가 점프하기 전에 직접 sprocs가 실행이 nafarious 사용자의 두려움 HOLDLOCK-S :-) 내가 당신이 새의 보증 고유성에이 점을 지적하자 설계 PK-S (식별 키, 오라클에서 시퀀스 생성기, 고유 인덱스 외부 ID-S, 인덱스에 포함 쿼리). 즉 알파와 문제의 오메가입니다. 당신이없는 경우, 우주의 더 HOLDLOCK-S는 당신을 구원하려고하지 않습니다 그리고 당신은에 UPDLOCK 넘어 그 다음 필요하지 않은 것도이있는 경우 첫 번째 (첫 번째 또는 업데이트를 사용하는)을 선택합니다.

    모두가 점프하기 전에 직접 sprocs가 실행이 nafarious 사용자의 두려움 HOLDLOCK-S :-) 내가 당신이 새의 보증 고유성에이 점을 지적하자 설계 PK-S (식별 키, 오라클에서 시퀀스 생성기, 고유 인덱스 외부 ID-S, 인덱스에 포함 쿼리). 즉 알파와 문제의 오메가입니다. 당신이없는 경우, 우주의 더 HOLDLOCK-S는 당신을 구원하려고하지 않습니다 그리고 당신은에 UPDLOCK 넘어 그 다음 필요하지 않은 것도이있는 경우 첫 번째 (첫 번째 또는 업데이트를 사용하는)을 선택합니다.

    sprocs가 일반적으로 매우 통제 된 조건 하에서 및 신뢰할 수있는 발신자 (중간 계층)의 가정으로 실행합니다. 의미 그 간단한 upsert 패턴 (갱신 + 삽입 또는 병합) 적 수단 SQL 같은 경우에 오류를 귀하의 중간 계층 또는 테이블 디자인과 그것의 좋은 버그를 소리 것 및 기록을 거부 중복 PK를보고합니다. 이 경우에 HOLDLOCK을 배치 예외를 먹고 당신의 반환 한을 감소 외에, 잠재적 결함 데이터를 복용 같습니다.

    INSERT가 먼저 선택 (UPDLOCK)를 추가 할 기억하지 않기 때문에 서버와 경향이 덜 오류에 쉽게 다음 MERGE 또는 UPDATE를 사용하여, 그 말한다면. 당신은 삽입을하고 있다면 또한, / 작은 일괄 업데이트 당신은 트랜잭션이 적합 여부를 결정하기 위해 데이터를 알 필요가있다. 그것은 그 다음 추가 "포위"트랜잭션이 유해 할 것이다 관련이없는 기록의 단지 모음입니다.

  14. ==============================

    14.먼저 삽입 한 다음 업데이 트를 시도 할 경우 경쟁 조건이 정말 중요합니까? 당신은 키 키에 대한 값을 설정하려면 두 개의 스레드가 있다고 가정하자 :

    먼저 삽입 한 다음 업데이 트를 시도 할 경우 경쟁 조건이 정말 중요합니까? 당신은 키 키에 대한 값을 설정하려면 두 개의 스레드가 있다고 가정하자 :

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

    예 경쟁 조건 시나리오

    그러나; 다중 스레드 환경에서 OS 스케줄러는 스레드 실행의 순서를 결정한다 - 우리는이 경쟁 조건이 위의 시나리오에서, 그것을 실행 순서에 결정 OS이었다. 즉 : 그것은 그 "1 스레드"말을 잘못 또는 "스레드 2"시스템 관점에서 "첫번째"이었다.

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

    구현을 위해 : 업데이트 오류 "중복 키"의 삽입 결과에 따른 경우,이 성공으로 처리되어야한다.

    또한, 하나는 물론 데이터베이스에 그 값이 마지막으로 쓴 값과 동일한 생각해서는 안됩니다.

  15. ==============================

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

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

  16. ==============================

    16.나는 솔루션 아래에 시도했다 및 삽입 문에 대한 동시 요청이 발생하면 그것은 나를 위해 작동합니다.

    나는 솔루션 아래에 시도했다 및 삽입 문에 대한 동시 요청이 발생하면 그것은 나를 위해 작동합니다.

    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
  17. ==============================

    17.이 쿼리를 사용할 수 있습니다. 모든 SQL Server 버전에서 작업 할 수 있습니다. 그것은 간단하고 분명하다. 하지만 당신은이 쿼리를 사용하면된다. 병합을 사용할 수없는 경우 사용할 수 있습니다

    이 쿼리를 사용할 수 있습니다. 모든 SQL Server 버전에서 작업 할 수 있습니다. 그것은 간단하고 분명하다. 하지만 당신은이 쿼리를 사용하면된다. 병합을 사용할 수없는 경우 사용할 수 있습니다

        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

    참고 : 응답 네거티브를 설명해주십시오

  18. ==============================

    18.당신은 ADO.NET을 사용하는 경우, 데이터 어댑터는이 문제를 처리합니다.

    당신은 ADO.NET을 사용하는 경우, 데이터 어댑터는이 문제를 처리합니다.

    당신이 그것을 직접 처리하는 경우,이 방법입니다 :

    있는지 확인 키 컬럼에 대한 기본 키 제약 조건이있다.

    당신이 그런 :

    또한, 즉 그것을 다른 방법의 원형을 먼저 삽입을하고, 삽입이 실패 할 경우 업데이트를 할 수 있습니다. 업데이트가 삽입보다 더 자주 수행하기 때문에 일반적으로 첫 번째 방법은 더 나은입니다.

  19. ==============================

    19.존재하는 경우 다른 ... 두 요청 최소 일을 포함 ...을하는 (하나 체크 한 것은 조치를 취할 수 있습니다). 삽입이 필요한 경우 다음의 접근 방식은 레코드 만이 존재 하나, 둘 필요합니다

    존재하는 경우 다른 ... 두 요청 최소 일을 포함 ...을하는 (하나 체크 한 것은 조치를 취할 수 있습니다). 삽입이 필요한 경우 다음의 접근 방식은 레코드 만이 존재 하나, 둘 필요합니다

    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')
  20. ==============================

    20.나는 보통 다른 포스터의 여러 처음 존재하고 올바른 경로가 어떤 일에 대한 검사에 관하여 말한 않습니다. 이 일을 할 때 기억해야 한가지는 SQL 캐시 실행 계획은 하나 개의 경로 또는 기타에 대한 nonoptimal이 될 수 있다는 것입니다. 나는이 작업을 수행하는 가장 좋은 방법은 두 개의 서로 다른 저장 프로 시저를 호출하는 것입니다 생각합니다.

    나는 보통 다른 포스터의 여러 처음 존재하고 올바른 경로가 어떤 일에 대한 검사에 관하여 말한 않습니다. 이 일을 할 때 기억해야 한가지는 SQL 캐시 실행 계획은 하나 개의 경로 또는 기타에 대한 nonoptimal이 될 수 있다는 것입니다. 나는이 작업을 수행하는 가장 좋은 방법은 두 개의 서로 다른 저장 프로 시저를 호출하는 것입니다 생각합니다.

    FirstSP:
If Exists
   Call SecondSP (UpdateProc)
Else
   Call ThirdSP (InsertProc)

    지금, 나는 그렇게 에누리 받아, 매우 자주 내 자신의 조언을 따르지 않습니다.

  21. ==============================

    21.당신이 결과를 얻을 경우,하지 않을 경우, 그것을 생성을 업데이트하는 선택을한다.

    당신이 결과를 얻을 경우,하지 않을 경우, 그것을 생성을 업데이트하는 선택을한다.

    22. from https://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] ID 일치를 기반으로 한 테이블에서 다른 SQL 업데이트  (0) 2020.03.08
[SQL] 오라클에서 여러 행으로 분할 문자열  (0) 2020.03.08
[SQL] 어떻게 SQL 주입에 대한 매개 변수화 된 쿼리 도움을?  (0) 2020.03.07
[SQL] 어떻게 SQL SELECT에서 THEN ...는 IF를 수행합니까?  (0) 2020.03.07
[SQL] SQL, 숫자의 보조 테이블  (0) 2020.03.07
,

티스토리툴바