BULK INSERT (Transact-SQL)

Importa un archivo de datos en una tabla o vista de base de datos con un formato especificado por el usuario en SQL Server 2008 R2. Use esta instrucción para transferir datos eficazmente entre SQL Server y orígenes de datos heterogéneos.

Icono de vínculo a temasConvenciones de sintaxis de Transact-SQL

Sintaxis

BULK INSERT 
   [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ] 
      FROM 'data_file' 
     [ WITH 
    ( 
   [ [ , ] BATCHSIZE = batch_size ] 
   [ [ , ] CHECK_CONSTRAINTS ] 
   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] 
   [ [ , ] DATAFILETYPE = 
      { 'char' | 'native'| 'widechar' | 'widenative' } ] 
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ] 
   [ [ , ] FIRSTROW = first_row ] 
   [ [ , ] FIRE_TRIGGERS ] 
   [ [ , ] FORMATFILE = 'format_file_path' ] 
   [ [ , ] KEEPIDENTITY ] 
   [ [ , ] KEEPNULLS ] 
   [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ] 
   [ [ , ] LASTROW = last_row ] 
   [ [ , ] MAXERRORS = max_errors ] 
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ] 
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ] 
   [ [ , ] ROWTERMINATOR = 'row_terminator' ] 
   [ [ , ] TABLOCK ] 
   [ [ , ] ERRORFILE = 'file_name' ] 
    )] 

Argumentos

  • database_name
    Es el nombre de la base de datos en la que reside la tabla o vista especificadas. Si no se especifica, es la base de datos actual.

  • schema_name
    Es el nombre del esquema de la tabla o vista. schema_name es opcional si el esquema predeterminado para el usuario que realiza la operación de importación masiva es el esquema de la tabla o vista especificada. Si no se especifica schema y el esquema predeterminado del usuario que realiza la operación de importación masiva es diferente de la tabla o vista especificada, SQL Server devuelve un mensaje de error y se cancela la operación de importación masiva.

  • table_name
    Es el nombre de la tabla o vista en la que se va a realizar una importación masiva de datos. Solo se pueden utilizar vistas en las que todas las columnas hagan referencia a la misma tabla base. Para obtener más información acerca de las restricciones para la carga de datos en vistas, vea INSERT (Transact-SQL).

  • 'data_file'
    Es la ruta de acceso completa al archivo de datos que contiene los datos que se van a importar en la tabla o vista especificada. BULK INSERT puede importar datos desde un disco (incluidos una ubicación de red, disquete, disco duro, etc.).

    data_file debe especificar una ruta de acceso válida del servidor en el que se ejecuta SQL Server. Si data_file es un archivo remoto, especifique un nombre UNC (Convención de nomenclatura universal). Un nombre UNC tiene el formato \\Systemname\ShareName\Path\FileName. Por ejemplo, \\SystemX\DiskZ\Sales\update.txt. 

  • BATCHSIZE **=**batch_size
    Especifica el número de filas de un lote. Cada lote se copia en el servidor como una transacción. Si no ocurre así, SQL Server confirma o revierte la transacción de cada lote. De forma predeterminada, todos los datos del archivo de datos especificado componen un lote. Para obtener información acerca de consideraciones de rendimiento, vea la sección "Comentarios" más adelante en este tema. 

    Para obtener más información, vea Administrar lotes para la importación masiva.

  • CHECK_CONSTRAINTS
    Especifica que deben comprobarse todas las restricciones de la tabla o vista de destino durante la operación de importación masiva. Sin la opción CHECK_CONSTRAINTS, se omiten las restricciones CHECK y FOREIGN KEY, y, después de la operación, la restricción sobre la tabla se marca como de no confianza.

    Nota

    Las restricciones UNIQUE, PRIMARY KEY y NOT NULL se exigen siempre.

    En algún momento, debe examinar las restricciones de toda la tabla. Si la tabla no estaba vacía antes de la operación de importación masiva, el costo de revalidar la restricción puede superar del costo de aplicar restricciones CHECK a los datos incrementales.

    Una situación en la que quizá desee que las restricciones estén deshabilitadas (comportamiento predeterminado) se produce cuando los datos de entrada contienen filas que infringen las restricciones. Con las restricciones CHECK deshabilitadas, puede importar los datos y utilizar después instrucciones Transact-SQL para quitar los datos no válidos.

    Nota

    La opción MAXERRORS no se aplica a la comprobación de restricciones.

    Nota

    En SQL Server 2005 y versiones posteriores, BULK INSERT aplica una nueva validación de datos y comprobaciones de datos que puedan dar lugar a errores en los scripts existentes cuando se ejecutan en datos no válidos de un archivo de datos.

    Para obtener más información, vea Controlar comprobación de restricciones mediante operaciones de importación masiva.

  • CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }
    Especifica la página de códigos de los datos incluidos en el archivo de datos. CODEPAGE solo es pertinente si los datos contienen columnas de tipo char, varchar o text con valores de caracteres mayores que 127 o menores que 32.

    Nota

    Microsoft recomienda especificar un nombre de intercalación para cada columna de un archivo de formato.

    Valor de CODEPAGE

    Descripción

    ACP

    Las columnas con el tipo de datos char, varchar o text se convierten de la página de códigos ANSI/Microsoft Windows (ISO 1252) a la página de códigos de SQL Server.

    OEM (valor predeterminado)

    Las columnas con los tipos de datos char, varchar o text se convierten de la página de códigos OEM del sistema a la página de códigos de SQL Server.

    RAW

    No se realiza ninguna conversión de una página de códigos a otra; se trata de la opción más rápida.

    code_page

    Número específico de una página de códigos; por ejemplo, 850.

    Nota importanteImportante
    SQL Server no admite la página de códigos 65001 (codificación UTF-8).

    Para obtener más información, vea Copiar datos entre distintas intercalaciones.

  • DATAFILETYPE = { 'char' | 'native' | 'widechar' | 'widenative' }
    Especifica que BULK INSERT realiza la operación de importación con el valor de tipo de archivo de datos especificado.

    Valor de DATAFILETYPE

    Todos los datos representados en:

    char (valor predeterminado)

    Formato de caracteres.

    Para obtener más información, vea Usar el formato de caracteres para importar o exportar datos.

    native

    Tipos de datos nativos (base de datos). Cree el archivo de datos nativos mediante la importación masiva de datos desde SQL Server con la utilidad bcp.

    El valor native ofrece una alternativa de mayor rendimiento al valor char.

    Para obtener más información, vea Usar el formato nativo para importar o exportar datos.

    widechar

    Caracteres Unicode.

    Para obtener más información, vea Usar el formato de caracteres Unicode para importar o exportar datos.

    widenative

    Tipos de datos nativos (base de datos), salvo en las columnas char, varchar y text en las que los datos se almacenan como datos Unicode. Cree el archivo de datos de widenative mediante la importación masiva de datos desde SQL Server con la utilidad bcp.

    El valor widenative ofrece una alternativa de mayor rendimiento a widechar. Si el archivo de datos contiene caracteres extendidos ANSI, especifique widenative.

    Para obtener más información, vea Usar el formato nativo Unicode para importar o exportar datos.

  • FIELDTERMINATOR ='field_terminator'
    Especifica el terminador de campo que se va a utilizar para archivos de datos de tipo char y widechar. El terminador de campo predeterminado es \t (tabulador). Para obtener más información, vea Especificar terminadores de campo y de fila.

  • FIRSTROW **=**first_row
    Especifica el número de la primera fila que se va a cargar. El valor predeterminado es la primera fila del archivo de datos especificado. FIRSTROW comienza en 1.

    Nota

    El atributo FIRSTROW no está pensado para saltar los encabezados de columna. La instrucción BULK INSERT no permite omitir los encabezados. Al omitir filas, SQL Server Database Engine (Motor de base de datos de SQL Server) solo analiza los terminadores de campo y no valida los datos en los campos de las filas omitidas.

  • FIRE_TRIGGERS
    Especifica que se ejecutarán todos los desencadenadores de inserción definidos en la tabla de destino durante la operación de importación masiva. Si se definen desencadenadores para operaciones INSERT en la tabla de destino, se activan para cada lote completado.

    Si no se especifica FIRE_TRIGGERS, no se ejecuta ningún desencadenador de inserción.

    Para obtener más información, vea Controlar la ejecución de desencadenadores al importar datos masivamente.

  • FORMATFILE ='format_file_path'
    Especifica la ruta de acceso completa de un archivo de formato. Un archivo de formato describe el archivo de datos que contiene respuestas almacenadas creado con la utilidad bcp en la misma tabla o vista. Se debe usar el archivo de formato si:

    • El archivo de datos contiene un número de columnas mayor o menor que la tabla o vista.

    • Las columnas están en un orden diferente.

    • Los delimitadores de columna varían.

    • Hay otros cambios en el formato de los datos. Los archivos de formato se suelen crear con la utilidad bcp y se modifican con un procesador de texto si es necesario. Para obtener más información, vea bcp (utilidad).

  • KEEPIDENTITY
    Especifica que se utilizará el valor o valores de identidad del archivo de datos importado para la columna de identidad. Si no se especifica KEEPIDENTITY, los valores de identidad de esta columna se comprueban pero no se importan y SQL Server asigna automáticamente valores únicos basados en los valores de inicialización y de incremento especificados durante la creación de la tabla. Si el archivo de datos no contiene valores para la columna de identidad de la tabla o vista, utilice un archivo de formato para especificar que se debe omitir la columna de identidad de la tabla o vista cuando se importen los datos; SQL Server asigna automáticamente valores únicos para la columna. Para obtener más información, vea DBCC CHECKIDENT (Transact-SQL).

    Para obtener más información acerca de cómo mantener los valores de identidad, vea Mantener valores de identidad al importar datos de forma masiva.

  • KEEPNULLS
    Especifica que las columnas vacías deben conservar un valor NULL durante la operación de importación masiva, en lugar de tener valores predeterminados para las columnas insertadas. Para obtener más información, vea Mantener valores NULL o utilizar valores predeterminados durante la importación masiva.

  • KILOBYTES_PER_BATCH = kilobytes_per_batch
    Especifica el número aproximado de kilobytes (KB) de datos por lote como kilobytes_per_batch. De forma predeterminada, el valor de KILOBYTES_PER_BATCH es desconocido. Para obtener información acerca de consideraciones de rendimiento, vea la sección "Comentarios" más adelante en este tema.

    Para obtener más información, vea Administrar lotes para la importación masiva.

  • LASTROW**=**last_row
    Especifica el número de la última fila que se va a cargar. El valor predeterminado es 0, que indica la última fila del archivo de datos especificado.

  • MAXERRORS = max_errors
    Especifica el número máximo de errores de sintaxis permitidos en los datos antes de cancelar la operación de importación masiva. Cada fila que no se puede importar con la operación de importación masiva se omite y se considera un error. Si no se especifica max_errors, el valor predeterminado es 10.

    Nota

    La opción MAX_ERRORS no se aplica para comprobar restricciones ni para convertir tipos de datos money y bigint.

  • ORDER ( { column [ ASC | DESC ] } [ ,... n ] )
    Especifica la forma en que están ordenados los datos del archivo de datos. El rendimiento de la importación masiva mejora si los datos importados se ordenan según el índice clúster de la tabla, si lo hay. Si el archivo de datos se ordena siguiendo otro criterio que no sea el orden de una clave de índice clúster, o si no hay ningún índice clúster en la tabla, la cláusula ORDER se pasa por alto. Los nombres de columna facilitados deben ser nombres válidos en la tabla de destino. De forma predeterminada, la operación de inserción masiva presupone que los datos del archivo no están ordenados. Para optimizar las operaciones de importación masiva, SQL Server también se valida que los datos importados estén ordenados.

    Para obtener más información, vea Controlar el criterio de ordenación en las importaciones masivas de datos.

  • n
    Es un marcador de posición que indica que se pueden especificar varias columnas.

  • ROWS_PER_BATCH **=**rows_per_batch
    Indica el número aproximado de filas de datos del archivo de datos.

    De forma predeterminada, todos los datos del archivo de datos se envían al servidor en una sola transacción y el optimizador de consultas desconoce el número de filas del lote. Si especifica ROWS_PER_BATCH (con el valor > 0) el servidor utiliza este valor para optimizar la operación de importación masiva. El valor especificado para ROWS_PER_BATCH debe ser aproximadamente el mismo que el número real de filas. Para obtener información acerca de consideraciones de rendimiento, vea la sección "Comentarios" más adelante en este tema.

    Para obtener más información, vea Administrar lotes para la importación masiva.

  • ROWTERMINATOR ='row_terminator'
    Especifica el terminador de fila que se va a utilizar para archivos de datos de tipo char y widechar. El terminador de fila predeterminado es \r\n (carácter de nueva línea). Para obtener más información, vea Especificar terminadores de campo y de fila.

  • TABLOCK
    Especifica que se obtiene un bloqueo de tabla durante la operación de importación masiva. Varios clientes pueden cargar una tabla simultáneamente si ésta no tiene índices y se especifica TABLOCK. De forma predeterminada, el comportamiento del bloqueo viene determinado por la opción de tabla table lock on bulk load. Al mantener un bloqueo durante la operación de importación masiva, se reduce la contención por bloqueos de la tabla y en algunos casos puede mejorarse notablemente el rendimiento. Para obtener información acerca de consideraciones de rendimiento, vea la sección "Comentarios" más adelante en este tema.

    Para obtener más información, vea Controlar el comportamiento de bloqueo para la importación masiva.

  • ERRORFILE ='file_name'
    Especifica el archivo utilizado para recopilar filas que tienen errores de formato y no pueden convertirse en un conjunto de filas OLE DB. Estas filas se copian en este archivo de errores desde el archivo de datos "tal cual".

    El archivo de errores se crea cuando se ejecuta el comando. Se produce un error si el archivo ya existe. Además, se crea un archivo de control con la extensión .ERROR.txt. Este archivo hace referencia a cada fila del archivo de errores y proporciona diagnósticos de errores. Tan pronto como se corrigen los errores, se pueden cargar los datos.

Comentarios

Para obtener una comparación de la instrucción BULK INSERT, la instrucción INSERT ... SELECT * FROM OPENROWSET(BULK...) y el comando bcp, vea Acerca de las operaciones de importación y exportación masivas.

Para obtener información sobre cómo preparar los datos para la importación masiva, por ejemplo los requisitos para importar los datos de un archivo de datos .csv, vea Preparar los datos para exportar o importar de forma masiva.

La instrucción BULK INSERT se puede ejecutar en una transacción definida por el usuario. Si se revierte una transacción definida por el usuario que utiliza una instrucción BULK INSERT y una cláusula BATCHSIZE para importar datos en una tabla o vista con varios lotes, se revierten todos los lotes enviados a SQL Server.

Para obtener información acerca de cuándo se registran en el registro de transacciones las operaciones de inserción de filas que se efectúan durante la importación masiva, vea Requisitos previos para el registro mínimo durante la importación masiva.

En SQL Server 2005 y versiones posteriores, BULK INSERT aplica una nueva validación más estricta de los datos y comprobaciones de los datos leídos de un archivo que puedan dar lugar a errores en los scripts existentes cuando se ejecutan en datos no válidos. Por ejemplo, ahora BULK INSERT comprueba que:

  • Las representaciones nativas de los tipos de datos float o real son válidas.

  • Los datos Unicode tienen una longitud de bytes uniforme.

Es posible que los formatos de datos no válidos que se podían importar de forma masiva en versiones anteriores de SQL Server no se carguen ahora. En versiones anteriores de SQL Server, el error no se produce hasta que un cliente intenta tener acceso a los datos no válidos. La validación más estricta evita sorpresas cuando se consultan los datos después de una importación masiva.

Restricciones

Cuando se usa un archivo de formato con BULK INSERT, solo se puede especificar un máximo de 1024 campos. Es el mismo número máximo de columnas permitido en una tabla. Si usa BULK INSERT con un archivo de datos que contenga más de 1024 campos, BULK INSERT genera el error 4822. La utilidad bcp no tiene esta limitación, por lo que para los archivos de datos que contengan más de 1024 campos use el comando bcp.

Consideraciones de rendimiento

Si el número de páginas que van a vaciarse en un único lote supera un umbral interno, podría producirse un examen completo del grupo de búferes para identificar qué páginas se han de vaciar cuando el lote se confirme. Este examen completo puede afectar de forma desfavorable al rendimiento de la importación masiva. Un caso en el que es probable que se supere el umbral interno se produce cuando un grupo de búferes grande se combina con un subsistema de E/S lento. Para evitar los desbordamientos del búfer en equipos grandes, no utilice la sugerencia TABLOCK (que quita la optimización masiva) o use un tamaño de lote menor (que la preserva).

Dado que los equipos varían, es recomendable que pruebe varios tamaños de lote con la carga de datos para averiguar lo que funciona mejor en su caso.

Importar o exportar masivamente documentos SQLXML

Para importar o exportar de forma masiva datos SQLXML, utilice uno de los tipos de datos siguientes en el archivo de formato:

Tipo de datos

Efecto

SQLCHAR o SQLVARYCHAR

Los datos se envían en la página de códigos del cliente o en la página de códigos implícita en la intercalación. El efecto es el mismo que si se especifica DATAFILETYPE ='char' sin especificar un archivo de formato.

SQLNCHAR o SQLNVARCHAR

Los datos se envían como Unicode. El efecto es el mismo que si se especifica DATAFILETYPE = 'widechar' sin especificar un archivo de formato.

SQLBINARY o SQLVARYBIN

Los datos se envían sin conversión.

Conversiones de tipo de cadena a decimal

En SQL Server 2005 y versiones posteriores, las conversiones de tipos de cadena a decimal utilizadas en BULK INSERT siguen las mismas reglas que la función CONVERT de Transact-SQL, que rechaza las cadenas que representan valores numéricos con notación científica. Por lo tanto, BULK INSERT trata esas cadenas como valores no válidos y genera errores de conversión.

Nota

En la versión 7.0 de SQL Server y SQL Server 2000, BULK INSERT admite la conversión de tipo de cadena a decimal para cadenas que representan valores numéricos con notación científica.

Para solucionar este comportamiento, use un archivo de formato para la importación masiva de datos de tipo float con notación científica en una columna con valores decimales. En el archivo de formato, describa explícitamente la columna como de datos real o float. Para obtener más información acerca de estos tipos de datos, vea float y real (Transact-SQL).

Nota

Los archivos de formato representan los datos real como el tipo de datos SQLFLT4 y los datos float como el tipo de datos SQLFLT8. Para obtener más información sobre archivos de formato XML, vea Sintaxis de esquema para archivos de formato XML; para obtener información sobre archivos que no tienen formato XML, vea Especificar el tipo de almacenamiento en archivo mediante bcp.

Ejemplo de importación de un valor numérico que utiliza notación científica

En este ejemplo se utiliza la siguiente tabla:

CREATE TABLE t_float(c1 float, c2 decimal (5,4))

El usuario desea importar masivamente datos en la tabla t_float. El archivo de datos (C:\t_float-c.dat) contiene datos float con notación científica; por ejemplo:

8.0000000000000002E-28.0000000000000002E-2

No obstante, BULK INSERT no puede importar estos datos directamente en t_float, ya que su segunda columna, c2, utiliza el tipo de datos decimal. Por lo tanto, es necesario un archivo de formato. El archivo de formato debe asignar los datos float con notación científica al formato decimal de la columna c2.

El siguiente archivo de formato utiliza el tipo de datos SQLFLT8 para asignar el segundo campo de datos a la segunda columna:

<?xml version="1.0"?>

<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<RECORD>

<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30"/>

<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30"/> </RECORD> <ROW>

<COLUMN SOURCE="1" NAME="c1" xsi:type="SQLFLT8"/>

<COLUMN SOURCE="2" NAME="c2" xsi:type="SQLFLT8"/> </ROW> </BCPFORMAT>

Para utilizar este archivo de formato (con el nombre de archivo C:\t_floatformat-c-xml.xml) para importar los datos de prueba en la tabla de prueba, emita la siguiente instrucción de Transact-SQL:

BULK INSERT bulktest..t_float
FROM 'C:\t_float-c.dat' WITH (FORMATFILE='C:\t_floatformat-c-xml.xml');
GO

Permisos

Requiere los permisos INSERT y ADMINISTER BULK OPERATIONS. Además, es necesario el permiso ALTER TABLE si se da una o varias de las siguientes circunstancias:

  • Existen restricciones y no se ha especificado la opción CHECK_CONSTRAINTS.

    Nota

    El comportamiento predeterminado es deshabilitar las restricciones. Para comprobar las restricciones CHECK explícitamente, utilice la opción CHECK_CONSTRAINTS.

  • Existen desencadenadores y no se ha especificado la opción FIRE_TRIGGER.

    Nota

    De manera predeterminada no se activan los desencadenadores. Para activar los desencadenadores explícitamente, use la opción FIRE_TRIGGER.

  • Se utiliza la opción KEEPIDENTITY para importar el valor de identidad de un archivo de datos.

Delegación de cuentas de seguridad (suplantación)

Si un usuario de SQL Server inicia sesión utilizando la autenticación de Windows, solo puede leer los archivos que son accesibles a la cuenta de usuario, independientemente del perfil de seguridad del proceso de SQL Server.

Si al ejecutar la instrucción BULK INSERT utiliza sqlcmd u osql desde un equipo e inserta datos en SQL Server en un segundo equipo y especifica data_file en un tercer equipo con una ruta de acceso UNC, es posible que reciba el error 4861.

Para resolver este error, utilice la autenticación de SQL Server y especifique un inicio de sesión de SQL Server, que utiliza el perfil de seguridad de la cuenta del proceso de SQL Server, o bien configure Windows para habilitar la delegación de la cuenta de seguridad. Para obtener información acerca de cómo habilitar una cuenta de usuario para que sea de confianza para la delegación, vea la Ayuda de Windows.

Para obtener más información acerca de ésta y otras consideraciones de seguridad en el uso de BULK INSERT, vea Importación masiva de datos mediante BULK INSERT u OPENROWSET(BULK...).

Ejemplos

A. Usar canalizaciones para importar datos de un archivo

En el siguiente ejemplo se importa información detallada de pedidos en la tabla AdventureWorks2008R2.Sales.SalesOrderDetail desde un archivo de datos especificado utilizando una canalización (|) como el terminador de campo y |\n como el terminador de fila.

BULK INSERT AdventureWorks2008R2.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH 
      (
         FIELDTERMINATOR =' |',
         ROWTERMINATOR =' |\n'
      )

B. Usar el argumento FIRE_TRIGGERS

En el ejemplo siguiente se especifica el argumento FIRE_TRIGGERS.

BULK INSERT AdventureWorks2008R2.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH
     (
        FIELDTERMINATOR =' |',
        ROWTERMINATOR = ':\n',
        FIRE_TRIGGERS
      )

C. Usar el salto de línea como terminador de fila

En el siguiente ejemplo se importa un archivo que utiliza el salto de línea como terminador de fila, igual que en una salida de UNIX:

DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = 'BULK INSERT AdventureWorks2008R2.Sales.SalesOrderDetail
FROM ''<drive>:\<path>\<filename>'' 
WITH (ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@bulk_cmd)

Nota

Debido al modo en que Microsoft Windows trata los archivos de texto, (\n se reemplaza automáticamente por \r\n).

Otros ejemplos