Compartir a través de

  • Artículo

INSERT (Transact-SQL)

Agrega una o varias filas nuevas a una tabla o una vista. Para obtener ejemplos, vea Ejemplos de INSERT (Transact-SQL).

Icono de vínculo a temasConvenciones 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 ] ) [ ,...n ] 
    | derived_table 
    | execute_statement
    | <dml_table_source>
    | DEFAULT VALUES 
    } 
} 
[; ]

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

<dml_table_source> ::=
    SELECT <select_list>
    FROM ( <dml_statement_with_output_clause> ) 
      [AS] table_alias [ ( column_alias [ ,...n ] ) ]
    [ WHERE <search_condition> ]
    [ OPTION ( <query_hint> [ ,...n ] ) ]

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 vinculado en el que se ubica la tabla o vista. server_name puede especificarse como un nombre de servidor vinculado o usando la función OPENDATASOURCE.

    Cuando server_name se especifica como un servidor vinculado, se requieren database_name y schema_name. Cuando server_name se especifica con OPENDATASOURCE, database_name y schema_name pueden no aplicarse a todos los orígenes de datos y estar sujetos a las capacidades del proveedor OLE DB que tiene acceso al objeto remoto. Para obtener más información, vea Consultas distribuidas.

  • 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 de tabla, 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 solamente 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. El uso de estas funciones está sujeto a las capacidades del proveedor OLE DB que tiene acceso al objeto remoto. Para obtener más información, vea Consultas distribuidas.

  • 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).

    Nota importanteImportante

    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 piense en 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, Database Engine (Motor de base de datos) debe ser capaz de proporcionar un valor basado en la definición de la columna; en caso contrario, no se puede cargar la fila. Database Engine (Motor de base de datos) proporciona automáticamente un valor para la columna si esta:

    • 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 tiempo.

    • Acepta valores NULL. Se usa un valor NULL.

    • Es una columna calculada. Se utiliza el valor calculado.

    Se debe utilizar column_list y una lista de valores 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. Los resultados se pueden devolver a la aplicación de procesamiento o insertarse en una tabla o variable de tabla para su nuevo procesamiento.

    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 una execute_statement. La cláusula OUTPUT INTO no se admite en instrucciones INSERT que contengan una cláusula <dml_table_source>.

  • VALUES
    Presenta la lista o listas 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 Value 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 de entrada.

    SQL Server 2008 incorpora el constructor de filas de Transact-SQL (que también se denomina constructor con valores de tabla) para especificar varias filas en una única instrucción INSERT. El constructor de filas se compone de una única cláusula VALUES con varias listas de valores escritos entre paréntesis y separados por una coma. Para obtener más información, vea Constructor con valores de tabla (Transact-SQL).

  • DEFAULT
    Hace que 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 esta admite valores NULL, se inserta NULL. Para una columna definida con el tipo de datos timestamp, se inserta el siguiente valor de marca de tiempo. 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 EXECUTE.

    Cuando se hace referencia a los tipos de datos de caracteres Unicode nchar, nvarchar y ntext, debe agregarse como prefijo la letra mayúscula 'N' a 'expression'. Si no se especifica 'N', SQL Server convierte la cadena a la página de códigos que se corresponde con la intercalación predeterminada de la base de datos o columna. Los caracteres que no se encuentren en esta página de códigos se perderán. Para obtener más información, vea Programación en el servidor con Unicode.

  • 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.

    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. En una transacción distribuida, execute_statement no se puede emitir en un servidor vinculado de bucle invertido cuando la conexión tiene varios conjuntos de resultados activos múltiples (MARS).

    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 subproceso principal no se insertan.

    No puede especificar un parámetro con valores de tabla como el destino de una instrucción INSERT EXEC; sin embargo, se puede especificar como un origen en la cadena o procedimiento almacenado INSERT EXEC. Para obtener más información, vea Parámetros con valores de tabla (motor de base de datos).

  • <dml_table_source>
    Especifica que las filas insertadas en la tabla de destino son las que ha devuelto la cláusula OUTPUT de una instrucción INSERT, UPDATE, DELETE o MERGE, filtradas opcionalmente por una cláusula WHERE. Si se especifica <dml_table_source>, el destino de la instrucción INSERT externa debe cumplir las restricciones siguientes:

    • Debe ser una tabla base, no una vista.

    • No puede ser una tabla remota.

    • No puede tener definido ningún desencadenador.

    • No puede participar en ninguna relación clave externa-clave principal.

    • No puede participar en la replicación de mezcla ni en las suscripciones actualizables para la replicación transaccional.

    El nivel de compatibilidad de la base de datos debe estar establecido en 100.

  • <select_list>
    Es una lista separada por comas que especifica las columnas devueltas por la cláusula OUTPUT que se tienen que insertar. Las columnas de <select_list> deben ser compatibles con las columnas en las que se insertan los valores. <select_list> no puede hacer referencia a funciones de agregado ni a TEXTPTR.

    [!NOTA]

    Las variables enumeradas en la lista SELECT hacen referencia a sus valores originales, sin tener en cuenta los cambios realizados en ellos en <dml_statement_with_output_clause>.

  • <dml_statement_with_output_clause>
    Es una instrucción INSERT, UPDATE, DELETE o MERGE válida que devuelve las filas afectadas en una cláusula OUTPUT. La instrucción no puede contener una cláusula WITH y no puede tener como destino tablas remotas o vistas con particiones. Si se especifica UPDATE o DELETE, no puede ser una instrucción UPDATE o DELETE basada en cursores. No se puede hacer referencia a las filas de origen como instrucciones DML anidadas.

  • WHERE <search_condition>
    Es cualquier cláusula WHERE que contiene una condición <search_condition> válida que filtra las filas devueltas por <dml_statement_with_output_clause>. Para obtener más información, vea Condiciones de búsqueda (Transact-SQL). Cuando se utiliza en este contexto, <search_condition> no puede contener subconsultas, funciones escalares definidas por el usuario que realicen acceso a datos, funciones de agregado, TEXTPTR ni predicados de búsqueda de texto completo.

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

Prácticas recomendadas para la carga masiva de datos

Usar INSERT INTO…SELECT para realizar una carga masiva de datos con registro mínimo

Puede usar INSERT INTO <target_table> SELECT <columns> FROM <source_table> para transferir eficazmente un gran número de filas de una tabla, como una tabla de ensayo, a otra tabla con registro mínimo. El registro mínimo puede mejorar el rendimiento de la instrucción y reducir la posibilidad de que la operación rellene el espacio del registro de transacciones disponible durante la transacción.

El registro mínimo para esta instrucción tiene los requisitos siguientes:

  • El modelo de recuperación de la base de datos está establecido en registro simple o masivo.

  • La tabla de destino es un montón vacío o no vacío.

  • La tabla de destino no se usa en la replicación.

  • La sugerencia TABLOCK se especifica para la tabla de destino.

Las filas que se insertan en un montón como el resultado de una acción de inserción en una instrucción MERGE también se pueden registrar con un nivel mínimo.

A diferencia de la instrucción BULK INSERT, que contiene un bloqueo Bulk Update menos restrictivo, INSERT INTO…SELECT con la sugerencia TABLOCK retiene un bloqueo exclusivo (X) en la tabla. Esto significa que no se pueden insertar filas mediante operaciones de inserción en paralelo. Para obtener más información acerca de los bloqueos, vea Modos de bloqueo.

Usar OPENROWSET y BULK para datos de carga masiva

La función OPENROWSET puede aceptar las siguientes sugerencias de tabla, que proporcionan optimizaciones de carga masiva con la instrucción INSERT:

  • La sugerencia TABLOCK puede reducir al mínimo el número de registros para la operación de inserción. El modelo de recuperación de la base de datos debe establecerse en registro simple o masivo, y la tabla de destino no se puede utilizar en la replicación. Para obtener más información, vea Requisitos previos para el registro mínimo durante la importación masiva.

  • La sugerencia IGNORE_CONSTRAINTS puede deshabilitar temporalmente la comprobación de restricciones FOREIGN KEY y CHECK.

  • La sugerencia IGNORE_TRIGGERS puede deshabilitar temporalmente la ejecución de desencadenadores.

  • La sugerencia KEEPDEFAULTS permite la inserción del valor predeterminado de la columna de una tabla, si existe, en lugar de NULL, cuando falta el valor del registro de datos de esa columna.

  • La sugerencia KEEPIDENTITY permite que se usen los valores de identidad en el archivo de datos importado para la columna de identidad en la tabla de destino.

Estas optimizaciones son similares a las que están disponibles con el comando BULK INSERT. Para obtener más información, vea Sugerencias de tabla (Transact-SQL).

Tipos de datos

Al insertar filas, considere el comportamiento de los tipos de datos siguientes:

  • Si se va a cargar un valor en columnas con un tipo de datos char, varchar o varbinary, el relleno o el truncamiento de los espacios en blanco finales (espacios para char y varchar, y ceros para varbinary) se determinan mediante el valor de la opción 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.

  • 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.

  • 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, 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 columna o variable uniqueidentifier. Utilice la función NEWID() para obtener un identificador único global (GUID).

Insertar valores en columnas de tipo definido por el usuario

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

  • Proporciona un valor del tipo definido por el usuario.

  • Suministra un valor en un tipo de datos del sistema de SQL Server, siempre que el tipo definido por el usuario admita la conversión implícita o explícita desde ese 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 CreateNewPoint() definida por el usuario para crear un valor nuevo del tipo Point definido por el usuario e insertar el valor en la tabla Cities.

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

Tratamiento de errores

Puede implementar el tratamiento de errores para la instrucción INSERT especificando la instrucción en una construcción TRY…CATCH. Para obtener más información, vea Usar TRY...CATCH en Transact-SQL.

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 se recibe un mensaje de error.

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 y que no se carguen filas.

Cuando una instrucción INSERT detecta un error aritmético (desbordamiento, división entre cero o error de dominio) al evaluar una expresión, Database Engine (Motor de base de datos) trata dichos errores como si SET ARITHABORT estuviera establecido en ON. El lote se detiene y se devuelve un mensaje de error. Al evaluar una expresión con SET ARITHABORT y SET ANSI_WARNINGS en OFF, si una instrucción INSERT, DELETE o UPDATE encuentra un error aritmético, desbordamiento, división entre cero o error de dominio, SQL Server inserta o actualiza un valor NULL. Si la columna de destino no acepta valores NULL, no se puede efectuar la acción de inserción o actualización, y el usuario recibe un error. Para obtener más información, vea Comportamiento cuando las opciones ARITHABORT y ARITHIGNORE están establecidas en ON.

Interoperabilidad

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. Para obtener más información acerca de los desencadenadores INSTEAD OF, vea CREATE TRIGGER (Transact-SQL).

Limitaciones y restricciones

Cuando se insertan valores en tablas remotas y no se especifican todos los valores de todas las columnas, debe identificar las columnas en las que se deben insertar los valores especificados.

El valor de la opción SET ROWCOUNT se omite en las instrucciones INSERT con vistas locales y remotas con particiones. Asimismo, esta opción se admite para las instrucciones INSERT emitidas contra las tablas remotas.

Nota importanteImportante

La utilización de SET ROWCOUNT no afectará a las instrucciones DELETE, INSERT ni UPDATE en la próxima versión de SQL Server. No use SET ROWCOUNT con las instrucciones DELETE, INSERT y UPDATE en los nuevos trabajos de desarrollo, y modifique las aplicaciones que la utilizan en la actualidad. Le recomendamos que, en su lugar, use la cláusula TOP.

Comportamiento del registro

La instrucción INSERT siempre se registra completamente excepto cuando se usa la función OPENROWSET con la palabra clave BULK o cuando se usa INSERT INTO <target_table> SELECT <columns> FROM <source_table>. Estas operaciones pueden ser registradas mínimamente. Para obtener más información, vea la sección "Prácticas recomendadas para la carga masiva de datos" anteriormente en este tema.

Seguridad

Durante una conexión de servidores vinculados, el servidor de envío proporciona un nombre de inicio de sesión y una contraseña para conectarse en su nombre al servidor de recepción. Para que esta conexión funcione, debe crear una asignación de inicio de sesión entre los servidores vinculados utilizando sp_addlinkedsrvlogin. Para obtener más información, vea Seguridad para servidores vinculados.

Cuando utilice OPENROWSET (BULK…), es importante que entienda el modo en el que SQL Server controla la suplantación. Para obtener más información, vea "Consideraciones de seguridad" en Importación masiva de datos mediante BULK INSERT u OPENROWSET(BULK...).

Permisos

El permiso INSERT es obligatorio en la tabla de destino.

Los permisos para INSERT se adjudican de forma predeterminada a los miembros de la función de servidor fija sysadmin, a las funciones de base de datos fijas 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 de la función fija de servidor bulkadmin.

Ejemplos

Para obtener ejemplos, vea Ejemplos de INSERT (Transact-SQL).