[sql-server] 모범 사례가없는 경우 SQL Server 삽입

Program / 글쓴이

나는이 Competitions보유하고 결과 테이블 팀 구성원의 이름 과 한 손에 순위를.

반면에 나는 유일한 경쟁자 이름 의 테이블 을 유지해야 합니다 .

CREATE TABLE Competitors (cName nvarchar(64) primary key)

이제 첫 번째 테이블에 약 200,000 개의 결과가 있으며 경쟁자 테이블이 비어 있으면 이것을 수행 할 수 있습니다.

INSERT INTO Competitors SELECT DISTINCT Name FROM CompResults

그리고 쿼리는 약 11,000 개의 이름을 삽입하는 데 5 초 정도 걸립니다.

지금까지 이것은 중요한 응용 프로그램이 아니므 로 약 10,000 행으로 새로운 경쟁 결과를받을 때 한 달에 한 번 경쟁사 테이블을 자르는 것을 고려할 수 있습니다 .

그러나 새로운 경쟁 업체와 기존 경쟁 업체에서 새로운 결과를 추가 할 때 가장 좋은 방법은 무엇입니까? 기존 경쟁사 테이블을 자르고 싶지 않습니다.

새로운 경쟁사에 대해서만 INSERT 문을 수행해야하며 존재하는 경우 아무 것도하지 않아도됩니다.

답변

의미 적으로 “아직 존재하지 않는 경쟁 업체 삽입”을 요청합니다.

INSERT Competitors (cName)
SELECT DISTINCT Name
FROM CompResults cr
WHERE
   NOT EXISTS (SELECT * FROM Competitors c
              WHERE cr.Name = c.cName)

답변

또 다른 옵션은 결과 테이블을 기존 경쟁자 테이블과 결합하고 조인과 일치하지 않는 고유 한 레코드를 필터링하여 새 경쟁자를 찾는 것입니다.

INSERT Competitors (cName)
SELECT  DISTINCT cr.Name
FROM    CompResults cr left join
        Competitors c on cr.Name = c.cName
where   c.cName is null

새로운 구문 MERGE 는 또한 작고 우아하며 효율적인 방법을 제공합니다.

MERGE INTO Competitors AS Target
USING (SELECT DISTINCT Name FROM CompResults) AS Source ON Target.Name = Source.Name
WHEN NOT MATCHED THEN
    INSERT (Name) VALUES (Source.Name);

답변

왜 다른 사람이 아직이 말을하지 않았는지 모릅니다.

정상화.

경쟁을 모델로 한 테이블이 있습니까? 경쟁은 경쟁자로 구성되어 있습니까? 하나 이상의 대회에서 다른 선수 목록이 필요합니다 ……

다음 테이블이 있어야합니다 …..

CREATE TABLE Competitor (
    [CompetitorID] INT IDENTITY(1,1) PRIMARY KEY
    , [CompetitorName] NVARCHAR(255)
    )

CREATE TABLE Competition (
    [CompetitionID] INT IDENTITY(1,1) PRIMARY KEY
    , [CompetitionName] NVARCHAR(255)
    )

CREATE TABLE CompetitionCompetitors (
    [CompetitionID] INT
    , [CompetitorID] INT
    , [Score] INT

    , PRIMARY KEY (
        [CompetitionID]
        , [CompetitorID]
        )
    )

다른 테이블을 가리키는 CompetitionCompetitors.CompetitionID 및 CompetitorID에 대한 제한 조건이 있습니다.

이러한 종류의 테이블 구조 (키는 모두 간단한 INTS입니다)-모델에 맞는 좋은 자연 키가없는 것처럼 보이므로 SURROGATE 키가 여기에 적합하다고 생각합니다.

따라서 특정 경쟁에서 다른 경쟁 업체 목록을 얻으려면 다음과 같이 쿼리를 발행하십시오.

DECLARE @CompetitionName VARCHAR(50) SET @CompetitionName = 'London Marathon'

    SELECT
        p.[CompetitorName] AS [CompetitorName]
    FROM
        Competitor AS p
    WHERE
        EXISTS (
            SELECT 1
            FROM
                CompetitionCompetitor AS cc
                JOIN Competition AS c ON c.[ID] = cc.[CompetitionID]
            WHERE
                cc.[CompetitorID] = p.[CompetitorID]
                AND cc.[CompetitionName] = @CompetitionNAme
        )

그리고 당신은 각 경쟁에 대한 점수를 원한다면 경쟁자가 있습니다 :

SELECT
    p.[CompetitorName]
    , c.[CompetitionName]
    , cc.[Score]
FROM
    Competitor AS p
    JOIN CompetitionCompetitor AS cc ON cc.[CompetitorID] = p.[CompetitorID]
    JOIN Competition AS c ON c.[ID] = cc.[CompetitionID]

그리고 새로운 경쟁자와 새로운 경쟁을 할 때 경쟁사 테이블에 어떤 경쟁자가 이미 있는지 확인하면됩니다. 이들이 이미 존재하는 경우 해당 경쟁 업체의 경쟁 업체에 삽입하지 않고 새 경쟁 업체에 대한 삽입을 수행합니다.

그런 다음 새 경쟁을 경쟁에 삽입하고 마지막으로 경쟁 경쟁에 모든 링크를 만듭니다.

답변

테이블을 함께 결합하여에 존재하지 않는 고유 경쟁 업체 목록을 가져와야합니다 Competitors.

고유 한 레코드가 삽입됩니다.

INSERT Competitors (cName)
SELECT DISTINCT Name
FROM CompResults cr LEFT JOIN Competitors c ON cr.Name = c.cName
WHERE c.Name IS NULL

고유 한 이름을 선택할 때까지 기다릴 필요없이이 삽입을 빠르게 수행해야 할 때가 있습니다. 이 경우 고유 이름을 임시 테이블에 삽입 한 다음 해당 임시 테이블을 사용하여 실제 테이블에 삽입 할 수 있습니다. 임시 테이블에 삽입 할 때 모든 처리가 수행되므로 실제 테이블에 영향을 미치지 않기 때문에이 방법이 효과적입니다. 그런 다음 모든 처리가 완료되면 실제 테이블에 빠르게 삽입합니다. 트랜잭션 내부의 실제 테이블에 삽입하는 마지막 부분을 감쌀 수도 있습니다.

답변

정상화에 대해 이야기하는 위의 답변은 훌륭합니다! 그러나 데이터베이스 스키마 나 구조를 그대로 사용할 수없는 나 같은 위치에 있다면 어떻게해야합니까? 예를 들어, DBA는 ‘신’이며 모든 제안 된 개정판은 / dev / null?

그런 점에서 위의 코드 샘플을 제공하는 모든 사용자와 관련 하여이 스택 오버플로 게시로도 이것이 답변 된 것 같습니다 .

INSERT VALUES WHERE NOT EXISTS 에서 코드를 다시 게시하고있어 기본 데이터베이스 테이블을 변경할 수 없으므로 가장 도움이되었습니다.

INSERT INTO #table1 (Id, guidd, TimeAdded, ExtraData)
SELECT Id, guidd, TimeAdded, ExtraData
FROM #table2
WHERE NOT EXISTS (Select Id, guidd From #table1 WHERE #table1.id = #table2.id)
-----------------------------------
MERGE #table1 as [Target]
USING  (select Id, guidd, TimeAdded, ExtraData from #table2) as [Source]
(id, guidd, TimeAdded, ExtraData)
    on [Target].id =[Source].id
WHEN NOT MATCHED THEN
    INSERT (id, guidd, TimeAdded, ExtraData)
    VALUES ([Source].id, [Source].guidd, [Source].TimeAdded, [Source].ExtraData);
------------------------------
INSERT INTO #table1 (id, guidd, TimeAdded, ExtraData)
SELECT id, guidd, TimeAdded, ExtraData from #table2
EXCEPT
SELECT id, guidd, TimeAdded, ExtraData from #table1
------------------------------
INSERT INTO #table1 (id, guidd, TimeAdded, ExtraData)
SELECT #table2.id, #table2.guidd, #table2.TimeAdded, #table2.ExtraData
FROM #table2
LEFT JOIN #table1 on #table1.id = #table2.id
WHERE #table1.id is null

위의 코드는 현재와 다른 필드를 사용하지만 다양한 기술로 일반적인 요지를 얻습니다.

Stack Overflow의 원래 답변에 따라이 코드는 here 에서 복사되었습니다 .

어쨌든 내 요점은 “모범 사례”는 종종 이론뿐만 아니라 할 수있는 것과 할 수없는 것에 귀착되는 것입니다.

  • 인덱스 / 키를 정규화하고 생성 할 수 있다면 좋습니다!
  • 그렇지 않은 경우 나와 같은 해킹을 코딩하는 방법이 있다면 위의 도움이되기를 바랍니다.

행운을 빕니다!

답변

Transact Charlie가 제안한 대로 운영 테이블 을 정규화 하는 것은 좋은 생각이며 시간이 지남에 따라 많은 두통과 문제를 줄일 것입니다. 그러나 외부 시스템과의 통합을 지원하는 인터페이스 테이블 과 분석 같은 것을 지원하는 보고 테이블과 같은 것들이 있습니다. 처리; 그리고 이러한 유형의 테이블은 반드시 정규화 되어서는 안됩니다 . 실제로, 매우 자주 , 훨씬 편리하고 성능이 좋지 않습니다 .

이 경우 운영 테이블에 대한 Transact Charlie의 제안이 좋은 것 같습니다.

그러나 통합 (외부 소스에서 데이터로드)을 위해 CompetitorName에 대한 효율적인 조인을 지원하기 위해 Competitors 테이블의 CompetitorName에 인덱스 (고유 할 필요는 없음)를 추가하고 인터페이스 테이블을 혼합에 넣습니다. CompetitionResults.

CompetitionResults에는 경쟁 결과에 포함 된 모든 데이터가 포함되어야합니다. 이와 같은 인터페이스 테이블의 요점은 Excel 시트 또는 CSV 파일 또는 해당 데이터가있는 형식에서 가능한 한 빠르고 쉽게 잘라 내고 다시로드 할 수 있도록하는 것입니다.

해당 인터페이스 테이블은 정규화 된 운영 테이블 세트의 일부로 간주되지 않아야합니다. 그런 다음 Richard가 제안한대로 CompetitionResults에 참여하여 아직 존재하지 않는 경쟁 업체에 레코드를 삽입하고 그렇지 않은 경쟁 업체에 레코드를 업데이트 할 수 있습니다 (예 : 실제로 전화 번호 또는 이메일 주소와 같은 경쟁 업체에 대한 자세한 정보가있는 경우).

실제로 주목할 것은, 경쟁사 이름은 데이터에서 고유하지 않을 것 입니다. 예를 들어, 20 만 명의 경쟁 업체에서는 2 명 이상의 David Smith가있을 수 있습니다. 따라서 경쟁 업체의 전화 번호 나 이메일 주소 또는 고유 한 정보 등 더 많은 정보를 수집하는 것이 좋습니다.

운영 테이블 경쟁사에는 복합 자연 키에 기여하는 각 데이터 항목마다 하나의 열만 있어야합니다. 예를 들어 기본 이메일 주소에 대해 하나의 열이 있어야합니다. 그러나 인터페이스 테이블 에는 기본 전자 메일 주소에 대한 이전 값 과 새 값에 대한 슬롯이 있어야 기존 값을 사용하여 경쟁 업체에서 레코드를 조회하고 해당 부분을 새 값으로 업데이트 할 수 있습니다.

따라서 CompetitionResults에는 “old”및 “new”필드 (oldEmail, newEmail, oldPhone, newPhone 등)가 있어야합니다. 이렇게하면 CompetitorName, Email 및 Phone의 경쟁사에서 복합 키를 구성 할 수 있습니다.

그런 다음 일부 경쟁 결과가 나오면 Excel 시트 또는 보유한 모든 항목에서 CompetitionResults 테이블을 잘라 내고 다시로드하고 모든 새 경쟁자를 경쟁사 테이블에 삽입하기위한 효율적인 단일 삽입을 실행하고 업데이트 할 단일 효율적인 업데이트를 수행 할 수 있습니다 CompetitionResults에서 기존 경쟁사에 대한 모든 정보. 또한 단일 삽입을 수행하여 CompetitionCompetitors 테이블에 새 행을 삽입 할 수 있습니다. 이러한 작업은 CompetitionResults 테이블을로드 한 후 실행될 수있는 ProcessCompetitionResults 저장 프로 시저에서 수행 할 수 있습니다.

이것은 Oracle Applications, SAP, PeopleSoft 및 기타 엔터프라이즈 소프트웨어 제품군의 세탁 목록을 사용하여 실제 세계에서 반복해서 본 것에 대한 기본적인 설명입니다.

내가 할 줄 마지막 코멘트 내가 SO에 이전했습니다 하나입니다 : 당신이 외래 키를 만들 경우 CompetitionCompetitors에 거기에 그 경쟁자와 행을 추가하기 전에 경쟁자가 경쟁자 테이블에 존재하는 것을 보장가 있다는 것을 확인 외래 키는 계단식 업데이트 및 삭제로 설정되어 있습니다. 이렇게하면 경쟁 업체를 삭제해야 할 경우 경쟁 업체를 삭제할 수 있으며 해당 경쟁 업체와 관련된 모든 행이 자동으로 삭제됩니다. 그렇지 않으면 기본적으로 외래 키를 사용하면 경쟁자를 삭제하기 전에 CompetitionCompetitors에서 모든 관련 행을 삭제해야합니다.

(일부 사람들은 계단식이 아닌 외래 키가 좋은 안전 예방책이라고 생각하지만 내 경험은 단순히 감독의 결과가 아니라 종종 엉덩이로 인해 고통을 겪는다는 사실입니다. 사람들이 실수로 물건을 삭제하는 것을 다루기 때문에 “확실히”대화 상자와 다양한 유형의 정기적 백업 및 중복 데이터 소스와 같은 것이 있기 때문에 실제로 데이터가 모두있는 경쟁자를 삭제하는 것이 훨씬 더 일반적입니다. 예를 들어 실수로 하나를 삭제 한 다음 “아뇨! 그럴 생각은 없었습니다! 이제 경쟁 결과가 없습니다! Aaaahh!” 준비를해야하지만 전자가 훨씬 일반적입니다.전자를 준비하는 가장 쉽고 가장 좋은 방법은 외래 키를 계단식으로 업데이트하고 삭제하는 것입니다.)

답변

좋아, 이것은 7 년 전에 요청되었지만 여기서 가장 좋은 해결책은 새 테이블을 완전히 버리고 사용자 정의 보기로하는 것입니다. 이렇게하면 데이터를 복제하지 않고 고유 한 데이터에 대해 걱정할 필요가 없으며 실제 데이터베이스 구조에 영향을 미치지 않습니다. 이 같은:

CREATE VIEW vw_competitions
  AS
  SELECT
   Id int
   CompetitionName nvarchar(75)
   CompetitionType nvarchar(50)
   OtherField1 int
   OtherField2 nvarchar(64)  --add the fields you want viewed from the Competition table
  FROM Competitions
GO

다른 테이블의 조인, WHERE 절 등과 같은 다른 항목을 여기에 추가 할 수 있습니다. 이제 뷰를 쿼리 할 수 ​​있으므로이 문제에 대한 가장 우아한 해결책 일 것입니다.

SELECT *
FROM vw_competitions

… 그리고 WHERE, IN 또는 EXISTS 절을보기 쿼리에 추가하십시오.