T-SQL(SQL Server)에서 "Duplicate Key" 오류를 무시하는 방법

T-SQL(SQL Server)에서 "Duplicate Key" 오류를 무시하는 방법

여러 SQL 문(INSERT, UPDATE 및/또는 DELETE)이 포함된 트랜잭션이 있습니다.실행할 때 중복 오류 문을 무시하고 다음 문으로 계속 진행하려고 합니다.그것을 하는 가장 좋은 방법은 무엇입니까?

인덱스에서 IGNORE_DUP_KEY 옵션을 찾고 있는 것 같습니다.http://msdn.microsoft.com/en-us/library/ms186869.aspx 에 나와 있는 IGNORE_DUP_KEY ON 옵션을 보면 중복 삽입 시도로 인해 오류 대신 경고가 생성됩니다.

귀하의 의견을 SquareCog의 답변으로 확장하면 다음과 같은 작업을 수행할 수 있습니다.

INSERT INTO X VALUES(Y,Z)    WHERE Y  NOT IN (SELECT Y FROM X)
INSERT INTO X2 VALUES(Y2,Z2) WHERE Y2 NOT IN (SELECT Y FROM X2)
INSERT INTO X3 VALUES(Y3,Z3) WHERE Y3 NOT IN (SELECT Y FROM X3)

여기서는 Y 열이 세 표 모두에 있다고 가정합니다.테이블이 Y에 인덱싱되지 않으면 성능이 저하됩니다.

네, Y는 고유한 제약 조건을 가지고 있기 때문에 색인화되어 있으며 최적의 성능을 발휘해야 합니다.

중복 삽입을 시도하지 않도록 SQL을 구성하는 것이 제가 강조하는 조언이지만(필립 켈리의 스니펫이 아마도 필요할 것입니다), 문에 오류가 있다고 해서 반드시 롤백이 발생하는 것은 아니라는 점을 언급하고 싶습니다.

~하지 않는 한XACT_ABORT이라ON연결이 끊어질 정도로 심각한 경우가 아니면 오류가 발생해도 트랜잭션이 자동으로 롤백되지 않습니다.XACT_ABORT기본값:OFF.

예를 들어 다음 sql은 테이블에 세 개의 값을 성공적으로 삽입합니다.

create table x ( y int not null primary key )

begin transaction
insert into x(y)
values(1)
insert into x(y)
values(2)
insert into x(y)
values(2)
insert into x(y)
values(3)
commit

설정하지 않는 한XACT_ABORT클라이언트에서 오류가 발생하여 롤백이 발생하고 있습니다.어떤 끔찍한 이유로 중복 항목을 삽입하지 않을 수 없는 경우에는 클라이언트에서 오류를 트랩하고 무시할 수 있어야 합니다.

"중복 오류 문 무시"로 인해 현재 문을 중단하고 트랜잭션을 중단하지 않고 다음 문으로 계속하려면 BEGIN TRY를 입력하십시오.각 문을 중심으로 시도 종료:

BEGIN TRY
    INSERT ...
END TRY
BEGIN CATCH /*required, but you dont have to do anything */ END CATCH
...

저는 다음 사항에 동의하고 싶습니다.

99%의 데이터가 오류 없이 삽입될 경우 사전에 선택하면 "멍청한" 삽입 및 가끔 오류를 감지하는 것에 비해 성능이 크게 저하됩니다(예: 200줄/초에서 20줄/초).

"기본 키 제약 조건 위반" 오류를 무시한 후 다른 리소스에 의해 병목 현상이 발생했습니다(헤드룸은 "보틀링 리소스에 없는 것"으로 정의됨).

이것이 제가 애초에 이 논의에 착수한 이유입니다.

알겠습니다. 몇 가지 오류 처리를 시도한 후, 저는 제가 안고 있던 문제를 해결하는 방법을 알아냈습니다.

다음은 이 작업을 수행하는 방법의 예입니다(누락된 내용이 있으면 알려주십시오).

SET XACT_ABORT OFF ; -- > really important to set that to OFF
BEGIN
DECLARE @Any_error int
DECLARE @SSQL varchar(4000)
BEGIN TRANSACTION
    INSERT INTO Table1(Value1) VALUES('Value1')
    SELECT @Any_error = @@ERROR
    IF @Any_error<> 0 AND @Any_error<>2627 GOTO ErrorHandler

    INSERT INTO Table1(Value1) VALUES('Value1')
    SELECT @Any_error = @@ERROR
    IF @Any_error<> 0 AND @Any_error<>2627 GOTO ErrorHandler

    INSERT INTO Table1(Value1) VALUES('Value2')
    SELECT @Any_error = @@ERROR
    IF @Any_error<> 0 AND @Any_error<>2627 GOTO ErrorHandler

    ErrorHandler: 
       IF @Any_error = 0 OR @Any_error=2627
       BEGIN 
           PRINT @ssql 
           COMMIT TRAN
       END
       ELSE 
       BEGIN 
           PRINT @ssql 
           ROLLBACK TRAN 
       END
END

상기 트랜잭션의 결과로, 표 1은 다음과 같은 값 Value1, Value2를 갖게 됩니다.

참고로 2627은 Duplicate Key의 에러 코드입니다.

빠른 답변과 도움이 되는 제안에 모두 감사드립니다.

사용하다IGNORE_DUP_KEY = OFF기본 키 정의 중에 중복 항목을 무시합니다.예를들면

create table X( col1.....)

CONSTRAINT [pk_X] PRIMARY KEY CLUSTERED 
(
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 70) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO KeyedTable(KeyField, Otherfield)
SELECT n.* FROM 
    (SELECT 'PossibleDupeLiteral' AS KeyField, 'OtherfieldValue' AS Otherfield
     UNION ALL
     SELECT 'PossibleDupeLiteral', 'OtherfieldValue2'
    )
LEFT JOIN KeyedTable k
    ON k.KeyField=n.KeyField
WHERE k.KeyField IS NULL

이것은 서버에 동일한 데이터(중복 키를 확인하는 것과 동일한 빠른 방법)를 찾고 발견되면 아무것도 삽입하지 말 것을 지시합니다.

저도 IGNORE_DUP_KEY 솔루션을 좋아하지만, 문제를 해결하기 위해 오류에 의존하는 사람은 서버가 자신의 중복 키 오류를 묵묵히 무시할 때 혼란스러울 것입니다.

필립 켈리의 솔루션보다 제가 이것을 선택한 이유는 여러 행의 데이터를 제공할 수 있고 누락된 데이터만 실제로 사용할 수 있기 때문입니다.

저도 같은 일을 하려고 여기에 왔습니다. 소스 데이터에 중복이 있다는 것을 알고 있었지만 대상 데이터를 업데이트하고 중복을 추가하지 않기를 원했습니다.

여기서 MERGE는 다른 것들을 업데이트하거나 삭제할 수 있고 누락된 것들을 삽입할 수 있기 때문에 매우 효과적이라고 생각합니다.

저는 결국 이것을 했고 그것은 잘 작동했습니다.SSIS를 사용하여 Excel 파일을 루프하여 "RAW" SQL 테이블에 모두 로드합니다.그런 다음 MERGE를 실행하여 "raw" 테이블을 프로덕션 테이블과 병합합니다.그런 다음 "raw" 테이블을 잘라내고 다음 Excel 파일로 이동합니다.

SQL Server 2000의 경우:

     INSERT INTO t1 (ID, NAME)
      SELECT valueid, valuename
      WHERE  NOT EXISTS
               (SELECT 0
                FROM   t1 as t2
                WHERE  t2.ID = valueid AND t2.name = valuename)

음, 당신은 이것을 온도 테이블로 해결할 수 있습니다.

DECLARE @RoleToAdds TABLE
([RoleID] int, [PageID] int)

INSERT INTO @RoleToAdds ([RoleID], [PageID])
    VALUES
    (1, 2),
    (1, 3),
    (1, 4),
    (2, 5)

INSERT INTO [dbo].[RolePages] ([RoleID], [PageID])
    SELECT rta.[RoleID], rta.[PageID] FROM @RoleToAdds rta WHERE NOT EXISTS 
        (SELECT * FROM [RolePages] rp WHERE rp.PageID = rta.PageID AND rp.RoleID = rta.RoleID)

이것은 대량의 데이터에는 효과가 없을 수도 있지만, 몇 개의 행에는 효과가 있을 것입니다!

키는 고유해야 합니다.그러지마세요.필요에 따라 재설계합니다.

(삽입하고 삭제하려고 하면 삽입이 실패합니다.)먼저 삭제를 수행합니다.두 문 중 하나에 오류가 있을 경우 롤백합니다.

언급URL : https://stackoverflow.com/questions/1139050/how-to-ignore-duplicate-key-error-in-t-sql-sql-server