SQL Server에 저장된 업데이트 프로시저를 삽입합니다.

SQL Server에 저장된 업데이트 프로시저를 삽입합니다.

레코드가 있는 경우 업데이트를 하고, 그렇지 않은 경우 삽입을 하는 스토어드 프로시저를 작성했습니다.다음과 같습니다.

update myTable set Col1=@col1, Col2=@col2 where ID=@ID
if @@rowcount = 0
insert into myTable (Col1, Col2) values (@col1, @col2)

이렇게 쓰는 저의 논리는 업데이트는 where 구를 사용하여 암묵적인 선택을 수행하며, 이것이 0을 반환하면 삽입이 이루어진다는 것입니다.

이 방법으로는 선택을 한 후 반환된 행 수에 따라 업데이트 또는 삽입을 수행하는 방법이 있습니다.업데이트를 하면 2가 선택되기 때문에 비효율적이라고 생각했습니다(첫 번째 명시적 선택 콜과 두 번째 업데이트 위치에 암묵적으로 포함).만약 프로가 삽입을 한다면 효율에 차이가 없을 것입니다.

제 논리가 맞는 건가요?이렇게 해서 삽입과 업데이트를 저장 프로시저에 결합할 수 있습니까?

당신의 추정이 옳습니다.이것이 최적의 방법이며, 이것을 업서트/머지라고 부릅니다.

UPSERT의 중요성 - sqlservercentral.com 에서 :

위에서 설명한 경우 업데이트 때마다 EXIST가 아닌 UPSERT를 사용할 경우 표에서 추가 판독치를 1개씩 삭제합니다.아쉽게도 삽입의 경우 UPSERT 메서드와 IF EXISTES 메서드 모두 테이블에서 동일한 읽기 수를 사용합니다.따라서 존재여부 확인은 추가 I/O를 정당화할 수 있는 매우 타당한 이유가 있을 때만 수행해야 합니다.최적화된 방법은 DB에서 가능한 한 읽기가 적은지 확인하는 것입니다.

가장 좋은 방법은 업데이트를 시도하는 것입니다.업데이트의 영향을 받는 행이 없으면 삽입합니다.대부분의 경우 행은 이미 존재하며 I/O는 하나만 필요합니다.

편집: 이 패턴의 문제점과 안전한 작동 방법에 대해 알아보려면 이 답변과 링크된 블로그 게시물을 확인하십시오.

당신이 사용할 수 있는 안전하고 좋은 패턴을 위해 제 블로그의 게시물을 읽어주세요.많은 고려사항이 있지만, 이 질문에 대해 받아들여지는 대답은 안전하지 않다.

빠른 답변을 얻으려면 다음 패턴을 사용해 보십시오.SQL 2000 이상에서는 정상적으로 동작합니다.SQL 2005에서는 오류 처리를 통해 다른 옵션이 열리고 SQL 2008에서는 MERGE 명령을 사용할 수 있습니다.

begin tran
   update t with (serializable)
   set hitCount = hitCount + 1
   where pk = @id
   if @@rowcount = 0
   begin
      insert t (pk, hitCount)
      values (@id,1)
   end
commit tran

SQL Server 2000/2005에서 사용하는 경우 데이터가 동시 시나리오에서 일관성을 유지할 수 있도록 원본 코드를 트랜잭션에 포함시켜야 합니다.

BEGIN TRANSACTION Upsert
update myTable set Col1=@col1, Col2=@col2 where ID=@ID
if @@rowcount = 0
insert into myTable (Col1, Col2) values (@col1, @col2)
COMMIT TRANSACTION Upsert

이로 인해 추가적인 성능 비용이 발생하지만 데이터 무결성은 보장됩니다.

또한 이미 제안했듯이 MERGE를 사용할 수 있는 경우 사용해야 합니다.

참고로 MERGE는 SQL Server 2008의 신기능 중 하나입니다.

트랜잭션에서 실행해야 할 뿐만 아니라 높은 격리 수준도 요구됩니다.i 팩트 디폴트 분리 레벨은 Read Committed이며 이 코드는 Serializable이 필요합니다.

SET transaction isolation level SERIALIZABLE
BEGIN TRANSACTION Upsert
UPDATE myTable set Col1=@col1, Col2=@col2 where ID=@ID
if @@rowcount = 0
  begin
    INSERT into myTable (ID, Col1, Col2) values (@ID @col1, @col2)
  end
COMMIT TRANSACTION Upsert

@@error 체크와 롤백을 추가하는 것도 좋을지도 모릅니다.

SQL 2008에서 Marge를 하지 않을 경우 다음과 같이 변경해야 합니다.

if @rowcount = 0 및 @@error=0

그렇지 않으면 어떤 이유로 업데이트가 실패하면 실패한 문의 행 수가 0이므로 나중에 삽입을 시도합니다.

UPSERT의 열렬한 팬으로 관리해야 할 코드를 줄였습니다.다른 방법이 있습니다.입력 파라미터 중 하나가 ID입니다.ID가 NULL 또는 0이면 INSERT임을 알 수 있고, 그렇지 않으면 업데이트입니다.응용 프로그램이 ID가 존재하는지 여부를 인식하고 있기 때문에 모든 상황에서 동작하지 않는다고 가정합니다.다만, ID가 존재하면 실행이 반으로 삭감됩니다.

수정된 Dima Malenko 게시물:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 

BEGIN TRANSACTION UPSERT 

UPDATE MYTABLE 
SET    COL1 = @col1, 
       COL2 = @col2 
WHERE  ID = @ID 

IF @@rowcount = 0 
  BEGIN 
      INSERT INTO MYTABLE 
                  (ID, 
                   COL1, 
                   COL2) 
      VALUES      (@ID, 
                   @col1, 
                   @col2) 
  END 

IF @@Error > 0 
  BEGIN 
      INSERT INTO MYERRORTABLE 
                  (ID, 
                   COL1, 
                   COL2) 
      VALUES      (@ID, 
                   @col1, 
                   @col2) 
  END 

COMMIT TRANSACTION UPSERT 

오류를 트랩하여 실패한 삽입 테이블로 레코드를 전송할 수 있습니다.
WSDL을 통해 전송되는 데이터는 모두 가져가며 가능하면 내부적으로 수정하기 때문에 어쩔 수 없었습니다.

논리적으로 타당하다고 생각되지만 특정 프라이머리 키를 전달한 경우 삽입을 방지하기 위해 코드를 추가하는 것을 고려해 보는 것이 좋습니다.

업데이트가 레코드에 영향을 주지 않은 경우 항상 삽입을 수행하는 경우 "UPSERT"를 실행하기 전에 다른 사용자가 레코드를 삭제하면 어떻게 됩니까?업데이트하려는 레코드가 존재하지 않으므로 대신 레코드가 생성됩니다.그건 아마 당신이 찾던 행동이 아닐 거예요.

언급URL : https://stackoverflow.com/questions/13540/insert-update-stored-proc-on-sql-server