Artículo
03/30/2012

Ejemplos de INSERT (Transact-SQL)

En este tema se proporcionan ejemplos de cómo usar la instrucción Transact-SQL INSERT en SQL Server 2008 R2. Los ejemplos de INSERT se agrupan en las siguientes categorías.

Categoría	Elementos de sintaxis destacados
Sintaxis básica	INSERT • constructor con valores de tabla
Tratar los valores de columna	IDENTITY • NEWID • valores predeterminados • tipos definidos por el usuario
Insertar datos de otras tablas	INSERT…SELECT • INSERT…EXECUTE • expresión de tabla común WITH • TOP
Especificar objetos de destino que no sean tablas estándar	Vistas • variables de tabla
Insertar filas en una tabla remota	Servidor vinculado • función de conjunto de filas OPENQUERY • función de conjunto de filas OPENDATASOURCE
Importar datos de forma masiva desde tablas o archivos de datos	INSERT…SELECT • función OPENROWSET
Invalidar el comportamiento predeterminado del optimizador de consultas mediante sugerencias	Sugerencias de tabla
Capturar los resultados de la instrucción INSERT	Cláusula OUTPUT

Sintaxis básica

Los ejemplos de esta sección demuestran la funcionalidad básica de la instrucción INSERT usando la sintaxis mínima requerida.

A. Insertar una sola fila de datos

En el siguiente ejemplo se inserta una fila en la tabla Production.UnitMeasure. Las columnas de esta tabla son UnitMeasureCode, Name y ModifiedDate. Dado que los valores para todas las columnas se suministran e incluyen en el mismo orden que las columnas de la tabla, no es necesario especificar los nombres de columna en la lista de columnas.

    USE AdventureWorks2008R2;
    GO
    INSERT INTO Production.UnitMeasure
    VALUES (N'FT', N'Feet', '20080414');
    GO

B. Insertar varias filas de datos

En el siguiente ejemplo se usa el constructor de valores de tabla para insertar tres filas en la tabla Production.UnitMeasure en una instrucción INSERT. Dado que los valores para todas las columnas se suministran e incluyen en el mismo orden que las columnas de la tabla, no es necesario especificar los nombres de columna en la lista de columnas.

    USE AdventureWorks2008R2;
    GO
    INSERT INTO Production.UnitMeasure
    VALUES (N'FT2', N'Square Feet ', '20080923'), (N'Y', N'Yards', '20080923'), (N'Y3', N'Cubic Yards', '20080923');
    GO

C. Insertar datos que no están en el mismo orden que las columnas de la tabla

En el siguiente ejemplo se usa una lista de columnas para especificar de forma explícita los valores insertados en cada columna. El orden de las columnas de la tabla Production.UnitMeasure es UnitMeasureCode, Name, ModifiedDate; no obstante, las columnas no se incluyen en dicho orden en column_list..

    USE AdventureWorks2008R2;
    GO
    INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode,
        ModifiedDate)
    VALUES (N'Square Yards', N'Y2', GETDATE());
    GO

Tratar los valores de columna

En los ejemplos de esta sección se muestran métodos para insertar valores en columnas que se definen con propiedad IDENTITY, valor DEFAULT o con tipos de datos como uniqueidentifer o columnas de un tipo definido por el usuario.

A. Insertar datos en una tabla con columnas que tienen valores predeterminados

El ejemplo siguiente muestra cómo insertar filas en una tabla con columnas que generan automáticamente un valor o tienen un valor predeterminado. Column_1 es una columna calculada que genera automáticamente un valor concatenando una cadena con el valor insertado en column_2. Column_2 se define con una restricción predeterminada. Si no se especifica un valor para esta columna, se usa el predeterminado. Column_3 se define con el tipo de datos rowversion, que genera automáticamente un número binario único que se incrementa. Column_4 no genera automáticamente un valor. Cuando no se especifica un valor para esta columna, se inserta NULL. La instrucción INSERT inserta filas que contienen valores para algunas de las columnas, pero no para todas. En la última instrucción INSERT, no se especifica ninguna columna y solamente se insertan los valores predeterminados con la cláusula DEFAULT VALUES.

    USE AdventureWorks2008R2;
    GO
    IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
        DROP TABLE dbo.T1;
    GO
    CREATE TABLE dbo.T1 
    (
        column_1 AS 'Computed column ' + column_2, 
        column_2 varchar(30) 
            CONSTRAINT default_name DEFAULT ('my column default'),
        column_3 rowversion,
        column_4 varchar(40) NULL
    );
    GO
    INSERT INTO dbo.T1 (column_4) 
        VALUES ('Explicit value');
    INSERT INTO dbo.T1 (column_2, column_4) 
        VALUES ('Explicit value', 'Explicit value');
    INSERT INTO dbo.T1 (column_2) 
        VALUES ('Explicit value');
    INSERT INTO T1 DEFAULT VALUES; 
    GO
    SELECT column_1, column_2, column_3, column_4
    FROM dbo.T1;
    GO

B. Insertar datos en una tabla con una columna de identidad

En el siguiente ejemplo se muestran los distintos métodos para insertar datos en una columna de identidad. Las dos primeras instrucciones INSERT permiten generar valores de identidad para las filas nuevas. La tercera instrucción INSERT invalida la propiedad IDENTITY de la columna con la instrucción SET IDENTITY_INSERT e inserta un valor explícito en la columna de identidad.

    USE AdventureWorks2008R2;
    GO
    IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
        DROP TABLE dbo.T1;
    GO
    CREATE TABLE dbo.T1 ( column_1 int IDENTITY, column_2 VARCHAR(30));
    GO
    INSERT T1 VALUES ('Row #1');
    INSERT T1 (column_2) VALUES ('Row #2');
    GO
    SET IDENTITY_INSERT T1 ON;
    GO
    INSERT INTO T1 (column_1,column_2) 
        VALUES (-99, 'Explicit identity value');
    GO
    SELECT column_1, column_2
    FROM T1;
    GO

C. Insertar datos en una columna uniqueidentifier mediante NEWID()

En el siguiente ejemplo se usa la función NEWID() para obtener un GUID para column_2. A diferencia de las columnas de identidad, Motor de base de datos no genera automáticamente valores de columnas con el tipo de datos uniqueidentifier, según se muestra en la segunda instrucción INSERT.

    USE AdventureWorks2008R2;
    GO
    IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
        DROP TABLE dbo.T1;
    GO
    CREATE TABLE dbo.T1 
    (
        column_1 int IDENTITY, 
        column_2 uniqueidentifier,
    );
    GO
    INSERT INTO dbo.T1 (column_2) 
        VALUES (NEWID());
    INSERT INTO T1 DEFAULT VALUES; 
    GO
    SELECT column_1, column_2
    FROM dbo.T1;
    GO

D. Insertar datos en columnas de tipo definido por el usuario

Las siguientes instrucciones de Transact-SQL insertan tres filas en la columna PointValue de la tabla Points. Esta columna usa un tipo definido por el usuario CLR (UDT). El tipo de datos Point está compuesto por valores enteros X e Y que se exponen como propiedades del UDT. Debe usar las funciones CAST o CONVERT para convertir los valores X e Y separados por comas al tipo Point. Las dos primeras instrucciones utilizan la función CONVERT para convertir un valor de cadena al tipo Point y la tercera usa la función CAST. Para obtener más información, vea Manipular datos UDT.

INSERT INTO dbo.Points (PointValue) VALUES (CONVERT(Point, '3,4'));
INSERT INTO dbo.Points (PointValue) VALUES (CONVERT(Point, '1,5'));
INSERT INTO dbo.Points (PointValue) VALUES (CAST ('1,99' AS Point));

Insertar datos de otras tablas

Los ejemplos de esta sección demuestran métodos para insertar filas de una tabla en otra.

A. Usar las opciones SELECT y EXECUTE para insertar datos de otras tablas

En el siguiente ejemplo se muestra cómo insertar datos de una tabla en otra mediante INSERT…SELECT o INSERT…EXECUTE. Cada uno se basa en una instrucción SELECT con varias tablas que contiene una expresión y un valor literal en la lista de columnas.

La primera instrucción INSERT usa una instrucción SELECT para derivar los datos de las tablas de origen (Employee, SalesPerson y Person) y almacenar el conjunto de resultados en la tabla EmployeeSales . La segunda instrucción INSERT usa la cláusula EXECUTE para llamar a un procedimiento almacenado que contiene la instrucción SELECT y la tercera instrucción INSERT usa la cláusula EXECUTE para hacer referencia a la instrucción SELECT como una cadena literal.

    USE AdventureWorks2008R2;
    GO
    IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
        DROP TABLE dbo.EmployeeSales;
    GO
    IF OBJECT_ID ('dbo.uspGetEmployeeSales', 'P') IS NOT NULL
        DROP PROCEDURE uspGetEmployeeSales;
    GO
    CREATE TABLE dbo.EmployeeSales
    ( DataSource   varchar(20) NOT NULL,
      BusinessEntityID   varchar(11) NOT NULL,
      LastName     varchar(40) NOT NULL,
      SalesDollars money NOT NULL
    );
    GO
    CREATE PROCEDURE dbo.uspGetEmployeeSales 
    AS 
        SET NOCOUNT ON;
        SELECT 'PROCEDURE', sp.BusinessEntityID, c.LastName, 
            sp.SalesYTD 
        FROM Sales.SalesPerson AS sp  
        INNER JOIN Person.Person AS c
            ON sp.BusinessEntityID = c.BusinessEntityID
        WHERE sp.BusinessEntityID LIKE '2%'
        ORDER BY sp.BusinessEntityID, c.LastName;
    GO
    --INSERT...SELECT example
    INSERT INTO dbo.EmployeeSales
        SELECT 'SELECT', sp.BusinessEntityID, c.LastName, sp.SalesYTD 
        FROM Sales.SalesPerson AS sp
        INNER JOIN Person.Person AS c
            ON sp.BusinessEntityID = c.BusinessEntityID
        WHERE sp.BusinessEntityID LIKE '2%'
        ORDER BY sp.BusinessEntityID, c.LastName;
    GO
    --INSERT...EXECUTE procedure example
    INSERT INTO dbo.EmployeeSales 
    EXECUTE dbo.uspGetEmployeeSales;
    GO
    --INSERT...EXECUTE('string') example
    INSERT INTO dbo.EmployeeSales 
    EXECUTE 
    ('
    SELECT ''EXEC STRING'', sp.BusinessEntityID, c.LastName, 
        sp.SalesYTD 
        FROM Sales.SalesPerson AS sp 
        INNER JOIN Person.Person AS c
            ON sp.BusinessEntityID = c.BusinessEntityID
        WHERE sp.BusinessEntityID LIKE ''2%''
        ORDER BY sp.BusinessEntityID, c.LastName
    ');
    GO
    --Show results.
    SELECT DataSource,BusinessEntityID,LastName,SalesDollars
    FROM dbo.EmployeeSales;
    GO

B. Usar la expresión de tabla común WITH para definir los datos insertados

En el siguiente ejemplo se crea la tabla NewEmployee. Una expresión de tabla común (EmployeeTemp) define las filas de una o varias tablas que se van a insertar en la tabla NewEmployee. La instrucción INSERT hace referencia a las columnas de la expresión de tabla común.

    USE AdventureWorks2008R2;
    GO
    IF OBJECT_ID (N'HumanResources.NewEmployee', N'U') IS NOT NULL
        DROP TABLE HumanResources.NewEmployee;
    GO
    CREATE TABLE HumanResources.NewEmployee
    (
        EmployeeID int NOT NULL,
        LastName nvarchar(50) NOT NULL,
        FirstName nvarchar(50) NOT NULL,
        PhoneNumber Phone NULL,
        AddressLine1 nvarchar(60) NOT NULL,
        City nvarchar(30) NOT NULL,
        State nchar(3) NOT NULL, 
        PostalCode nvarchar(15) NOT NULL,
        CurrentFlag Flag
    );
    GO
    WITH EmployeeTemp (EmpID, LastName, FirstName, Phone, 
                       Address, City, StateProvince, 
                       PostalCode, CurrentFlag)
    AS (SELECT 
           e.BusinessEntityID, c.LastName, c.FirstName, pp.PhoneNumber,
           a.AddressLine1, a.City, sp.StateProvinceCode, 
           a.PostalCode, e.CurrentFlag
        FROM HumanResources.Employee e
            INNER JOIN Person.BusinessEntityAddress AS bea
            ON e.BusinessEntityID = bea.BusinessEntityID
            INNER JOIN Person.Address AS a
            ON bea.AddressID = a.AddressID
            INNER JOIN Person.PersonPhone AS pp
            ON e.BusinessEntityID = pp.BusinessEntityID
            INNER JOIN Person.StateProvince AS sp
            ON a.StateProvinceID = sp.StateProvinceID
            INNER JOIN Person.Person as c
            ON e.BusinessEntityID = c.BusinessEntityID
        )
    INSERT INTO HumanResources.NewEmployee 
        SELECT EmpID, LastName, FirstName, Phone, 
               Address, City, StateProvince, PostalCode, CurrentFlag
        FROM EmployeeTemp;
    GO

C. Usar TOP para limitar los datos insertados de la tabla de origen

En el ejemplo siguiente se usa la cláusula TOP para limitar el número de filas que se insertan en la tabla NewEmployee desde la tabla Employee. En el ejemplo se insertan los datos de dirección para el primer conjunto aleatorio de 10 empleados de la tabla Employee. A continuación, se ejecuta la instrucción SELECT para comprobar el contenido de la tabla NewEmployee.

    USE AdventureWorks2008R2;
    GO
    IF OBJECT_ID (N'HumanResources.NewEmployee', N'U') IS NOT NULL
        DROP TABLE HumanResources.NewEmployee;
    GO
    CREATE TABLE HumanResources.NewEmployee
    (
        BusinessEntityID int NOT NULL,
        LastName nvarchar(50) NOT NULL,
        FirstName nvarchar(50) NOT NULL,
        PhoneNumber Phone NULL,
        AddressLine1 nvarchar(60) NOT NULL,
        City nvarchar(30) NOT NULL,
        State nchar(3) NOT NULL, 
        PostalCode nvarchar(15) NOT NULL,
        CurrentFlag Flag
    );
    GO
    -- Insert 10 random rows into the table NewEmployee.
    INSERT TOP (10) INTO HumanResources.NewEmployee 
        SELECT
           e.BusinessEntityID, c.LastName, c.FirstName, pp.PhoneNumber,
           a.AddressLine1, a.City, sp.StateProvinceCode, 
           a.PostalCode, e.CurrentFlag
        FROM HumanResources.Employee e
            INNER JOIN Person.BusinessEntityAddress AS bea
            ON e.BusinessEntityID = bea.BusinessEntityID
            INNER JOIN Person.Address AS a
            ON bea.AddressID = a.AddressID
            INNER JOIN Person.PersonPhone AS pp
            ON e.BusinessEntityID = pp.BusinessEntityID
            INNER JOIN Person.StateProvince AS sp
            ON a.StateProvinceID = sp.StateProvinceID
            INNER JOIN Person.Person as c
            ON e.BusinessEntityID = c.BusinessEntityID;
    GO
    SELECT  BusinessEntityID, LastName, FirstName, PhoneNumber,
            AddressLine1, City, State, PostalCode, CurrentFlag
    FROM HumanResources.NewEmployee;
    GO

Especificar objetos de destino que no sean tablas estándar

Los ejemplos de esta sección demuestran cómo insertar filas especificando una variable de tabla o vista.

A. Insertar datos especificando una vista

En el siguiente ejemplo se especifica un nombre de vista como objeto de destino; sin embargo, la fila nueva se inserta en la tabla base subyacente. El orden de los valores de la instrucción INSERT debe coincidir con el orden de las columnas de la vista. Para obtener más información, vea Modificar datos mediante una vista.

    USE AdventureWorks2008R2;
    GO
    IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
        DROP TABLE dbo.T1;
    GO
    IF OBJECT_ID ('dbo.V1', 'V') IS NOT NULL
        DROP VIEW dbo.V1;
    GO
    CREATE TABLE T1 ( column_1 int, column_2 varchar(30));
    GO
    CREATE VIEW V1 AS 
    SELECT column_2, column_1 
    FROM T1;
    GO
    INSERT INTO V1 
        VALUES ('Row 1',1);
    GO
    SELECT column_1, column_2 
    FROM T1;
    GO
    SELECT column_1, column_2
    FROM V1;
    GO

B. Insertar datos en una variable de tabla

En el siguiente ejemplo se especifica una variable de tabla como el objeto de destino.

USE AdventureWorks2008R2;
GO
-- Create the table variable.
DECLARE @MyTableVar table(
    LocationID int NOT NULL,
    CostRate smallmoney NOT NULL,
    NewCostRate AS CostRate * 1.5,
    ModifiedDate datetime);

-- Insert values into the table variable.
INSERT INTO @MyTableVar (LocationID, CostRate, ModifiedDate)
    SELECT LocationID, CostRate, GETDATE() FROM Production.Location
    WHERE CostRate > 0;

-- View the table variable result set.
SELECT * FROM @MyTableVar;
GO

Insertar filas en una tabla remota

Los ejemplos de esta sección demuestran cómo insertar filas en una tabla de destino remota mediante un servidor vinculado o una función de conjunto de filas para hacer referencia a la tabla remota.

A. Insertar datos en una tabla remota con un servidor vinculado

El ejemplo siguiente inserta filas en una tabla remota. En el ejemplo se comienza creando un vínculo al origen de datos remoto mediante sp_addlinkedserver. El nombre del servidor vinculado, MyLinkServer, se especifica entonces como parte del nombre de objeto de cuatro partes en el formato server.catalog.schema.object.

    USE master;
    GO
    -- Create a link to the remote data source. 
    -- Specify a valid server name for @datasrc as 'server_name' or 'server_name\instance_name'.
    
    EXEC sp_addlinkedserver @server = N'MyLinkServer',
        @srvproduct = N' ',
        @provider = N'SQLNCLI', 
        @datasrc = N'server_name',
        @catalog = N'AdventureWorks2008R2';
    GO


    USE AdventureWorks2008R2;
    GO
    -- Specify the remote data source in the FROM clause using a four-part name 
    -- in the form linked_server.catalog.schema.object.
    
    INSERT INTO MyLinkServer.AdventureWorks2008R2.HumanResources.Department (Name, GroupName)
    VALUES (N'Public Relations', N'Executive General and Administration');
    GO

B. Insertar datos en una tabla remota con una función OPENQUERY

En el ejemplo siguiente se inserta una fila en una tabla remota especificando la función de conjunto de filas OPENQUERY. En este ejemplo se usa el nombre del servidor vinculado creado en el ejemplo anterior.

-- Use the OPENQUERY function to access the remote data source.

INSERT OPENQUERY (MyLinkServer, 'SELECT Name, GroupName FROM AdventureWorks2008R2.HumanResources.Department')
VALUES ('Environmental Impact', 'Engineering');
GO

C. Insertar datos en una tabla remota con una función OPENDATASOURCE

En el ejemplo siguiente se inserta una fila en una tabla remota especificando la función de conjunto de filas OPENDATASOURCE. Especifique un nombre de servidor válido para el origen de datos con el formato server_name o server_name\instance_name.

-- Use the OPENDATASOURCE function to specify the remote data source.
-- Specify a valid server name for Data Source using the format server_name or server_name\instance_name.

INSERT INTO OPENDATASOURCE('SQLNCLI',
    'Data Source= <server_name>; Integrated Security=SSPI')
    .AdventureWorks2008R2.HumanResources.Department (Name, GroupName)
    VALUES (N'Standards and Methods', 'Quality Assurance');
GO

Importación en bloque a partir de tablas o archivos de datos

En los ejemplos de esta sección se muestran dos métodos para la importación en bloque (carga masiva) en una tabla con la instrucción INSERT.

A. Insertar los datos en un montón con registro mínimo

El ejemplo siguiente crea una tabla nueva (un montón) e inserta los datos en ella desde otra tabla con registro mínimo. El ejemplo supone que el modelo de recuperación de la base de datos AdventureWorks2008R2 está establecido en FULL. Para asegurarse de que se usa el registro mínimo, el modelo de recuperación de la base de datos AdventureWorks2008R2 se establece en BULK_LOGGED antes de que las filas se inserten y se restablece en FULL a continuación de la instrucción INSERT INTO…SELECT. Además, se especifica la sugerencia TABLOCK para la tabla de destino Sales.SalesHistory. De esta forma, se asegura de que la instrucción use el espacio mínimo en el registro de transacciones y funcione eficazmente.

    USE AdventureWorks2008R2;
    GO
    -- Create the target heap.
    CREATE TABLE Sales.SalesHistory(
        SalesOrderID int NOT NULL,
        SalesOrderDetailID int NOT NULL,
        CarrierTrackingNumber nvarchar(25) NULL,
        OrderQty smallint NOT NULL,
        ProductID int NOT NULL,
        SpecialOfferID int NOT NULL,
        UnitPrice money NOT NULL,
        UnitPriceDiscount money NOT NULL,
        LineTotal money NOT NULL,
        rowguid uniqueidentifier ROWGUIDCOL  NOT NULL,
        ModifiedDate datetime NOT NULL );
    GO
    -- Temporarily set the recovery model to BULK_LOGGED.
    ALTER DATABASE AdventureWorks2008R2
    SET RECOVERY BULK_LOGGED;
    GO
    -- Transfer data from Sales.SalesOrderDetail to Sales.SalesHistory
    INSERT INTO Sales.SalesHistory WITH (TABLOCK)
        (SalesOrderID, 
         SalesOrderDetailID,
         CarrierTrackingNumber, 
         OrderQty, 
         ProductID, 
         SpecialOfferID, 
         UnitPrice, 
         UnitPriceDiscount,
         LineTotal, 
         rowguid, 
         ModifiedDate)
    SELECT * FROM Sales.SalesOrderDetail;
    GO
    -- Reset the recovery model.
    ALTER DATABASE AdventureWorks2008R2
    SET RECOVERY FULL;
    GO

B. Usar la función OPENROWSET con BULK para la importación en bloque de datos en una tabla

En el ejemplo siguiente se insertan filas de un archivo de datos en una tabla especificando la función OPENROWSET. La sugerencia de tabla IGNORE_TRIGGERS se especifica para la optimización del rendimiento. Para obtener más ejemplos, vea Importación masiva de datos mediante BULK INSERT u OPENROWSET(BULK...).

    -- Use the OPENROWSET function to specify the data source and specifies the IGNORE_TRIGGERS table hint.
    INSERT INTO HumanResources.Department WITH (IGNORE_TRIGGERS) (Name, GroupName)
    SELECT b.Name, b.GroupName 
    FROM OPENROWSET (
        BULK 'C:\SQLFiles\DepartmentData.txt',
        FORMATFILE = 'C:\SQLFiles\BulkloadFormatFile.xml',
        ROWS_PER_BATCH = 15000)AS b ;
    GO

Invalidar el comportamiento predeterminado del optimizador de consultas mediante sugerencias

Los ejemplos de esta sección demuestran cómo usar sugerencias de tabla para invalidar de forma temporal el comportamiento predeterminado del optimizador de consultas cuando se procesa la instrucción INSERT.

Advertencia
Como el optimizador de consultas de SQL Server suele seleccionar el mejor plan de ejecución de una consulta, se recomienda que únicamente los administradores de bases de datos y desarrolladores experimentados utilicen las sugerencias como último recurso.

A. Usar la sugerencia TABLOCK para especificar un método de bloqueo

En el ejemplo siguiente se especifica que se aplique un bloqueo exclusivo (X) a la tabla Production.Location y que se mantenga hasta que el final de la instrucción INSERT.

    USE AdventureWorks2008R2;
    GO
    INSERT INTO Production.Location WITH (XLOCK)
    (Name, CostRate, Availability)
    VALUES ( N'Final Inventory', 15.00, 80.00);
    GO

Capturar los resultados de la instrucción INSERT

Los ejemplos de esta sección demuestran cómo usar la cláusula OUTPUT para devolver información de cada fila afectada por una instrucción INSERT o de expresiones que se basan en esta instrucción. Estos resultados se pueden devolver a la aplicación de procesamiento para que los use en mensajes de confirmación, archivado y otros requisitos similares de una aplicación.

A. Usar OUTPUT con una instrucción INSERT

En el siguiente ejemplo se inserta una fila en la tabla ScrapReason y se usa la cláusula OUTPUT para devolver los resultados de la instrucción a la variable de tabla @MyTableVar. Dado que la columna ScrapReasonID de la tabla ScrapReason se ha definido con una propiedad IDENTITY, no se especifica ningún valor en la instrucción INSERT para esa columna. No obstante, observe que el valor generado por Motor de base de datos para esa columna se devuelve en la cláusula OUTPUT de la columna INSERTED.ScrapReasonID.

    USE AdventureWorks2008R2;
    GO
    DECLARE @MyTableVar table( NewScrapReasonID smallint,
                               Name varchar(50),
                               ModifiedDate datetime);
    INSERT Production.ScrapReason
        OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
            INTO @MyTableVar
    VALUES (N'Operator error', GETDATE());
    
    --Display the result set of the table variable.
    SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
    --Display the result set of the table.
    SELECT ScrapReasonID, Name, ModifiedDate 
    FROM Production.ScrapReason;
    GO

B. Usar OUTPUT con columnas de identidad y calculadas

En el siguiente ejemplo se crea la tabla EmployeeSales y, a continuación, se insertan varias filas en ella usando una instrucción INSERT con una instrucción SELECT para recuperar los datos de las tablas de origen. La tabla EmployeeSales contiene una columna de identidad (EmployeeID) y una columna calculada (ProjectedSales). Puesto que Motor de base de datos genera estos valores durante la operación de inserción, ninguna de estas columnas se puede definir en @MyTableVar.

    USE AdventureWorks2008R2 ;
    GO
    IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
        DROP TABLE dbo.EmployeeSales;
    GO
    CREATE TABLE dbo.EmployeeSales
    ( EmployeeID   int IDENTITY (1,5)NOT NULL,
      LastName     nvarchar(20) NOT NULL,
      FirstName    nvarchar(20) NOT NULL,
      CurrentSales money NOT NULL,
      ProjectedSales AS CurrentSales * 1.10 
    );
    GO
    DECLARE @MyTableVar table(
      LastName     nvarchar(20) NOT NULL,
      FirstName    nvarchar(20) NOT NULL,
      CurrentSales money NOT NULL
      );
    
    INSERT INTO dbo.EmployeeSales (LastName, FirstName, CurrentSales)
      OUTPUT INSERTED.LastName, 
             INSERTED.FirstName, 
             INSERTED.CurrentSales
      INTO @MyTableVar
        SELECT c.LastName, c.FirstName, sp.SalesYTD
        FROM Sales.SalesPerson AS sp
        INNER JOIN Person.Person AS c
            ON sp.BusinessEntityID = c.BusinessEntityID
        WHERE sp.BusinessEntityID LIKE '2%'
        ORDER BY c.LastName, c.FirstName;
    
    SELECT LastName, FirstName, CurrentSales
    FROM @MyTableVar;
    GO
    SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
    FROM dbo.EmployeeSales;
    GO

C. Insertar los datos devueltos por una cláusula OUTPUT

En el ejemplo siguiente se capturan los datos devueltos por la cláusula OUTPUT de una instrucción MERGE y se insertan en otra tabla. La instrucción MERGE actualiza diariamente la columna Quantity de la tabla ProductInventory basándose en los pedidos procesados en la tabla SalesOrderDetail. También elimina las filas correspondientes a los productos cuyas existencias se colocan en el valor 0. En el ejemplo se capturan las filas que se eliminan y se insertan en otra tabla, ZeroInventory, que realiza el seguimiento de los productos sin existencias.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID(N'Production.ZeroInventory', N'U') IS NOT NULL
    DROP TABLE Production.ZeroInventory;
GO
--Create ZeroInventory table.
CREATE TABLE Production.ZeroInventory (DeletedProductID int, RemovedOnDate DateTime);
GO

INSERT INTO Production.ZeroInventory (DeletedProductID, RemovedOnDate)
SELECT ProductID, GETDATE()
FROM
(   MERGE Production.ProductInventory AS pi
    USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
           JOIN Sales.SalesOrderHeader AS soh
           ON sod.SalesOrderID = soh.SalesOrderID
           AND soh.OrderDate = '20070401'
           GROUP BY ProductID) AS src (ProductID, OrderQty)
    ON (pi.ProductID = src.ProductID)
    WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0
        THEN DELETE
    WHEN MATCHED
        THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
    OUTPUT $action, deleted.ProductID) AS Changes (Action, ProductID)
WHERE Action = 'DELETE';
IF @@ROWCOUNT = 0
PRINT 'Warning: No rows were inserted';
GO
SELECT DeletedProductID, RemovedOnDate FROM Production.ZeroInventory;

Vea también

Referencia

INSERT (Transact-SQL)

IDENTITY (propiedad de Transact-SQL)

Compartir a través de