quitar - Insertar SQL Server si no existe la mejor práctica

quitar identity columna sql server

Question

Tengo una tabla de resultados de Competitions que contiene los nombres de los miembros del equipo y su clasificación por un lado.

Por otro lado, necesito mantener una tabla de nombres de competidores únicos :

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

Ahora tengo unos 200,000 resultados en la primera tabla y cuando la tabla de competidores está vacía , puedo realizar esto:

INSERT INTO Competitors SELECT DISTINCT Name FROM CompResults

Y la consulta solo toma unos 5 segundos para insertar aproximadamente 11,000 nombres.

Hasta ahora, esta no es una aplicación crítica, por lo que puedo considerar truncar la tabla de Competidores una vez al mes, cuando reciba los nuevos resultados de la competencia con unas 10.000 filas.

¿Pero cuál es la mejor práctica cuando se agregan nuevos resultados, con nuevos competidores Y existentes? No quiero truncar la tabla de competidores existentes

Necesito realizar la declaración INSERT solo para nuevos competidores y no hacer nada si existen.

Answer 1

¡Las respuestas arriba que hablan de normalización son geniales! Pero, ¿qué ocurre si te encuentras en una posición como la mía en la que no puedes tocar el esquema o la estructura de la base de datos tal como está? Por ejemplo, los DBA son ''dioses'' y todas las revisiones sugeridas van a / dev / null?

En ese sentido, siento que esto también se ha respondido con esta publicación de con respecto a todos los usuarios que aparecen arriba que dan ejemplos de código.

Estoy volviendo a publicar el código de INSERT VALUES WHERE NOT EXISTS que me ayudó más ya que no puedo alterar ninguna tabla de base de datos subyacente:

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

El código anterior usa campos diferentes a los que tienes, pero obtienes la esencia general de las diversas técnicas.

Tenga en cuenta que según la respuesta original en , este código se copió desde aquí .

De todos modos, mi punto es que la "mejor práctica" a menudo se reduce a lo que puede y no puede hacer tan bien como la teoría.

• Si puedes normalizar y generar índices / claves, ¡genial!
• Si no es así y tienes el recurso de piratear el código como yo, con suerte lo anterior ayuda.

¡Buena suerte!

Answer 2

Es buena idea normalizar sus tablas operativas según lo sugerido por Transact Charlie, y ahorrará muchos dolores de cabeza y problemas con el tiempo, pero hay cosas como tablas de interfaz , que admiten la integración con sistemas externos, y tablas de informes , que admiten cosas como análisis tratamiento; y esos tipos de tablas no necesariamente deben ser normalizados , de hecho, muy a menudo es mucho, mucho más conveniente y eficiente para ellos no serlo .

En este caso, creo que la propuesta de Transact Charlie para sus tablas operativas es buena.

Pero agregaría un índice (no necesariamente único) a CompetitorName en la tabla Competidores para admitir combinaciones eficientes en CompetitorName a los fines de la integración (carga de datos de fuentes externas), y pondría una tabla de interfaz en la combinación: CompetitionResults.

Los Resultados de la Competencia deben contener los datos que contengan los resultados de tu competencia. El objetivo de una tabla de interfaz como esta es hacer que sea lo más rápido y fácil posible truncar y volver a cargarlo desde una hoja de Excel o un archivo CSV, o en cualquier forma en que tenga esos datos.

Esa tabla de interfaz no debe considerarse parte del conjunto normalizado de tablas operativas. Luego puede unirse a CompetitionResults como lo sugiere Richard, insertar registros en Competidores que aún no existen y actualizar los que sí lo hacen (por ejemplo, si realmente tiene más información sobre competidores, como su número de teléfono o dirección de correo electrónico).

Una cosa que destacaría, en realidad, el nombre del competidor, me parece, es muy poco probable que sea único en sus datos . En 200,000 competidores, es muy posible que tenga 2 o más David Smiths, por ejemplo. Por lo tanto, le recomiendo que recopile más información de la competencia, como su número de teléfono o dirección de correo electrónico, o algo que probablemente sea único.

Su tabla operativa, Competidores, debería tener solo una columna para cada elemento de datos que contribuya a una clave natural compuesta; por ejemplo, debe tener una columna para una dirección de correo electrónico principal. Pero la tabla de interfaz debe tener una ranura para valores antiguos y nuevos para una dirección de correo electrónico principal, de forma que el valor anterior pueda usarse para buscar el registro en Competidores y actualizar esa parte del mismo al nuevo valor.

Por lo tanto, los resultados de la competencia deben tener algunos campos "antiguos" y "nuevos": correo electrónico antiguo, correo electrónico nuevo, teléfono antiguo, teléfono nuevo, etc. De esta forma puede formar una clave compuesta, en Competidores, desde Nombre del competidor, Correo electrónico y Teléfono.

Luego, cuando tenga algunos resultados de competencia, puede truncar y volver a cargar su tabla CompetenciasResultados desde su hoja de Excel o lo que tenga, y ejecutar un solo inserto eficiente para insertar todos los nuevos competidores en la tabla de Competidores y una actualización única y eficiente para actualizar toda la información sobre los competidores existentes de los Resultados de la Competencia. Y puede hacer un único inserto para insertar nuevas filas en la tabla CompetitionCompetitors. Estas cosas se pueden hacer en un procedimiento almacenado ProcessCompetitionResults, que se puede ejecutar después de cargar la tabla CompetitionResults.

Es una especie de descripción rudimentaria de lo que he visto hacer una y otra vez en el mundo real con Oracle Applications, SAP, PeopleSoft y una lista de otras suites de software empresarial.

Un último comentario que haría es uno que hice anteriormente en SO: si crea una clave externa que asegure que existe un competidor en la tabla de Competidores antes de que pueda agregar una fila con ese competidor en CompetitionCompetitors, asegúrese de que la clave externa está configurada para las actualizaciones y eliminaciones en cascada . De esta forma, si necesita eliminar un competidor, puede hacerlo y todas las filas asociadas con ese competidor se eliminarán automáticamente. De lo contrario, de forma predeterminada, la clave externa requerirá que elimine todas las filas relacionadas de CompetitionCompetitors antes de que le permita eliminar un competidor.

(Algunas personas piensan que las claves externas no en cascada son una buena medida de seguridad, pero mi experiencia es que son solo un maldito dolor en el trasero que a menudo son el resultado de un descuido y crean un montón de trabajo para DBA. Tratar con personas borrando accidentalmente cosas es por qué tienes cosas como "¿estás seguro?", diálogos y varios tipos de copias de seguridad regulares y fuentes de datos redundantes. Es mucho, mucho más común que realmente quieras eliminar a un competidor, cuyos datos son todos estropeado, por ejemplo, que es eliminar accidentalmente uno y luego decir "¡Oh, no! ¡No quise hacer eso! ¡Y ahora no tengo los resultados de la competencia! ¡Aaaahh!" Este último es ciertamente lo suficientemente común, por lo que , es necesario estar preparado para ello, pero el primero es mucho más común, por lo que la forma más fácil y mejor de prepararse para el primero, imo, es simplemente hacer actualizaciones de cascadas de claves externas y eliminarlas).

Answer 3

No sé por qué nadie más no ha dicho esto todavía;

NORMALIZAR.

¿Tienes una mesa que modela competiciones? Las competiciones están compuestas de competidores? Necesita una lista distinta de competidores en una o más competiciones ......

Deberías tener las siguientes tablas .....

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

Con Restricciones en CompetitionCompetitors.CompetitionID y CompetitorID apuntando a las otras tablas.

Con este tipo de estructura de tabla, tus claves son todas INTS simples, no parece haber una buena CLAVE NATURAL que se ajuste al modelo, así que creo que una CLAVE DE SUSTITUCIÓN es adecuada aquí.

Entonces, si tiene esto, entonces, para obtener la lista de competidores en una competencia en particular, puede emitir una 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
        )

Y si desea el puntaje para cada competencia, un competidor se encuentra en:

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]

Y cuando tiene una nueva competencia con nuevos competidores, simplemente verifica cuáles ya existen en la tabla de Competidores. Si ya existen, entonces no se inserta en Competidor para esos Competidores y se inserta para los nuevos.

Luego inserta la nueva Competencia en Competencia y finalmente acaba de hacer todos los enlaces en CompeticiónCompetidores.

Answer 4

Otra opción es unir la tabla de resultados con la tabla de competidores existente y buscar nuevos competidores filtrando los distintos registros que no coinciden en la unión:

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

Nueva sintaxis MERGE también ofrece una forma compacta, elegante y eficiente de hacerlo:

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

Answer 5

Semánticamente estás preguntando "insertar competidores donde aún no existe":

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

Answer 6

Tendrá que unirse a las mesas y obtener una lista de competidores únicos que aún no existen en Competitors .

Esto insertará registros únicos.

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

Puede llegar un momento en que este inserto deba realizarse rápidamente sin poder esperar la selección de nombres únicos. En ese caso, podría insertar los nombres únicos en una tabla temporal, y luego usar esa tabla temporal para insertar en su tabla real. Esto funciona bien porque todo el procesamiento ocurre en el momento en que se está insertando en una tabla temporal, por lo que no afecta su tabla real. Luego, cuando haya terminado todo el proceso, haga una inserción rápida en la tabla real. Incluso podría envolver la última parte, donde insertar en la mesa real, dentro de una transacción.