Uso de BULK INSERT u OPENROWSET(BULK...) para importar datos a SQL Server

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance

En este artículo se ofrece información general sobre cómo usar las instrucciones Transact-SQL BULK INSERT e INSERT...SELECT * FROM OPENROWSET(BULK...) para realizar una importación masiva de datos desde un archivo de datos a una tabla de SQL Server o Azure SQL Database. En este artículo también se describen las consideraciones de seguridad del uso de BULK INSERT y OPENROWSET(BULK...), así como el uso de estos métodos para una importación en bloque desde un origen de datos remoto.

Instrucción BULK INSERT

BULK INSERT carga datos de un archivo de datos a una tabla. Esta funcionalidad es parecida a la que ofrece la opción in del comando bcp, aunque el que lee el archivo de datos es el proceso de SQL Server. Para obtener una descripción de la sintaxis de BULK INSERT, vea BULK INSERT (Transact-SQL).

Ejemplos de BULK INSERT

• BULK INSERT
• Ejemplos de importación y exportación de forma masiva documentos XML
• Mantener valores de identidad al importar datos de forma masiva
• Mantener valores NULL o usar valores predeterminados durante la importación masiva
• Especificar terminadores de campo y de fila
• Usar un archivo de formato para importar datos de forma masiva
• Usar el formato de caracteres para importar o exportar datos
• Usar el formato nativo para importar o exportar datos
• Usar el formato de caracteres Unicode para importar o exportar datos
• Usar el formato nativo Unicode para importar o exportar datos
• Usar un archivo de formato para omitir una columna de tabla
• Usar un archivo de formato para asignar columnas de tabla a campos de un archivo de datos

Función OPENROWSET(BULK…)

Se tiene acceso al proveedor de conjuntos de filas BULK de OPENROWSET al llamar a la función OPENROWSET y especificar la opción BULK. La función OPENROWSET(BULK...) permite acceder a datos remotos mediante la conexión a un origen de datos remoto como, por ejemplo, un archivo de datos, a través de un proveedor OLE DB.

Para realizar una importación masiva de datos, llame a OPENROWSET(BULK...) desde una cláusula SELECT...FROM en una instrucción INSERT. La sintaxis básica de una importación masiva de datos es:

INSERT ... SELECT * FROM OPENROWSET(BULK...)

Cuando se usa en una instrucción INSERT, OPENROWSET(BULK...) admite sugerencias de tabla. Además de las sugerencias de tabla normales, como TABLOCK, la cláusula BULK puede aceptar las sugerencias de tablas especializadas siguientes: IGNORE_CONSTRAINTS (solo pasa por alto las restricciones CHECK), IGNORE_TRIGGERS, KEEPDEFAULTS y KEEPIDENTITY. Para obtener más información, vea Sugerencias de tabla (Transact-SQL).

Para obtener información sobre los usos adicionales de la opción BULK, vea OPENROWSET (Transact-SQL).

Instrucciones INSERT...SELECT * FROM OPENROWSET(BULK...), ejemplos

• Ejemplos de importación y exportación de forma masiva documentos XML
• Mantener valores de identidad al importar datos de forma masiva
• Mantener valores NULL o usar valores predeterminados durante la importación masiva
• Usar un archivo de formato para importar datos de forma masiva
• Usar el formato de caracteres para importar o exportar datos
• Usar un archivo de formato para omitir una columna de tabla
• Usar un archivo de formato para omitir un campo de datos
• Usar un archivo de formato para asignar columnas de tabla a campos de un archivo de datos

Consideraciones sobre la seguridad

Si un usuario utiliza un inicio de sesión de SQL Server , se utilizará el perfil de seguridad de la cuenta de proceso de SQL Server . Un inicio de sesión que use autenticación de SQL Server no se puede autenticar fuera del Motor de base de datos. Por tanto, cuando un inicio de sesión que usa autenticación de SQL Server inicia un comando BULK INSERT, la conexión con los datos se realiza usando el contexto de seguridad de la cuenta de proceso de SQL Server (la cuenta usada por el servicio Motor de base de datos de SQL Server).

Para leer correctamente los datos de origen, debe conceder acceso a los datos de origen a la cuenta usada por el Motor de base de datos de SQL Server. Por el contrario, si un usuario de SQL Server ha iniciado sesión mediante autenticación de Windows, el usuario solo puede leer los archivos a los que la cuenta de usuario tiene acceso, independientemente del perfil de seguridad del proceso de SQL Server .

Por ejemplo, imagine un usuario que ha iniciado sesión en una instancia de SQL Server mediante autenticación de Windows. Para que el usuario pueda utilizar BULK INSERT u OPENROWSET para importar datos de un archivo de datos a una tabla de SQL Server, la cuenta de usuario necesita acceso de lectura para el archivo de datos. Como el usuario dispone de acceso al archivo de datos, podrá importar datos del archivo a la tabla, aunque el proceso de SQL Server no tenga permiso de acceso al archivo. El usuario no tiene que conceder permiso de acceso a archivos al proceso de SQL Server.

SQL Server y Microsoft Windows pueden configurarse para permitir que una instancia de SQL Server se conecte a otra instancia de SQL Server mediante el reenvío de las credenciales de un usuario de Windows autenticado. Esto se conoce como suplantación o delegación. Es importante entender cómo la versión de SQL Server trata la seguridad en la suplantación de usuarios al utilizar BULK INSERT u OPENROWSET. La suplantación de usuarios permite que el archivo de datos resida en un equipo diferente al del proceso de SQL Server o del usuario. Por ejemplo, si un usuario del Equipo_A tiene acceso a un archivo de datos del Equipo_B y la delegación de credenciales se ha establecido correctamente, el usuario puede conectarse a una instancia de SQL Server que se esté ejecutando en el Equipo_C, tener acceso al archivo de datos del Equipo_B y realizar una importación en bloque de datos desde ese archivo a una tabla en el Equipo_C.

Importación en bloque a SQL Server desde un archivo de datos remoto

Para usar BULK INSERT o INSERT...SELECT * FROM OPENROWSET(BULK...) para la importación en bloque de datos desde otro equipo, el archivo de datos debe estar compartido entre los dos equipos. Para especificar un archivo de datos compartido, use la convención de nomenclatura universal (UNC) para el nombre, que adopta el formato general de \\NombreDeServidor\NombreDeRecursoCompartido\RutaDeAcceso\NombreDeArchivo. Además, la cuenta usada para obtener acceso al archivo de datos debe tener los permisos necesarios para leer el archivo en el disco remoto.

Por ejemplo, la siguiente instrucción BULK INSERT realiza la importación masiva de datos en una tabla SalesOrderDetail de la base de datos AdventureWorks desde un archivo de datos denominado newdata.txt. Este archivo de datos reside en una carpeta compartida llamada \dailyorders en un directorio compartido de red llamado salesforce de un sistema llamado computer2.

BULK INSERT AdventureWorks2022.Sales.SalesOrderDetail
   FROM '\\computer2\salesforce\dailyorders\neworders.txt';

Importación en bloque desde Azure Blob Storage

Cuando se importe desde Azure Blob Storage y los datos no sean públicos (acceso anónimo), cree una instancia de DATABASE SCOPED CREDENTIAL basada en una clave SAS cifrada con MASTER KEY y, después, cree un origen de base de datos externo para usarlo en el comando BULK INSERT.

Como alternativa, cree una CREDENCIAL CON ÁMBITO DE BASE DE DATOS basada en MANAGED IDENTITY para autorizar solicitudes de acceso a datos en cuentas de almacenamiento no públicas. Cuando se usa MANAGED IDENTITY, Azure Storage debe conceder permisos a la identidad administrada de la instancia mediante la adición del rol integrado de control de acceso basado en roles (RBAC) Colaborador de datos de blobs de almacenamiento. Este rol proporciona acceso de lectura y escritura a la identidad administrada de los contenedores de Azure Blob Storage necesarios. Azure SQL Managed Instance dispone de una identidad administrada asignada por el sistema, y también puede tener una o varias identidades administradas asignadas por el usuario. Puede usar identidades administradas asignadas por el sistema o identidades administradas asignadas por el usuario para autorizar las solicitudes. Para la autorización, se utilizaría la identidad default de la instancia administrada (es decir, la identidad administrada asignada por el usuario principal o la identidad administrada asignada por el sistema, si no se especifica la identidad administrada asignada por el usuario).

Usar BULK INSERT

En el ejemplo siguiente se muestra cómo usar el comando BULK INSERT para cargar datos desde un archivo csv en una ubicación de Azure Blob Storage en la que se ha creado una clave SAS. La ubicación de Azure Blob Storage está configurada como origen de datos externo. Esto requiere credenciales con ámbito de base de datos mediante una firma de acceso compartido que se cifra con una clave maestra en la base de datos de usuario.

--> Optional - a MASTER KEY is not required if a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword1';
GO
--> Optional - a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z***************';

 -- NOTE: Make sure that you don't have a leading ? in SAS token, and
 -- that you have at least read permission on the object that should be loaded srt=o&sp=r, and
 -- that expiration period is valid (all dates are in UTC time)

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
          LOCATION = 'https://****************.blob.core.windows.net/invoices'
          , CREDENTIAL= MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);

BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (DATA_SOURCE = 'MyAzureBlobStorage');

En el ejemplo siguiente se muestra cómo usar el comando BULK INSERT para cargar datos desde un archivo CSV en una ubicación de Azure Blob Storage que usa identidades administradas. La ubicación de Azure Blob Storage está configurada como origen de datos externo.

--> Optional - a MASTER KEY is not required if a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword1';
GO

--> Optional - a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
WITH IDENTITY = 'Managed Identity';

-- NOTE: Make sure you have granted Storage Bob Data Contributor RBAC on storage to provides read/write access to the managed identity for the necessary Azure Blob Storage containers.

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
          LOCATION = 'https://****************.blob.core.windows.net/invoices'
          , CREDENTIAL= MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);

BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (DATA_SOURCE = 'MyAzureBlobStorage');

Uso de OPENROWSET

En el ejemplo siguiente se muestra cómo usar el comando OPENROWSET para cargar datos desde un archivo csv en una ubicación de Azure Blob Storage en la que se ha creado una clave SAS. La ubicación de Azure Blob Storage está configurada como origen de datos externo. Esto requiere credenciales con ámbito de base de datos mediante una firma de acceso compartido que se cifra con una clave maestra en la base de datos de usuario.

--> Optional - a MASTER KEY is not required if a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword1';
GO
--> Optional - a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z***************';

 -- NOTE: Make sure that you don't have a leading ? in SAS token, and
 -- that you have at least read permission on the object that should be loaded srt=o&sp=r, and
 -- that expiration period is valid (all dates are in UTC time)

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
          LOCATION = 'https://****************.blob.core.windows.net/invoices'
          , CREDENTIAL= MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);

INSERT INTO achievements with (TABLOCK) (id, description)
SELECT * FROM OPENROWSET(
   BULK  'csv/achievements.csv',
   DATA_SOURCE = 'MyAzureBlobStorage',
   FORMAT ='CSV',
   FORMATFILE='csv/achievements-c.xml',
   FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage'
    ) AS DataFile;

Contenido relacionado

• INSERT (Transact-SQL)
• SELECT (cláusula de Transact-SQL)
• Importar y exportar datos de forma masiva
• OPENROWSET (Transact-SQL)
• SELECT (Transact-SQL)
• FROM (Transact-SQL)
• bcp (utilidad)
• BULK INSERT (Transact-SQL)