INSERT (Transact-SQL)

Agrega una fila nueva a una tabla o vista.

Convenciones de sintaxis de Transact-SQL

Sintaxis

[ WITH <common_table_expression> [ ,...n ] ]
INSERT 
    [ TOP ( expression ) [ PERCENT ] ] 
    [ INTO] 
    { <object> | rowset_function_limited 
      [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
    }
{
    [ ( column_list ) ] 
    [ <OUTPUT Clause> ]
    { VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) 
    | derived_table 
    | execute_statement 
    } 
} 
    | DEFAULT VALUES 
[; ]

<object> ::=
{ 
    [ server_name . database_name . schema_name . 
      | database_name .[ schema_name ] . 
      | schema_name . 
    ]
        table_or_view_name
}

Argumentos

• WITH <common_table_expression>
  Especifica el conjunto de resultados con un nombre temporal, denominado también expresión de tabla común, definido en el ámbito de la instrucción INSERT. El conjunto de resultados se deriva de una instrucción SELECT.

  Las expresiones de tabla comunes también se pueden utilizar con las instrucciones SELECT, DELETE, UPDATE y CREATE VIEW. Para obtener más información, vea WITH common_table_expression (Transact-SQL).

• TOP (expression) [ PERCENT ]
  Especifica el número o porcentaje de filas aleatorias que se van a insertar. expression puede ser un número o un porcentaje de filas. Las filas a las que se hace referencia en la expresión TOP utilizadas con INSERT, UPDATE o DELETE no presentan ningún orden.

  En las instrucciones INSERT, UPDATE y DELETE, se deben utilizar paréntesis para delimitar expression en TOP. Para obtener más información, vea TOP (Transact-SQL).

• INTO
  Es una palabra clave opcional que se puede utilizar entre INSERT y la tabla de destino.

• server_name
  Es el nombre del servidor (con la función OPENDATASOURCE como nombre del servidor) en el que se encuentra la tabla o la vista. Si se especifica server_name, son obligatorios database_name y schema_name.

• database_name
  Es el nombre de la base de datos.

• schema_name
  Es el nombre del esquema al que pertenece la tabla o la vista.

• table_or view_name
  Es el nombre de la tabla o la vista que va a recibir los datos.

  Se puede utilizar una variable table en su ámbito como origen de tabla en una instrucción INSERT.

  La vista a la que hace referencia table_or_view_name debe poder actualizarse y debe hacer referencia exactamente a una tabla base en la cláusula FROM de la vista. Por ejemplo, la instrucción INSERT de una vista de varias tablas debe utilizar una column_list que sólo haga referencia a columnas de una tabla base. Para obtener más información acerca de las vistas que se pueden actualizar, vea CREATE VIEW (Transact-SQL).

• rowset_function_limited
  Especifica la función OPENQUERY u OPENROWSET.

• WITH ( <table_hint_limited> [... n ] )
  Especifica una o más sugerencias de tabla permitidas para una tabla de destino. La palabra clave WITH y los paréntesis son obligatorios.

  No se permiten READPAST, NOLOCK ni READUNCOMMITTED. Para obtener más información acerca de las sugerencias de tabla, vea Sugerencias de tabla (Transact-SQL).

  Importante:

  La posibilidad de especificar las sugerencias HOLDLOCK, SERIALIZABLE, READCOMMITTED, REPEATABLEREAD o UPDLOCK en tablas que son destinos de instrucciones INSERT se eliminará en una versión futura de SQL Server. Estas sugerencias no influyen en el rendimiento de las instrucciones INSERT. Evite el uso de dichas sugerencias en los nuevos trabajos de desarrollo y prevea modificar las aplicaciones que las utilizan actualmente.

  Especificar la sugerencia TABLOCK en una tabla que es el destino de una instrucción INSERT tiene el mismo efecto que especificar la sugerencia TABLOCKX. Se realiza un bloqueo exclusivo en la tabla.

• (column_list)
  Es una lista de una o más columnas en las que se insertarán los datos. column_list se debe incluir entre paréntesis y delimitar con comas.

  Si la columna no se incluye en column_list, el SQL Server 2005 Database Engine (Motor de base de datos de SQL Server 2005) debe poder proporcionar un valor basado en la definición de la columna; en caso contrario, no se puede cargar la fila. El Database Engine (Motor de base de datos) proporciona automáticamente un valor para la columna si ésta:

  • Tiene una propiedad IDENTITY. Se usa el valor de identidad incremental siguiente.
  • Tiene un valor predeterminado. Se usa el valor predeterminado de la columna.
  • Tiene un tipo de datos timestamp. Se utiliza el valor actual de marca de hora.
  • Admite valores NULL. Se usa un valor NULL.
  • Es una columna calculada. Se utiliza el valor calculado.

  Se debe utilizar column_list y la lista VALUES al insertar valores explícitos en una columna de identidad. La opción SET IDENTITY_INSERT debe ser ON para la tabla.

• Cláusula OUTPUT
  Devuelve las filas insertadas como parte de la operación de inserción. La cláusula OUTPUT no se admite en las instrucciones DML que hacen referencia a vistas locales con particiones, vistas distribuidas con particiones, tablas remotas o instrucciones INSERT que contengan execute_statement.

• VALUES
  Presenta la lista de valores de datos que se van a insertar. Debe haber un valor de datos por cada columna en column_list, si se especifica, o en la tabla. La lista de valores debe ir entre paréntesis.

  Si los valores de la lista VALUES no están en el mismo orden que las columnas de la tabla o no contienen un valor para cada columna de la tabla, se debe usar column_list para especificar de forma explícita la columna que almacenará cada valor entrante.

• DEFAULT
  Exige que el Database Engine (Motor de base de datos) cargue el valor predeterminado definido para una columna. Si no existe ningún valor predeterminado para la columna y ésta admite valores NULL, se inserta NULL. Para una columna definida con el tipo de datos timestamp, se inserta el siguiente valor de marca de hora. DEFAULT no es un valor válido para una columna de identidad.

• expression
  Es una constante, variable o expresión. La expresión no puede contener una instrucción SELECT o EXECUTE.

• derived_table
  Es cualquier instrucción SELECT válida que devuelva filas con los datos que se van a cargar en la tabla. La instrucción SELECT no puede contener una expresión de tabla común (CTE).

• execute_statement
  Es cualquier instrucción EXECUTE válida que devuelva datos con instrucciones SELECT o READTEXT. La instrucción SELECT no puede contener una CTE.

  Si se utiliza execute_statement con INSERT, cada conjunto de resultados debe ser compatible con las columnas de la tabla o de column_list.

  execute_statement se puede utilizar para ejecutar procedimientos almacenados en el mismo servidor o en un servidor remoto. Se ejecuta el procedimiento en el servidor remoto, se devuelven los conjuntos de resultados al servidor local y se cargan en la tabla del servidor local.

  SQL Server 2008 cambia la semántica de las transacciones de las instrucciones INSERT...EXECUTE que se ejecutan con un servidor vinculado de bucle invertido. En SQL Server 2005, este escenario no se admite y causa un error. En SQL Server 2008, una instrucción INSERT...EXECUTE puede ejecutarse con un servidor vinculado de bucle invertido cuando la conexión no tiene habilitado Multiple Active Result Sets (MARS). Cuando MARS se habilita en la conexión, el comportamiento es el mismo que en SQL Server 2005.

  Si execute_statement devuelve datos con la instrucción READTEXT, cada instrucción READTEXT puede devolver un máximo de 1 MB (1024 KB) de datos. execute_statement también se puede utilizar con procedimientos extendidos. execute_statement inserta los datos devueltos por el subproceso principal del procedimiento extendido; no obstante, los resultados de los subprocesos distintos del principal no se insertan.

• DEFAULT VALUES
  Hace que la nueva fila contenga los valores predeterminados definidos para cada columna.

Notas

INSERT anexa nuevas filas a una tabla. Para reemplazar los datos de una tabla, se deben utilizar las instrucciones DELETE o TRUNCATE TABLE para borrar los datos existentes antes de cargar los datos nuevos con INSERT. Para modificar los valores de una columna en las filas existentes, utilice UPDATE. Puede crear una tabla nueva y cargarla con datos en un solo paso con la opción INTO de la instrucción SELECT.

En las columnas creadas con el tipo de datos uniqueidentifier se almacenan valores binarios de 16 bytes con formato especial. A diferencia de las columnas de identidad, el Database Engine (Motor de base de datos) no genera automáticamente valores de columnas con el tipo de datos uniqueidentifier. Durante una operación de inserción, se pueden usar variables con un tipo de datos uniqueidentifier y constantes de cadena con el formato xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx (36 caracteres incluidos los guiones, donde x es un dígito hexadecimal de los intervalos 0-9 o a-f) para las columnas uniqueidentifier. Por ejemplo, 6F9619FF-8B86-D011-B42D-00C04FC964FF es un valor válido para una variable o columna uniqueidentifier. Utilice la función NEWID() para obtener un identificador único global (GUID).

La configuración de la opción SET ROWCOUNT se omite en las instrucciones INSERT con vistas locales y remotas con particiones. Además, esta opción no se admite para las instrucciones INSERT generadas en tablas remotas en el Database Engine (Motor de base de datos) si el nivel de compatibilidad está establecido en 80 o más.

Cuando una instrucción INSERT detecta un error aritmético (desbordamiento, división por cero o error de dominio) al evaluar una expresión, Database Engine (Motor de base de datos) trata dichos errores como si SET ARITHABORT fuera ON. El resto del lote se detiene y se devuelve un mensaje de error.

Reglas para insertar filas

Al insertar filas, se aplican las siguientes reglas:

• Si se va a cargar un valor en columnas con un tipo de datos char, varchar o varbinary, el relleno o truncamiento de los espacios en blanco finales (espacios para char y varchar, y ceros para varbinary) se determinan mediante la configuración de SET ANSI_PADDING definida para la columna al crear la tabla. Para obtener más información, vea SET ANSI_PADDING (Transact-SQL).
  En la siguiente tabla se muestra la operación predeterminada cuando SET ANSI_PADDING es OFF.

  Tipo de datos	Operación predeterminada
  char	Rellena el valor con espacios hasta el ancho definido de la columna.
  varchar	Quita los espacios finales hasta el último carácter distinto de espacio o hasta un carácter de espacio único para las cadenas compuestas solamente de espacios.
  varbinary	Quita los ceros finales.

• Si se carga una cadena vacía (' ') en una columna con un tipo de datos varchar o text, la operación predeterminada consiste en cargar una cadena de longitud cero.

• Si una instrucción INSERT infringe una restricción o una regla, o si contiene un valor incompatible con el tipo de datos de la columna, la instrucción no se puede ejecutar y el Database Engine (Motor de base de datos) muestra un mensaje de error.

• Al insertar un valor NULL en una columna text o image, no se crea un puntero de texto válido ni se asigna previamente una página de texto de 8 KB. Para obtener más información acerca de cómo insertar datos text e image, vea Usar las funciones text, ntext e image.

• Si INSERT carga varias filas con SELECT o EXECUTE, cualquier infracción de una regla o restricción que se produzca en los valores que se cargan provoca que se detenga la instrucción completa y que no se carguen filas.

• Cuando se insertan valores en tablas en una instancia remota del Database Engine (Motor de base de datos) y no se especifican todos los valores de todas las columnas, debe identificar las columnas en las que se deben insertar los valores especificados.

Usar desencadenadores INSTEAD OF en acciones INSERT

Cuando se define un desencadenador INSTEAD OF en las acciones INSERT en una tabla o vista, se ejecuta el desencadenador en lugar de la instrucción INSERT. Las versiones anteriores de SQL Server sólo admiten desencadenadores AFTER definidos en INSERT y otras instrucciones de modificación de datos. Para obtener más información acerca de los desencadenadores INSTEAD OF, vea CREATE TRIGGER (Transact-SQL).

Insertar valores en columnas de tipo definido por el usuario

Puede insertar valores en columnas de tipo definido por el usuario si:

• Suministra un valor del tipo definido por el usuario.

• Suministra un valor en un tipo de datos del sistema de SQL Server 2005 siempre que el tipo definido por el usuario admita la conversión implícita o explícita del tipo. En el siguiente ejemplo se muestra cómo insertar un valor en una columna de tipo definido por el usuario Point mediante la conversión explícita a partir de una cadena.

  INSERT INTO Cities (Location)
  VALUES ( CONVERT(Point, '12.3:46.2') );
  

  También se puede suministrar un valor binario sin realizar ninguna conversión explícita dado que todos los tipos definidos por el usuario se pueden convertir implícitamente a partir de este valor binario. Para obtener más información acerca de los tipos definidos por el usuario, vea Realizar operaciones en tipos definidos por el usuario.

• Llama a una función definida por el usuario que devuelve un valor del tipo definido por el usuario. En el siguiente ejemplo se utiliza una función definida por el usuario CreateNewPoint() para crear un valor nuevo del tipo definido por el usuario Point e insertar el valor en la tabla Cities.

  INSERT INTO Cities (Location)
  VALUES ( dbo.CreateNewPoint(x, y) );
  

Usar OPENROWSET y BULK para datos de carga masiva

En SQL Server 2005 Database Engine (Motor de base de datos de SQL Server 2005), las nuevas sugerencias de tabla disponibles con el proveedor de conjuntos de filas BULK OPENROWSET ofrecen las siguientes optimizaciones de carga masiva con la instrucción INSERT:

• Registro de carga masiva (minimización del número de registros para la operación de inserción)
• Comprobación de restricciones establecida en ON u OFF
• Ejecución de desencadenadores establecida en ON u OFF

Estos optimizadores son similares a los que hay disponibles con el comando BULK INSERT.

Si una instrucción INSERT realiza una carga masiva en una tabla no vacía, se aplican las siguientes mejoras adicionales del rendimiento:

• Si se divide una página durante una carga masiva, se agregan a la página filas nuevas que no se han registrado por completo.
• Si hay índices sin agrupar, pero no hay ningún índice agrupado en la tabla, puede que las filas de índice deban registrarse por completo, aunque las filas de datos no lo necesiten.

Para obtener más información, vea OPENROWSET (Transact-SQL) y Sugerencias de tabla (Transact-SQL).

Permisos

El permiso INSERT es obligatorio en la tabla de destino.

Los permisos INSERT se conceden de forma predeterminada a los miembros de la función fija de servidor sysadmin, las funciones fijas de base de datos db_owner y db_datawriter y al propietario de la tabla. Los miembros de las funciones sysadmin, db_owner y db_securityadmin, y el propietario de la tabla pueden transferir permisos a otros usuarios.

Para ejecutar INSERT con la opción BULK de la función OPENROWSET, debe ser miembro de la función fija de servidor sysadmin o bulkadmin

Ejemplos

A. Usar una instrucción INSERT simple

En el siguiente ejemplo se inserta una fila en la tabla Production.UnitMeasure. 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 column_list..

USE AdventureWorks;
GO
INSERT INTO Production.UnitMeasure
VALUES (N'F2', N'Square Feet', GETDATE());
GO

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

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

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

C. Insertar datos con menos valores que columnas

En el siguiente ejemplo se muestra la inserción de filas en una tabla con columnas que generan automáticamente un valor o tienen un valor predeterminado. 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 sólo se insertan los valores predeterminados.

USE AdventureWorks;
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) 
        CONSTRAINT default_name DEFAULT ('my column default'),
    column_3 timestamp,
    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

D. 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 AdventureWorks;
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

E. Insertar datos en una columna uniqueidentifier con NEWID()

En el siguiente ejemplo se utiliza la función NEWID() para obtener un GUID para column_2. A diferencia de las columnas de identidad, Database Engine (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 AdventureWorks;
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

F. Insertar datos en una tabla a través de una vista

En el siguiente ejemplo se especifica un nombre de vista en la instrucción INSERT; sin embargo, la fila nueva se inserta en la tabla subyacente de la vista. El orden de la lista VALUES de la instrucción INSERT debe coincidir con el orden de las columnas de la vista.

USE AdventureWorks;
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

G. Insertar datos con las opciones SELECT y EXECUTE

En el siguiente ejemplo se muestran tres métodos diferentes para obtener datos de una tabla e insertarlos en otra. 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 utiliza directamente una instrucción SELECT para recuperar datos de las tablas de origen (Employee, SalesPerson y Contact) y almacenar el conjunto de resultados en la tabla EmployeeSales . La segunda instrucción INSERT ejecuta un procedimiento almacenado que contiene la instrucción SELECT y la tercera instrucción INSERT ejecuta la instrucción SELECT como una cadena literal.

USE AdventureWorks;
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,
  EmployeeID   varchar(11) NOT NULL,
  LastName     varchar(40) NOT NULL,
  SalesDollars money NOT NULL
);
GO
CREATE PROCEDURE dbo.uspGetEmployeeSales 
AS 
    SET NOCOUNT ON;
    SELECT 'PROCEDURE', e.EmployeeID, c.LastName, 
        sp.SalesYTD 
    FROM HumanResources.Employee AS e 
        INNER JOIN Sales.SalesPerson AS sp  
        ON e.EmployeeID = sp.SalesPersonID 
        INNER JOIN Person.Contact AS c
        ON e.ContactID = c.ContactID
    WHERE e.EmployeeID LIKE '2%'
    ORDER BY e.EmployeeID, c.LastName;
GO
--INSERT...SELECT example
INSERT dbo.EmployeeSales
    SELECT 'SELECT', e.EmployeeID, c.LastName, sp.SalesYTD 
    FROM HumanResources.Employee AS e
        INNER JOIN Sales.SalesPerson AS sp
        ON e.EmployeeID = sp.SalesPersonID 
        INNER JOIN Person.Contact AS c
        ON e.ContactID = c.ContactID
    WHERE e.EmployeeID LIKE '2%'
    ORDER BY e.EmployeeID, c.LastName;
GO
--INSERT...EXECUTE procedure example
INSERT EmployeeSales 
EXECUTE uspGetEmployeeSales;
GO
--INSERT...EXECUTE('string') example
INSERT EmployeeSales 
EXECUTE 
('
SELECT ''EXEC STRING'', e.EmployeeID, c.LastName, 
    sp.SalesYTD 
    FROM HumanResources.Employee AS e 
        INNER JOIN Sales.SalesPerson AS sp 
        ON e.EmployeeID = sp.SalesPersonID 
        INNER JOIN Person.Contact AS c
        ON e.ContactID = c.ContactID
    WHERE e.EmployeeID LIKE ''2%''
    ORDER BY e.EmployeeID, c.LastName
');
GO
--Show results.
SELECT DataSource,EmployeeID,LastName,SalesDollars
FROM dbo.EmployeeSales;
GO

H. Insertar datos con la cláusula TOP

En el siguiente ejemplo se crea la tabla NewEmployee y se insertan los datos de dirección para los 10 primeros empleados de la tabla Employee. A continuación, se ejecuta la instrucción SELECT para comprobar el contenido de la tabla NewEmployee.

USE AdventureWorks;
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,
    Phone 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 TOP (10) INTO HumanResources.NewEmployee 
    SELECT
       e.EmployeeID, c.LastName, c.FirstName, c.Phone,
       a.AddressLine1, a.City, sp.StateProvinceCode, 
       a.PostalCode, e.CurrentFlag
    FROM HumanResources.Employee e
        INNER JOIN HumanResources.EmployeeAddress AS ea
        ON e.EmployeeID = ea.EmployeeID
        INNER JOIN Person.Address AS a
        ON ea.AddressID = a.AddressID
        INNER JOIN Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
        INNER JOIN Person.Contact as c
        ON e.ContactID = c.ContactID;
GO
SELECT  EmployeeID, LastName, FirstName, Phone,
        AddressLine1, City, State, PostalCode, CurrentFlag
FROM HumanResources.NewEmployee;
GO

I. Usar OUTPUT con una instrucción INSERT

En el siguiente ejemplo se inserta una fila en la tabla ScrapReason y se utiliza la cláusula OUTPUT para devolver los resultados de la instrucción para la variable @MyTableVartable. Dado que la columna ScrapReasonID se define con una propiedad IDENTITY, no se especifica ningún valor en la instrucción INSERT para dicha columna. No obstante, debe tener en cuenta que el valor generado por el Database Engine (Motor de base de datos) para la columna se devuelve en la cláusula OUTPUT de la columna INSERTED.ScrapReasonID.

USE AdventureWorks;
GO
DECLARE @MyTableVar table( ScrapReasonID 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 ScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate 
FROM Production.ScrapReason;
GO

J. Usar la expresión de tabla común WITH con una instrucción INSERT

En el siguiente ejemplo se crea la tabla NewEmployee. Una expresión de tabla común (EmployeeTemp) define las filas 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 AdventureWorks;
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,
    Phone 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.EmployeeID, c.LastName, c.FirstName, c.Phone,
        a.AddressLine1, a.City, sp.StateProvinceCode, 
        a.PostalCode, e.CurrentFlag
    FROM HumanResources.Employee e
        INNER JOIN HumanResources.EmployeeAddress AS ea
        ON e.EmployeeID = ea.EmployeeID
        INNER JOIN Person.Address AS a
        ON ea.AddressID = a.AddressID
        INNER JOIN Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
        INNER JOIN Person.Contact as c
        ON e.ContactID = c.ContactID
    )
INSERT INTO HumanResources.NewEmployee 
    SELECT EmpID, LastName, FirstName, Phone, 
           Address, City, StateProvince, PostalCode, CurrentFlag
    FROM EmployeeTemp;
GO

K. 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 con una instrucción INSERT y 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 Database Engine (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 AdventureWorks ;
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 HumanResources.Employee AS e
        INNER JOIN Sales.SalesPerson AS sp
        ON e.EmployeeID = sp.SalesPersonID 
        INNER JOIN Person.Contact AS c
        ON e.ContactID = c.ContactID
    WHERE e.EmployeeID 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

Vea también

Referencia

BULK INSERT (Transact-SQL)
CREATE TABLE (Transact-SQL)
DELETE (Transact-SQL)
EXECUTE (Transact-SQL)
FROM (Transact-SQL)
IDENTITY (propiedad de Transact-SQL)
NEWID (Transact-SQL)
SELECT (Transact-SQL)
SET ROWCOUNT (Transact-SQL)
UPDATE (Transact-SQL)

Otros recursos

Insertar datos en una tabla

Ayuda e información

Obtener ayuda sobre SQL Server 2005