Compartir a través de

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

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

En este artículo se proporciona información general sobre cómo usar la instrucción Transact-SQL BULK INSERT y la INSERT...SELECT * FROM OPENROWSET(BULK...) instrucción para importar datos de forma masiva desde un archivo de datos en una tabla de SQL Server o Azure SQL Database.

En este artículo también se describen las consideraciones de seguridad para usar BULK INSERT y OPENROWSET(BULK...)y usar estos métodos para importar de forma masiva desde un origen de datos remoto.

Instrucción BULK INSERT

BULK INSERT carga datos de un archivo de datos en una tabla. Esta funcionalidad es similar a la proporcionada por la in opción del bcp comando . Sin embargo, el proceso de SQL Server lee el archivo de datos. Para obtener una descripción de la BULK INSERT sintaxis, vea BULK INSERT (Transact-SQL).

Ejemplos de BULK INSERT

Función OPENROWSET(BULK…)

Se OPENROWSET obtiene acceso al proveedor de conjuntos de filas masivas llamando a la OPENROWSET función y especificando la BULK opción . La OPENROWSET(BULK...) función permite acceder a datos remotos mediante la conexión a un origen de datos remoto, como un archivo de datos, a través de un proveedor de datos.

Para importar datos de forma masiva, llame desde OPENROWSET(BULK...) una SELECT...FROM cláusula dentro de una INSERT instrucción .

La sintaxis básica de una importación masiva de datos es:

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

Cuando se usa en una INSERT instrucción , OPENROWSET(BULK...) admite sugerencias de tabla. Además de las sugerencias de tabla normales, como TABLOCK, la BULK cláusula puede aceptar las siguientes sugerencias de tabla especializadas:

  • IGNORE_CONSTRAINTS (omite solo las restricciones CHECK)
  • IGNORE_TRIGGERS
  • KEEPDEFAULTS
  • 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 BULK (Transact-SQL).

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

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 lo tanto, cuando un inicio de sesión inicia un BULK INSERT inicio de sesión mediante la autenticación de SQL Server, la conexión a los datos se realiza mediante 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...) importar datos de forma masiva desde otro equipo, el archivo de datos debe compartirse entre los dos equipos. Para especificar un archivo de datos compartido, use su nombre de convención de nomenclatura universal (UNC), que toma la forma general, \\Servername\Sharename\Path\Filename.

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';

Nota:

Esta restricción no se aplica a la bcp utilidad porque el cliente lee el archivo independientemente de SQL Server.

Importación en bloque desde Azure Blob Storage

Al importar desde Azure Blob Storage y los datos no son públicos (acceso anónimo), cree una credencial CREATE DATABASE SCOPED CREDENTIAL basada en una clave SAS cifrada con una clave CREATE MASTER KEY y, a continuación, cree un origen de base de datos externo para usarlo en el BULK INSERT comando.

Como alternativa, cree una CREDENCIAL CREATE DATABASE SCOPED basada en MANAGED IDENTITY para autorizar solicitudes de acceso a datos en cuentas de almacenamiento no pública. 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).

Importante

La identidad administrada no se admite en versiones de SQL Server anteriores a SQL Server 2025.

Nota:

No use transacciones explícitas o recibirá un error 4861.

Uso de BULK INSERT

En el ejemplo siguiente se muestra cómo usar el BULK INSERT comando para cargar datos desde un archivo csv en una ubicación de Azure Blob Storage en la que 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 BULK INSERT comando para cargar datos desde un archivo CSV en una ubicación de Azure Blob Storage mediante identidad administrada. 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');

Importante

Azure SQL Database no admite la lectura de archivos de Windows.

Uso de OPENROWSET

En el ejemplo siguiente se muestra cómo usar el OPENROWSET comando para cargar datos desde un archivo csv en una ubicación de Azure Blob Storage en la que 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;

Importante

Azure SQL Database no admite la lectura de archivos de Windows.

Contenido relacionado