tiempo - insertar datos en tablas relacionadas mysql java

¿Cómo puedo INSERTAR datos en dos tablas simultáneamente en SQL Server?

Question

Digamos que la estructura de mi tabla se ve así:

CREATE TABLE [dbo].[table1] (
    [id] [int] IDENTITY(1,1) NOT NULL,
    [data] [varchar](255) NOT NULL,
    CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED ([id] ASC)
)

CREATE TABLE [dbo].[table2] (
    [id] [int] IDENTITY(1,1) NOT NULL,
    [table1_id] [int] NOT NULL,
    [data] [varchar](255) NOT NULL,
    CONSTRAINT [PK_table2] PRIMARY KEY CLUSTERED ([id] ASC)
)

El campo [id] de la primera tabla corresponde al campo [table1_id] del segundo. Lo que me gustaría hacer es insertar datos en ambas tablas en una sola transacción. Ahora ya sé cómo hacer esto haciendo INSERT-SELECT-INSERT, así:

BEGIN TRANSACTION;
DECLARE @id [int];
INSERT INTO [table1] ([data]) VALUES (''row 1'');
SELECT @id = SCOPE_IDENTITY();
INSERT INTO [table2] ([table1_id], [data]) VALUES (@id, ''more of row 1'');
COMMIT TRANSACTION;

Todo está bien y está bien para casos pequeños como esos en los que solo insertas unas cuantas filas. Pero lo que tengo que hacer es insertar un par de cientos de miles de filas, o posiblemente incluso un millón de filas, todas a la vez. Los datos provienen de otra tabla, por lo que si solo lo insertara en una sola tabla, sería fácil, solo tendría que hacer esto:

INSERT INTO [table] ([data])
SELECT [data] FROM [external_table];

¿Pero cómo haría esto y dividiría los datos en [table1] y [table2] , y aún actualizaría [table2] con el [table1_id] apropiado mientras lo hago? ¿Es eso posible?

Answer 1

Otra opción es ejecutar las dos inserciones por separado, dejando la columna FK nula, y luego ejecutando una actualización para rellenarla correctamente.

Si no hay nada natural almacenado dentro de las dos tablas que coinciden de un registro a otro (probable), entonces cree una columna GUID temporal y rellene esto en sus datos e insértelo en ambos campos. Luego puede actualizar con el FK adecuado y anular los GUID.

P.ej:

CREATE TABLE [dbo].[table1] ( 
    [id] [int] IDENTITY(1,1) NOT NULL, 
    [data] [varchar](255) NOT NULL, 
    CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED ([id] ASC),
    JoinGuid UniqueIdentifier NULL
) 

CREATE TABLE [dbo].[table2] ( 
    [id] [int] IDENTITY(1,1) NOT NULL, 
    [table1_id] [int] NULL, 
    [data] [varchar](255) NOT NULL, 
    CONSTRAINT [PK_table2] PRIMARY KEY CLUSTERED ([id] ASC),
    JoinGuid UniqueIdentifier NULL
) 


INSERT INTO Table1....

INSERT INTO Table2....

UPDATE b
SET table1_id = a.id
FROM Table1 a
JOIN Table2 b on a.JoinGuid = b.JoinGuid
WHERE b.table1_id IS NULL

UPDATE Table1 SET JoinGuid = NULL
UPDATE Table2 SET JoinGuid = NULL

Answer 2

Preste atención a SQL Server para que admita la instrucción ''INSERTAR TODO''. Oracle ya lo tiene, se ve así ( libro de cocina SQL ):

insert all
  when loc in (''NEW YORK'', ''BOSTON'') THEN
   into dept_east(deptno, dname, loc) values(deptno, dname, loc)
  when loc in (''CHICAGO'') THEN
   into dept_mid(deptno, dname, loc) values(deptno, dname, loc)
  else
   into dept_west(deptno, dname, loc) values(deptno, dname, loc)
select deptno, dname, loc
  from dept

Answer 3

Prueba esto:

insert into [table] ([data])
output inserted.id, inserted.data into table2
select [data] from [external_table]

ACTUALIZACIÓN: Re:

Denis: esto parece muy parecido a lo que quiero hacer, pero ¿quizás podrías arreglar la siguiente declaración SQL para mí? Básicamente, los [datos] en [tabla1] y los [datos] en [tabla2] representan dos columnas distintas / distintas de [tabla_externa]. La declaración que publicó anteriormente solo funciona cuando desea que las columnas [de datos] sean las mismas.

INSERT INTO [table1] ([data]) 
OUTPUT [inserted].[id], [external_table].[col2] 
INTO [table2] SELECT [col1] 
FROM [external_table] 

Es imposible generar columnas externas en una instrucción de insert , así que creo que podrías hacer algo como esto

merge into [table1] as t
using [external_table] as s
on 1=0 --modify this predicate as necessary
when not matched then insert (data)
values (s.[col1])
output inserted.id, s.[col2] into [table2]
;

Answer 4

Puede escribir un procedimiento almacenado que itere sobre la transacción que ha propuesto. El iterador sería el cursor de la tabla que contiene los datos de origen.

Answer 5

También estaba luchando con este problema y descubrí que la mejor manera es usar un CURSOR .

He probado la solución de Denis con OUTPUT, pero como mencionó, es imposible generar columnas externas en una instrucción de inserción, y MERGE no puede funcionar cuando se insertan varias filas mediante la selección.

Entonces, he usado un CURSOR, para cada fila en la tabla externa, hice un INSERTO, luego uso @@ IDENTIDAD para otro INSERTAR.

DECLARE @OuterID int

DECLARE MY_CURSOR CURSOR 
  LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR 
SELECT  ID FROM   [external_Table]

OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @OuterID

WHILE @@FETCH_STATUS = 0
BEGIN 
INSERT INTO [Table]   (data)
    SELECT data
    FROM     [external_Table] where ID = @OuterID 

    INSERT INTO [second_table] (FK,OuterID)
    VALUES(@OuterID,@@identity)

    FETCH NEXT FROM MY_CURSOR INTO @OuterID
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

Answer 6

BEGIN TRANSACTION;

DECLARE @tblMapping table(sourceid int, destid int)

INSERT INTO [table1] ([data]) 
OUTPUT source.id, new.id
Select [data] from [external_table] source;

INSERT INTO [table2] ([table1_id], [data])
Select map.destid, source.[more data] 
from [external_table] source
    inner join @tblMapping map on source.id=map.sourceid;

COMMIT TRANSACTION;