sql server - values - ¿Qué columnas se pueden usar en la cláusula OUTPUT INTO?

sql output inserted updated

Question

Estoy intentando crear una tabla de asignación para asociar los ID de las filas nuevas en una tabla con aquellas de las que se copiaron. La cláusula OUTPUT INTO parece perfecta para eso, pero no parece comportarse de acuerdo con la documentación.

Mi código:

DECLARE @Missing TABLE (SrcContentID INT PRIMARY KEY )
INSERT INTO @Missing 
    ( SrcContentID ) 
SELECT cshadow.ContentID
    FROM Private.Content AS cshadow
    LEFT JOIN Private.Content AS cglobal ON cshadow.Tag = cglobal.Tag
    WHERE cglobal.ContentID IS NULL 

PRINT ''Adding new content headers''
DECLARE @Inserted TABLE (SrcContentID INT PRIMARY KEY, TgtContentID INT )
INSERT INTO Private.Content 
    ( Tag, Description, ContentDate, DateActivate, DateDeactivate, SortOrder, CreatedOn, IsDeleted, ContentClassCode, ContentGroupID, OrgUnitID ) 
    OUTPUT cglobal.ContentID, INSERTED.ContentID INTO @Inserted (SrcContentID, TgtContentID)
SELECT Tag, Description, ContentDate, DateActivate, DateDeactivate, SortOrder, CreatedOn, IsDeleted, ContentClassCode, ContentGroupID, NULL 
    FROM Private.Content AS cglobal
    INNER JOIN @Missing AS m ON cglobal.ContentID = m.SrcContentID

Resultados en el mensaje de error:

Msg 207, Level 16, State 1, Line 34
Invalid column name ''SrcContentID''.

(la línea 34 es la que tiene la SALIDA HACIA)

La experimentación sugiere que solo las filas que están realmente presentes en el objetivo de INSERT se pueden seleccionar en OUTPUT INTO. Pero esto contradice los documentos en los libros en línea. El artículo sobre la cláusula OUTPUT tiene el ejemplo E que describe un uso similar:

La cláusula OUTPUT INTO devuelve valores de la tabla que se está actualizando (WorkOrder) y también de la tabla Producto. La tabla Producto se utiliza en la cláusula FROM para especificar las filas a actualizar.

¿Alguien ha trabajado con esta característica?

(Mientras tanto, he reescrito mi código para hacer el trabajo usando un bucle de cursor, pero eso es feo y todavía tengo curiosidad)

Answer 1

(MS) Si la tabla que se está modificando también se especifica en la cláusula FROM, cualquier referencia a las columnas en esa tabla debe calificarse con el prefijo INSERTED o BORRADO.

En su ejemplo, no puede usar la tabla cglobal en la SALIDA a menos que esté INSERTED.column_name o DELETED.column_name:

INSERT INTO Private.Content 
    (Tag) 
    OUTPUT cglobal.ContentID, INSERTED.ContentID 
    INTO @Inserted (SrcContentID, TgtContentID)
SELECT Tag
    FROM Private.Content AS cglobal
    INNER JOIN @Missing AS m ON cglobal.ContentID = m.SrcContentID

Lo que funcionó para mí fue una tabla de alias simple, como esta:

INSERT INTO con1 
    (Tag) 
    OUTPUT **con2**.ContentID, INSERTED.ContentID 
    INTO @Inserted (SrcContentID, TgtContentID)
SELECT Tag
    FROM Private.Content con1
    **INNER JOIN Private.Content con2 ON con1.id=con2.id**
    INNER JOIN @Missing AS m ON con1.ContentID = m.SrcContentID

Answer 2

Creo que encontré una solución a este problema, lamentablemente involucra una tabla temporal, pero al menos evitará la creación de un cursor temido :) Lo que tienes que hacer es agregar una columna extra a la tabla en la que estás duplicando registros desde y darle un tipo ''uniqueidentifer''.

luego declara una tabla temporal:

DECLARE @tmptable TABLE (uniqueid uniqueidentifier, original_id int, new_id int)

inserte los datos en su tabla temporal de esta manera:

insert into @tmptable
(uniqueid,original_id,new_id)
select NewId(),id,0 from OriginalTable

Continúe y realice la inserción real en la tabla original:

insert into OriginalTable
(uniqueid)
select uniqueid from @tmptable

Ahora, para agregar los valores de identidad recién creados a su tabla temporal:

update @tmptable
set new_id = o.id
from OriginalTable o inner join @tmptable tmp on tmp.uniqueid = o.uniqueid

Ahora tiene una tabla de búsqueda que contiene la nueva identificación y la identificación original en un registro, para su placer de uso :)

Espero que esto ayude a alguien...

Answer 3

Me encuentro con EXACTAMENTE el mismo problema que tú, siento tu dolor ... Hasta donde he podido averiguar, no hay forma de usar el prefijo from_table_name con una instrucción INSERT. Estoy seguro de que hay una razón técnica viable para esto, y me encantaría saber exactamente qué es.

Ok, lo encontré, aquí hay una publicación en el foro sobre por qué no funciona: los foros de MSDN

Answer 4

Puede hacer esto con un MERGE en Sql Server 2008. Código de ejemplo a continuación:

--drop table A
create table A (a int primary key identity(1, 1))
insert into A default values
insert into A default values

delete from A where a>=3

-- insert two values into A and get the new primary keys
MERGE a USING (SELECT a FROM A) AS B(a)
ON (1 = 0) -- ignore the values, NOT MATCHED will always be true
WHEN NOT MATCHED THEN INSERT DEFAULT VALUES -- always insert here for this example
OUTPUT $action, inserted.*, deleted.*, B.a; -- show the new primary key and source data

El resultado es

INSERT, 3, NULL, 1
INSERT, 4, NULL, 2

es decir, para cada fila, la nueva clave primaria (3, 4) y la anterior (1, 2). Crear una tabla llamada, por ejemplo, #OUTPUT y agregar "INTO #OUTPUT;" al final de la cláusula OUTPUT se guardarían los registros.

Answer 5

Verifiqué que el problema es que solo puedes usar columnas INSERTED . La documentación parece indicar que puede usar from_table_name , pero parece que no puedo hacer que funcione (el identificador de varias partes "m.ContentID" no se pudo enlazar):

TRUNCATE TABLE main

SELECT *
FROM incoming

SELECT *
FROM main

DECLARE @Missing TABLE (ContentID INT PRIMARY KEY)
INSERT INTO @Missing(ContentID) 
SELECT incoming.ContentID
FROM incoming
LEFT JOIN main
    ON main.ContentID = incoming.ContentID
WHERE main.ContentID IS NULL

SELECT *
FROM @Missing

DECLARE @Inserted TABLE (ContentID INT PRIMARY KEY, [Content] varchar(50))
INSERT INTO main(ContentID, [Content]) 
OUTPUT INSERTED.ContentID /* incoming doesn''t work, m doesn''t work */, INSERTED.[Content] INTO @Inserted (ContentID, [Content])
SELECT incoming.ContentID, incoming.[Content] 
FROM incoming
INNER JOIN @Missing AS m
    ON m.ContentID = incoming.ContentID

SELECT *
FROM @Inserted

SELECT *
FROM incoming

SELECT *
FROM main

Aparentemente, el prefijo from_table_name solo está permitido en DELETE o UPDATE (o MERGE en 2008) - No estoy seguro de por qué:

• from_table_name

Es un prefijo de columna que especifica una tabla incluida en la cláusula FROM de una declaración DELETE o UPDATE que se utiliza para especificar las filas para actualizar o eliminar.

Si la tabla que se está modificando también se especifica en la cláusula FROM , cualquier referencia a las columnas en esa tabla debe calificarse con el prefijo INSERTED o DELETED .