c# - tablas - insertar registros de una base de datos a otra en sql server

¿Es posible recuperar los ID de PrimayKey después de una copia masiva de SQL?

Question

Estoy usando C # y usando SqlBulkCopy. Aunque tengo un problema. Necesito hacer una inserción masiva en una tabla y luego otra inserción masiva en otra tabla.

Estos 2 tienen una relación PK / FK.

Table A
Field1 -PK auto incrementing (easy to do SqlBulkCopy as straight forward)

Table B
Field1 -PK/FK - This field makes the relationship and is also the PK of this table. It is not auto incrementing and needs to have the same id as to the row in Table A.

Así que estas tablas tienen una relación de uno a uno, pero no estoy seguro de cómo recuperar todas las ID de PK que realizó el inserto masivo desde que las necesito para la Tabla B.

Editar

¿Podría hacer algo como esto?

SELECT * 
FROM Product
WHERE NOT EXISTS (SELECT * FROM ProductReview WHERE Product.ProductId = ProductReview.ProductId AND Product.Qty = NULL AND Product.ProductName != ''Ipad'')

Esto debería encontrar todas las filas que se insertaron con la copia masiva de SQL. No estoy seguro de cómo tomar los resultados de esto y luego hacer una inserción masiva con ellos desde un SP.

El único problema que puedo ver con esto es que si un usuario está haciendo los registros uno por uno y esta declaración se ejecuta al mismo tiempo, podría intentar insertar una fila dos veces en la "Tabla de revisión de productos".

Entonces, digamos que tengo como un usuario que usa el modo manual y otro que lo hace en forma masiva casi al mismo tiempo.

Manera manual. 1. El usuario envía datos 2. Linq to sql El objeto de producto se elabora con los datos y se envía. 3. este objeto ahora contiene el ProductId 4. Se crea otro objeto linq to sql para la tabla de revisión del producto y se inserta (el Id. Del producto del paso 3 se envía a lo largo).

Camino de masas 1. El usuario toma datos de un usuario que comparte los datos. 2. Se capturan todas las filas de productos del usuario que comparte. 3. La inserción de la copia masiva de SQL en las filas del producto sucede. 4. Mi SP selecciona todas las filas que solo existen en la tabla Producto y cumple con algunas otras condiciones 5. La inserción masiva ocurre con esas filas.

Entonces, ¿qué sucede si el paso 3 (modo manual) ocurre al mismo tiempo que el paso 4 (modo masivo)? Creo que intentaría insertar la misma fila dos veces causando una exención de restricción primaria.

Answer 1

Dependiendo de sus necesidades y de la cantidad de control que tenga sobre las tablas, puede considerar el uso de UNIQUEIDENTIFIERs (Guids) en lugar de sus claves primarias de IDENTIDAD. Esto mueve la gestión de claves fuera de la base de datos y en su aplicación. Este enfoque tiene algunas concesiones serias, por lo que es posible que no satisfaga sus necesidades. Pero puede valer la pena considerarlo. Si está seguro de que va a inyectar una gran cantidad de datos en sus tablas a través de inserciones masivas, a menudo es muy útil tener esas claves administradas en su modelo de objetos en lugar de que su aplicación se base en la base de datos para devolverle la información. datos.

También puede adoptar un enfoque híbrido con tablas de preparación tal como se sugirió anteriormente. Obtenga los datos en esas tablas utilizando GUID para las relaciones, y luego, a través de las sentencias de SQL, puede obtener las claves externas enteras en orden y bombear datos a sus tablas de producción.

Answer 2

En ese escenario, usaría SqlBulkCopy para insertar en una tabla de preparación (es decir, una que se parece a los datos que quiero importar, pero no es parte de las tablas transaccionales principales), y luego en la base de datos a un INSERT / SELECT para Mueve los datos a la primera tabla real.

Ahora tengo dos opciones dependiendo de la versión del servidor; Podría hacer una segunda INSERT / SELECT a la segunda tabla real, o podría usar la cláusula INSERT / OUTPUT para hacer la segunda inserción, usando las filas de identidad de la tabla.

Por ejemplo:

     -- dummy schema
     CREATE TABLE TMP (data varchar(max))
     CREATE TABLE [Table1] (id int not null identity(1,1), data varchar(max))
     CREATE TABLE [Table2] (id int not null identity(1,1), id1 int not null, data varchar(max))

     -- imagine this is the SqlBulkCopy
     INSERT TMP VALUES(''abc'')
     INSERT TMP VALUES(''def'')
     INSERT TMP VALUES(''ghi'')

     -- now push into the real tables
     INSERT [Table1]
     OUTPUT INSERTED.id, INSERTED.data INTO [Table2](id1,data)
     SELECT data FROM TMP

Answer 3

Me gustaría:

1. Activar la inserción de identidad en la mesa.

2. Agarra la identificación de la última fila de la mesa

3. Bucle desde (int i = Id; i < datable.rows.count+1; i++)

4. En el bucle, asigne la propiedad Id de su datable a i+1 .

5. Ejecute su inserción masiva de SQL con su identidad de mantener activada.

6. Desactivar la inserción de identidad

Creo que esa es la forma más segura de obtener sus identificadores en una inserción masiva de SQL porque evitará que los identificadores no coincidentes que podrían ser causados ​​por la aplicación se ejecuten en otro hilo.

Answer 4

Mi enfoque es similar al descrito por RiceRiceBaby, excepto que una cosa importante que se debe agregar es que la llamada para recuperar Max (Id) debe ser parte de una transacción, junto con la llamada a SqlBulkCopy.WriteToServer. De lo contrario, alguien más puede insertar durante su transacción y esto haría que su identificación sea incorrecta. Aquí está mi código:

public static void BulkInsert<T>(List<ColumnInfo> columnInfo, List<T> data, string 
destinationTableName, SqlConnection conn = null, string idColumn = "Id")
    {
        NLogger logger = new NLogger();

        var closeConn = false;


        if (conn == null)
        {
            closeConn = true;
            conn = new SqlConnection(_connectionString);
            conn.Open();
        }

        SqlTransaction tran = 
    conn.BeginTransaction(System.Data.IsolationLevel.Serializable);

        try
        {
            var options = SqlBulkCopyOptions.KeepIdentity;
            var sbc = new SqlBulkCopy(conn, options, tran);

            var command = new SqlCommand(
                    $"SELECT Max({idColumn}) from {destinationTableName};", conn, 
           tran);
            var id = command.ExecuteScalar();

            int maxId = 0;

            if (id != null && id != DBNull.Value)
            {
                maxId = Convert.ToInt32(id);
            }

            data.ForEach(d =>
                {
                    maxId++;
                    d.GetType().GetProperty(idColumn).SetValue(d, maxId);
                });

            var dt = ConvertToDataTable(columnInfo, data);

            sbc.DestinationTableName = destinationTableName;

            foreach (System.Data.DataColumn dc in dt.Columns)
            {
                sbc.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
            }

            sbc.WriteToServer(dt);

            tran.Commit();

            if(closeConn)
            {
                conn.Close();
                conn = null;
            }
        }
        catch (Exception ex)
        {
            tran.Rollback();
            logger.Write(LogLevel.Error, $@"An error occurred while performing a bulk 
insert into table {destinationTableName}. The entire
                                           transaction has been rolled back. 

{ex.ToString()}");
            throw ex;
        }
    }

Answer 5

Si su aplicación lo permite, puede agregar otra columna en la que almacena un identificador del inserto masivo (una guía por ejemplo). Usted establecería esta identificación explícitamente.

Luego, después de la inserción masiva, simplemente selecciona las filas que tienen ese identificador.

Answer 6

Tuve el mismo problema en el que tenía que recuperar los identificadores de las filas insertadas con SqlBulkCopy. Mi columna de identificación era una columna de identidad.

Solución:

He insertado más de 500 filas con copia masiva y luego las seleccioné de nuevo con la siguiente consulta:

SELECT TOP InsertedRowCount * 
FROM   MyTable 
ORDER BY ID DESC

Esta consulta devuelve las filas que acabo de insertar con sus identificadores. En mi caso tuve otra columna única. Así que seleccioné esa columna y la identificación. Luego los mapearon con un IDictionary como tal:

 IDictionary<string, int> mymap = new Dictionary<string, int>()
 mymap[Name] = ID

Espero que esto ayude.

Answer 7

Descargo de responsabilidad : soy el propietario del proyecto C # Bulk Operations

La biblioteca supera las limitaciones de SqlBulkCopy y agrega características flexibles como el valor de identidad insertado de salida.

Detrás del código, hace exactamente lo mismo que la respuesta aceptada pero mucho más fácil de usar.

var bulk = new BulkOperation(connection);

// Output Identity
bulk.ColumnMappings.Add("ProductID", ColumnMappingDirectionType.Output);
// ... Column Mappings...

bulk.BulkInsert(dt);