SQL Server에서의 INSERT 또는 업데이트 솔루션
를 이이 assume assume assume assume assume assume 。MyTable(KEY, datafield1, datafield2...).
기존 레코드를 업데이트하거나 새 레코드가 없는 경우 새 레코드를 삽입하는 경우가 많습니다.
기본적으로:
IF (key exists)
run update command
ELSE
run insert command
이 글을 쓰는 가장 좋은 방법은 무엇일까요?
거래를 잊지 마세요.성능은 좋지만 간단한(존재하는 경우) 접근 방식은 매우 위험합니다.
여러 스레드가 삽입 또는 업데이트를 시도하면 기본 키를 쉽게 위반할 수 있습니다.
@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 Crowford's는 SQL 2005 이하에서 사용하는 것이 좋습니다.단, 영업권을 부여하고 있다면 최초 구매자에게 맡겨야 합니다.유일한 문제는 삽입의 경우 여전히 두 번의 IO 작업이 필요하다는 것입니다.
Sql2008은 MS Sql2008을 합니다.mergeSQL: 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, ... )
현재는 입출력 조작이 1회밖에 되지 않지만, 코드가 엉망입니다.
UPSERT 실행:
업데이트 MyTable SET 필드A=@FieldA WHERE Key=@Key IF @@ROWCOUNT = 0MyTable(FieldA) 값에 삽입(@FieldA)
http://en.wikipedia.org/wiki/Upsert
많은 이 쓰라고 예요.MERGE하지만 경고합니다.기본적으로는 여러 개의 문장과 마찬가지로 동시성 및 인종 조건으로부터 사용자를 보호하지 않으며 다음과 같은 다른 위험이 발생합니다.
이러한 「심플러」구문을 사용할 수 있어도, 다음의 어프로치를 추천합니다(간단하게 하기 위해서 에러 처리를 생략).
BEGIN TRANSACTION;
UPDATE dbo.table WITH (UPDLOCK, SERIALIZABLE)
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
BEGIN
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 행의 변수에 적절한 값(및 유형)을 설정합니다.
건배.
그것은 사용 패턴에 따라 다릅니다.세세한 부분까지 망각하지 않고 사용법을 크게 봐야 한다.예를 들어 레코드 작성 후 사용 패턴이 99% 업데이트인 경우 'UPSERT'가 가장 좋은 솔루션입니다.
첫 번째 삽입(히트) 후에는 if 또는 but 없이 모두 단일 스테이트먼트의 갱신이 됩니다.인서트의 '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
하시면 됩니다.MERGE스테이트먼트. 이 스테이트먼트는 존재하지 않는 경우 데이터를 삽입하거나, 존재하지 않는 경우 데이터를 갱신하기 위해 사용됩니다.
MERGE INTO Employee AS e
using EmployeeUpdate AS eu
ON e.EmployeeID = eu.EmployeeID`
UPDATE if-no-rows-updated 후 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를 사용하는 것이 좋습니다.
MS SQL Server 2008은 MERGE 스테이트먼트를 도입하고 있습니다.MERGE 스테이트먼트는 SQL:2003 표준의 일부라고 생각합니다.많은 사람들이 한 줄의 사례를 처리하는 것이 큰 문제가 아니지만 대규모 데이터셋을 처리할 때는 커서가 필요하며 그에 따른 모든 성능 문제가 수반됩니다.MERGE 스테이트먼트는 대규모 데이터셋을 취급할 때 추가되는 것을 매우 환영합니다.
모든 사용자가 직접 sprocs를 실행하는 겁에 질려 HOLDLOCK으로 넘어가기 전에:-) 새로운 PK-의 고유성을 설계(아이덴티티 키, Oracle 시퀀스 생성기, 외부 ID의 고유 인덱스, 인덱스로 커버되는 쿼리)로 보증해야 합니다.그것이 이 문제의 핵심이다.이 기능이 없으면 우주의 HOLDLOCK은 당신을 구할 수 없습니다.이 기능이 있다면 첫 번째 선택에서 UPDLOCK 이상의 기능은 필요하지 않습니다(또는 업데이트를 먼저 사용).
일반적으로 Sproc는 매우 제어된 조건에서 신뢰할 수 있는 발신자(미드티어)의 가정 하에 실행됩니다.즉, 단순한 업서트 패턴(update+insert 또는 merge)이 미드레인지 또는 테이블 설계에 버그가 있음을 의미하는 중복 PK를 발견한 경우 SQL이 장애를 외치고 레코드를 거부하는 것이 좋습니다.이 경우 HOLDLOCK을 배치하면 성능 저하뿐만 아니라 식사 예외 및 결함 가능성이 있는 데이터 수집을 의미합니다.
단, MERGE 또는 UPDATE를 사용하면 서버에서 더 쉽고 처음에 선택하기 위해 (UPDLOCK)를 추가할 필요가 없기 때문에 오류가 발생하기 쉽습니다.또한 작은 배치로 삽입/업데이트를 수행하는 경우 트랜잭션이 적절한지 여부를 판단하기 위해 데이터를 알아야 합니다.관련 없는 레코드 모음일 뿐이므로 추가적인 "봉투" 트랜잭션은 해가 될 수 있습니다.
먼저 업데이트를 시도한 후 삽입을 시도하면 레이스 조건이 정말 중요합니까?키 키의 값을 설정하는 스레드가 2개 있다고 합니다.
스레드 1: 값 = 1
스레드 2: 값 = 2
레이스 조건 시나리오의 예
- 키가 정의되지 않았습니다.
- 스레드 1에서 업데이트 실패.
- 스레드 2에서 업데이트 실패.
- 스레드 1 또는 스레드2 중 하나가 삽입에 성공합니다.예: 나사산 1
다른 스레드는 삽입(복제 키 오류 발생)으로 실패합니다(스레드 2).
- 결과: 삽입할 두 트레드 중 첫 번째 트레드가 값을 결정합니다.
- 원하는 결과:데이터 쓰기(업데이트 또는 삽입)의 마지막 2개의 스레드가 값을 결정합니다.
단, 멀티스레드 환경에서는 OS 스케줄러가 스레드 실행 순서를 결정합니다.이러한 레이스 조건에서는 OS가 실행 순서를 결정합니다.ie: "스레드 1" 또는 "스레드 2"를 시스템 관점에서 "첫 번째"라고 말하는 것은 잘못된 것입니다.
스레드 1과 스레드 2의 실행 시간이 매우 가까울 경우 레이스 조건의 결과는 중요하지 않습니다.유일한 요건은 스레드 중 하나가 결과 값을 정의해야 한다는 것입니다.
구현의 경우:업데이트 후 삽입하면 "복제 키" 오류가 발생할 경우 성공으로 간주해야 합니다.
또한 데이터베이스의 값이 마지막으로 기록한 값과 동일하다고 가정해서는 안 됩니다.
저는 아래 솔루션을 시도했고, 동시에 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 에디션에서 작동합니다.간단명료합니다.단, 2개의 쿼리를 사용해야 합니다.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
메모: 부정적인 답변에 대해 설명하십시오.
행을 "/" SQL Server를 이 가장 합니다.REPEATABLE READ"CHANGE: " "CHANGE: "CHANGE:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION
IF (EXISTS (SELECT * FROM myTable WHERE key=@key)
UPDATE myTable SET ...
WHERE key=@key
ELSE
INSERT INTO myTable (key, ...)
VALUES (@key, ...)
COMMIT TRANSACTION
이 분리 수준에서는 이후 반복 가능한 읽기 트랜잭션이 동일한 행에 액세스하는 것을 방지/차단합니다(WHERE key=@key현재 실행 중인 트랜잭션이 열려 있습니다.한편, 다른 행의 조작은 차단되지 않습니다(WHERE key=@key2를 참조해 주세요.
SQL Server 2008에서는 MERGE 문을 사용할 수 있습니다.
다음을 사용할 수 있습니다.
INSERT INTO tableName (...) VALUES (...)
ON DUPLICATE KEY
UPDATE ...
이를 사용하여 특정 키에 대한 엔트리가 이미 있는 경우 해당 키는 업데이트되고 그렇지 않은 경우 삽입됩니다.
ADO를 사용하면.NET, DataAdapter가 이를 처리합니다.
직접 처리하려면 다음과 같이 하십시오.
키 열에 기본 키 제약이 있는지 확인하십시오.
그러면 당신은:
- 업데이트 실행
- 키가 있는 레코드가 이미 존재하기 때문에 업데이트가 실패하면 삽입을 수행합니다.업데이트가 실패하지 않으면 종료됩니다.
반대로도 할 수 있습니다.즉, 먼저 삽입하고 삽입에 실패 시 업데이트를 할 수 있습니다.일반적으로 업데이트는 삽입보다 더 자주 수행되기 때문에 첫 번째 방법이 더 좋습니다.
존재하는 경우 실행 중...그렇지 않으면 최소 두 가지 요청(확인할 요청과 조치를 취할 요청)을 수행해야 합니다.다음 접근방식에서는 레코드가 존재하는 경우 1개, 삽입이 필요한 경우 2개만 필요합니다.
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에 의해 캐시된 실행 계획이 한 경로 또는 다른 경로에 대해 최적화되지 않을 수 있다는 것입니다.이렇게 하는 가장 좋은 방법은 두 가지 저장 프로시저를 호출하는 것이라고 생각합니다.
First SP: 존재하는 경우Call Second SP(Update Proc)또 다른서드SP(Insert Proc)에 문의하다
저는 제 조언을 잘 따르지 않기 때문에, 조금 참고 들어주세요.
결과를 얻으면 선택하고, 업데이트하고, 그렇지 않으면 생성합니다.
언급URL : https://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server
'programing' 카테고리의 다른 글
| 임시 테이블이 있는지 확인하고 임시 테이블을 만들기 전에 해당 테이블이 있으면 삭제하십시오. (0) | 2023.04.07 |
|---|---|
| LIKE 절에서 대괄호를 이스케이프하려면 어떻게 해야 합니까? (0) | 2023.04.07 |
| .bak 파일을 SQL 서버의 데이터베이스로 가져옵니다. (0) | 2023.04.07 |
| CTE와 SubQuery의 차이점 (0) | 2023.04.07 |
| SQL Server에서 JOIN을 사용하여 테이블을 업데이트하시겠습니까? (0) | 2023.04.07 |