Inserção do SQL Server se não existir melhor prática

Eu tenho uma tabela de resultados de Competitions que mantém os nomes dos membros da equipe e sua sorting em uma mão.

Por outro lado, preciso manter uma tabela de nomes exclusivos de concorrentes :

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

Agora eu tenho uns 200.000 resultados na primeira mesa e quando a mesa dos competidores está vazia eu posso fazer isso:

 INSERT INTO Competitors SELECT DISTINCT Name FROM CompResults 

E a consulta leva apenas cerca de 5 segundos para inserir cerca de 11.000 nomes.

Até agora, essa não é uma aplicação crítica, portanto, posso considerar truncar a tabela Concorrentes uma vez por mês, quando eu receber os novos resultados da competição com cerca de 10.000 linhas.

Mas qual é a melhor prática quando novos resultados são adicionados, com novos concorrentes E? Eu não quero truncar tabela de concorrentes existentes

Eu preciso executar a instrução INSERT apenas para novos concorrentes e não fazer nada se eles existirem.

   

Semanticamente você está perguntando “insira concorrentes onde ainda não existe”:

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

Outra opção é se juntar à sua tabela de resultados com a Tabela de concorrentes existente e encontrar os novos competidores filtrando os registros distintos que não coincidem com a junit:

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

Nova syntax A MERGE também oferece uma maneira compacta, elegante e eficiente de fazer isso:

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

Não sei porque alguém ainda não disse isso;

NORMALIZAR.

Você tem uma mesa que modela competições? As competições são compostas de concorrentes? Você precisa de uma lista distinta de concorrentes em uma ou mais competições ……

Você deve ter as seguintes tabelas …..

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

Com restrições em competidores Competidores.CompetitionID e CompetitorID apontando para as outras mesas.

Com este tipo de estrutura de tabela – suas chaves são todas INTS simples – não parece haver uma boa chave NATURAL que se encaixe no modelo, então acho que uma chave SURROGATE é uma boa opção aqui.

Então, se você tivesse isso, então para obter a lista de concorrentes em uma competição específica, você pode fazer uma consulta como esta:

 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 ) 

E se você quiser a pontuação de cada competição, o competidor está em:

 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] 

E quando você tem uma nova competição com novos concorrentes, você simplesmente verifica quais já existem na tabela Concorrentes. Se eles já existem, então você não insere no Concorrente para esses Concorrentes e insere os novos.

Então você insere o novo Concurso em Competição e, finalmente, você acabou de fazer todos os links em CompetpetCompetitors.

Você precisará juntar as tabelas e obter uma lista de concorrentes únicos que ainda não existem nos Competitors .

Isso irá inserir registros exclusivos.

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

Pode chegar um momento em que esta inserção precisa ser feita rapidamente sem poder esperar pela seleção de nomes exclusivos. Nesse caso, você poderia inserir os nomes exclusivos em uma tabela temporária e, em seguida, usar essa tabela temporária para inserir em sua tabela real. Isso funciona bem porque todo o processamento acontece no momento em que você está inserindo em uma tabela temporária, portanto isso não afeta sua tabela real. Então, quando você tiver todo o processamento concluído, faça uma inserção rápida na tabela real. Eu posso até envolver a última parte, onde você insere na tabela real, dentro de uma transação.

Normalizando suas tabelas operacionais como sugerido por Transact Charlie, é uma boa idéia, e irá salvar muitas dores de cabeça e problemas ao longo do tempo – mas existem coisas como tabelas de interface , que suportam integração com sistemas externos e tabelas de relatórios , que suportam coisas como analíticas em processamento; e esses tipos de tabelas não devem necessariamente ser normalizados – de fato, muitas vezes é muito, muito mais conveniente e eficiente para eles não serem .

Nesse caso, acho que a proposta do Transact Charlie para suas tabelas operacionais é boa.

Mas adicionaria um índice (não necessariamente exclusivo) ao CompetitorName na tabela Concorrentes para oferecer suporte a associações eficientes no CompetitorName para fins de integração (carregamento de dados de fonts externas) e colocaria uma tabela de interface no mix: CompetitionResults.

Os resultados da competição devem conter quaisquer dados contidos nos resultados da competição. O ponto de uma tabela de interface como esta é tornar o mais rápido e fácil possível truncar e recarregar a partir de uma planilha do Excel ou um arquivo CSV, ou qualquer que seja a forma em que você tenha esses dados.

Essa tabela de interface não deve ser considerada parte do conjunto normalizado de tabelas operacionais. Então você pode participar com CompetitionResults como sugerido por Richard, para inserir registros em Concorrentes que ainda não existem e atualizar os que o fazem (por exemplo, se você realmente tem mais informações sobre concorrentes, como seu número de telefone ou endereço de e-mail).

Uma coisa que eu notaria – na realidade, o nome do concorrente, parece-me, é muito improvável que seja único em seus dados . Em 200.000 competidores, você pode muito bem ter 2 ou mais David Smiths, por exemplo. Por isso, recomendo que você colete mais informações de concorrentes, como seu número de telefone ou um endereço de e-mail, ou algo que seja mais provável que seja exclusivo.

Sua tabela operacional, Concorrentes, deve ter apenas uma coluna para cada item de dados que contribui para uma chave natural composta; por exemplo, deve ter uma coluna para um endereço de email principal. Mas a tabela de interface deve ter um slot para valores antigos e novos para um endereço de email principal, para que o valor antigo possa ser usado para procurar o registro em Concorrentes e atualizar essa parte para o novo valor.

Assim, o CompetitionResults deve ter alguns campos “antigos” e “novos” – oldEmail, newEmail, oldPhone, newPhone, etc. Dessa forma, você pode formar uma chave composta, em Concorrentes, de CompetitorName, Email e Telefone.

Então, quando você tiver alguns resultados de competição, poderá truncar e recarregar sua tabela CompetitionResults de sua planilha de excel ou o que tiver e executar uma única inserção eficiente para inserir todos os novos concorrentes na tabela Concorrentes e uma atualização única e eficiente para atualizar todas as informações sobre os concorrentes existentes a partir dos Resultados da Competição. E você pode fazer uma única inserção para inserir novas linhas na tabela CompetitionCompetitors. Essas coisas podem ser feitas em um procedimento armazenado ProcessCompetitionResults, que pode ser executado após o carregamento da tabela CompetitionResults.

Essa é uma espécie de descrição rudimentar do que eu já vi várias vezes no mundo real com o Oracle Applications, SAP, PeopleSoft e uma lista de outras suítes de softwares corporativos.

Um último comentário que faço é um que fiz antes no SO: Se você criar uma chave estrangeira que garanta a existência de um Concorrente na tabela Concorrentes antes de poder adicionar uma linha com esse Concorrente a Competidores, certifique-se de que chave estrangeira está definida para atualizações e exclusões em cascata . Dessa forma, se você precisar excluir um concorrente, poderá fazê-lo e todas as linhas associadas a esse concorrente serão excluídas automaticamente. Caso contrário, por padrão, a chave estrangeira exigirá que você exclua todas as linhas relacionadas dos Concorrentes da Competição antes de permitir que você exclua um Concorrente.

(Algumas pessoas pensam que foreign keys não em cascata são uma boa precaução de segurança, mas minha experiência é que elas são apenas uma dor na bunda que, na maioria das vezes, são simplesmente resultado de um descuido e criam um monte de trabalho. para lidar com DBA’s. Lidando com pessoas acidentalmente apagando coisas é porque você tem coisas como “tem certeza” diálogos e vários tipos de backups regulares e fonts de dados redundantes. É muito mais comum querer excluir um concorrente, cujos dados são todos desarrumada por exemplo, que é para acidentalmente excluir um e depois ir “Oh não! Eu não queria fazer isso! E agora eu não tenho os resultados da competição! Aaaahh!” Este último é certamente bastante comum, então , você precisa estar preparado para isso, mas o primeiro é muito mais comum, então a maneira mais fácil e melhor de se preparar para o primeiro, é apenas fazer com que foreign keys sejam atualizadas e excluídas em cascata.)

As respostas acima que falam sobre normalização são ótimas! Mas e se você se encontrar em uma posição como eu, onde não é permitido tocar no esquema ou na estrutura do database como está? Por exemplo, os DBAs são ‘deuses’ e todas as revisões sugeridas vão para / dev / null?

A esse respeito, sinto que isso foi respondido com essa postagem do Stack Overflow também em relação a todos os usuários acima dando amostras de código.

Estou repostando o código de INSERT VALUES WHERE NOT EXISTS, o que me ajudou mais, pois não posso alterar nenhuma tabela de database subjacente:

 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 

O código acima usa campos diferentes do que você tem, mas você obtém a essência geral com as várias técnicas.

Observe que, de acordo com a resposta original no Stack Overflow, esse código foi copiado daqui .

De qualquer forma, meu ponto de vista é “melhor prática”, muitas vezes se resume ao que você pode e não pode fazer, assim como a teoria.

  • Se você é capaz de normalizar e gerar índices / chaves – ótimo!
  • Se não e você tem o recurso de hacks de código como eu, espero que o acima ajuda.

Boa sorte!

Ok, isso foi perguntado há 7 anos, mas acho que a melhor solução aqui é abrir mão da nova tabela e fazer isso como uma exibição personalizada. Dessa forma, você não está duplicando dados, não há preocupação com dados exclusivos e não afeta a estrutura real do database. Algo assim:

 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 

Outros itens podem ser adicionados aqui, como junções em outras tabelas, cláusulas WHERE, etc. Essa é provavelmente a solução mais elegante para esse problema, pois agora você pode apenas consultar a visualização:

 SELECT * FROM vw_competitions 

… e adicione qualquer cláusula WHERE, IN ou EXISTS à consulta de visualização.