Вставка SQL Server, если не существует лучшей практики - программирование

Вставка SQL Server, если не существует лучшей практики

У меня есть таблица результатов Competitions, которая содержит имена членов команды и их рейтинг с одной стороны.

С другой стороны, мне нужно поддерживать таблицу уникальных имен конкурентов:

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

Теперь у меня есть около 200 000 результатов в 1-й таблице и , когда таблица конкурентов пуста. Я могу выполнить это:

INSERT INTO Competitors SELECT DISTINCT Name FROM CompResults

И запрос занимает всего около 5 секунд, чтобы вставить около 11 000 имен.

Пока это не критическое приложение, поэтому я могу рассмотреть обрезать таблицу конкурентов один раз в месяц, когда получаю новые результаты конкурса с примерно 10 000 строк.

Но какова наилучшая практика при добавлении новых результатов с новыми И существующими конкурентами? Я не хочу урезать таблицу существующих конкурентов

Мне нужно выполнить инструкцию INSERT только для новых конкурентов и ничего не делать, если они существуют.

4b9b3361

ОТВЕТЫ

Ответ 1

Семантически вы спрашиваете "вставить конкурентов, где их еще нет":

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

Ответ 2

Другой вариант - оставить в левой части таблицы результатов с существующими конкурентами. Таблицу и найти новых конкурентов путем фильтрации отдельных записей, которые не соответствуют int join:

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);

Ответ 3

Не знаю, почему кто-то еще этого не сказал,

NORMALIZE.

У вас есть таблица, которая моделирует конкурсы? Соревнования состоят из конкурентов? Вам нужен отдельный список участников в одном или нескольких соревнованиях...

У вас должны быть следующие таблицы.....

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 - похоже, нет хорошего NATURAL KEY, который бы соответствовал модели, поэтому я думаю, что SURROGATE KEY здесь хорошо подходит.

Итак, если у вас было это, чтобы получить отдельный список конкурентов в конкретном соревновании, вы можете сделать такой запрос:

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]

И когда у вас новый конкурс с новыми конкурентами, вы просто проверяете, какие из них уже существуют в таблице конкурентов. Если они уже существуют, вы не вставляете в Конкурент для этих Конкурентов и не вставляете их в новые.

Затем вы вставляете новый Конкурс в конкурсе и, наконец, просто делаете все ссылки в CompetCompetitors.

Ответ 4

Вам нужно будет объединить таблицы и получить список уникальных конкурентов, которые еще не существуют в Competitors.

Это приведет к вставке уникальных записей.

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

Возможно, наступит время, когда эту вставку нужно выполнить быстро, не дожидаясь выбора уникальных имен. В этом случае вы можете вставить уникальные имена во временную таблицу, а затем использовать эту временную таблицу для вставки в свою реальную таблицу. Это хорошо работает, потому что вся обработка происходит во время вставки во временную таблицу, поэтому она не влияет на вашу реальную таблицу. Затем, когда вы закончите обработку, вы быстро вставляете ее в реальную таблицу. Я мог бы даже обернуть последнюю часть, где вы вставляете в реальную таблицу внутри транзакции.

Ответ 5

Нормализация ваших рабочих таблиц, предложенная Transact Charlie, является хорошей идеей и с течением времени сэкономит много головных болей и проблем, но есть такие вещи, как таблицы интерфейсов, которые поддерживают интеграцию с внешними системами и таблицы отчетов, которые поддерживают такие вещи, как аналитическая обработка; и эти типы таблиц должны не обязательно быть нормализованы - на самом деле, очень часто для них намного удобнее и удобнее.

В этом случае я думаю, что предложение Transact Charlie для ваших рабочих таблиц является хорошим.

Но я бы добавил индекс CompetitorName в таблицу CompetitorsName (не обязательно уникальный) для поддержки эффективных объединений в CompetitorName для целей интеграции (загрузка данных из внешних источников), и я бы поместил таблицу интерфейса в микс: Результаты конкурса.

Конкурсные результаты должны содержать любые данные, полученные в результате ваших соревнований. Точка таблицы интерфейса, подобная этой, состоит в том, чтобы сделать ее максимально быстрой и легкой, чтобы усечь и перезагрузить ее с листа Excel или CSV файла или любой другой формы, в которой у вас есть эти данные.

Эта таблица интерфейсов не должна рассматриваться как часть нормализованного набора рабочих таблиц. Затем вы можете присоединиться к участникам конкурса, как предложил Ричард, вставить записи в Конкуренты, которые еще не существуют, и обновить те, которые делают (например, если у вас есть больше информации о конкурентах, например, их номер телефона или адрес электронной почты).

Одна вещь, которую я хотел бы отметить - на самом деле, имя конкурента, мне кажется, вряд ли будет уникальным в ваших данных. Например, у 200 000 конкурентов у вас может быть 2 или более Дэвида Смита. Поэтому я бы рекомендовал вам собирать больше информации от конкурентов, таких как их номер телефона или адрес электронной почты, или нечто более вероятное, чтобы быть уникальным.

В вашей рабочей таблице, Конкуренты, должен быть только один столбец для каждого элемента данных, который вносит вклад в составной естественный ключ; например, он должен иметь один столбец для основного адреса электронной почты. Но таблица интерфейса должна иметь слот для старых и новых значений для основного адреса электронной почты, так что старое значение может использоваться для поиска записи в Конкурентах и ​​обновления этой части ее к новому значению.

Таким образом, у конкурентов должно быть несколько "старых" и "новых" полей - oldEmail, newEmail, oldPhone, newPhone и т.д. Таким образом, вы можете сформировать составной ключ в Конкурентах из CompetitorName, Email и Phone.

Затем, когда у вас есть некоторые результаты конкурса, вы можете усекать и перезагружать таблицу CompetitionResults из своего листа excel или любого другого, что у вас есть, и запустить единую эффективную вставку, чтобы вставить всех новых конкурентов в таблицу конкурентов и единый, эффективный обновить, чтобы обновить всю информацию о существующих конкурентах от результатов конкурса. И вы можете сделать одну вставку для вставки новых строк в таблицу CompetitionCompetitors. Это можно сделать в хранимой процедуре ProcessCompetitionResults, которая может быть выполнена после загрузки таблицы CompetitionResults.

Это своего рода рудиментарное описание того, что я видел снова и снова в реальном мире с приложениями Oracle, SAP, PeopleSoft и списком стилей других корпоративных программных пакетов.

Один последний комментарий, который я сделаю, - это тот, который я сделал ранее в SO: Если вы создаете внешний ключ, который гарантирует, что Участник существует в таблице конкурентов, прежде чем вы сможете добавить строку с этим Конкурентом в нее к CompetCompetitors, убедитесь, что внешний ключ настроен на каскадное обновление и удаление. Таким образом, если вам нужно удалить участника, вы можете это сделать, и все строки, связанные с этим конкурентом, автоматически удаляются. В противном случае, по умолчанию, внешний ключ потребует, чтобы вы удалили все связанные строки из CompetCompetitors, прежде чем он позволит вам удалить конкурента.

(Некоторые люди считают, что некаскадные внешние ключи являются хорошей мерой предосторожности, но мой опыт заключается в том, что они просто волнующая боль в заднице, которая чаще всего является просто результатом контроля и создает пучок чтобы работать с DBA. Работа с людьми, случайно удаляющими вещи, - это то, почему у вас есть такие вещи, как "вы уверены", диалоги и различные типы регулярных резервных копий и избыточных источников данных. Это гораздо более распространено, чтобы на самом деле захотеть удалить участника, чей все данные перепутаны, например, чтобы случайно удалить один, а затем пойти "О нет! Я не хотел этого делать! И теперь у меня нет результатов конкурса! Аааах!" Последнее, безусловно, достаточно, так что вам нужно быть готовым к этому, но первый гораздо более распространен, поэтому самый простой и лучший способ подготовки к первому, imo - просто сделать каскадные обновления и удалять внешние ключи.)

Ответ 6

Ответы выше, которые говорят о нормализации, велики! Но что, если вы окажетесь в такой позиции, как я, где вам не разрешено касаться схемы или структуры базы данных в ее нынешнем виде? Например, 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

В приведенном выше коде используются разные поля, кроме того, что у вас есть, но вы получаете общий смысл с помощью различных методов.

Обратите внимание, что в соответствии с исходным ответом на Qaru этот код был скопирован здесь.

В любом случае моя точка зрения - "лучшая практика", часто сводится к тому, что вы можете и не можете делать, а также теории.

  • Если вы в состоянии нормализовать и генерировать индексы/ключи - отлично!
  • Если нет, и у вас есть возможность использовать такие хаки, как я, надеюсь, выше помогает.

Удачи!