Notes
L’accès à cette page nécessite une autorisation. Vous pouvez essayer de vous connecter ou de modifier des répertoires.
L’accès à cette page nécessite une autorisation. Vous pouvez essayer de modifier des répertoires.
S’applique à :SQL Server
Azure SQL Database
Azure SQL Managed Instance
Cet article fournit une vue d’ensemble de l’utilisation de l’instruction Transact-SQL BULK INSERT
et de l’instruction INSERT...SELECT * FROM OPENROWSET(BULK...)
pour importer en bloc des données à partir d’un fichier de données dans une table SQL Server ou Azure SQL Database.
Cet article décrit également les considérations de sécurité relatives à l’utilisation BULK INSERT
et OPENROWSET(BULK...)
à l’utilisation de ces méthodes pour importer en bloc à partir d’une source de données distante.
Instruction BULK INSERT
BULK INSERT
charge les données d’un fichier de données dans une table. Cette fonctionnalité est similaire à celle fournie par l’option in
de la bcp
commande. Toutefois, le fichier de données est lu par le processus SQL Server. Pour obtenir une description de la BULK INSERT
syntaxe, consultez BULK INSERT (Transact-SQL).
Exemples BULK INSERT
- BULK INSERT (Transact-SQL)
- Exemples d'importation et d'exportation en bloc de documents XML (SQL Server)
- Conserver des valeurs d'identité lors de l'importation de données en bloc (SQL Server)
- Conserver les valeurs null ou les valeurs par défaut lors de l’importation en bloc (SQL Server)
- Spécifier les indicateurs de fin de champ et de ligne (SQL Server)
- Utiliser un fichier de format pour importer des données en bloc (SQL Server)
- Utiliser le format caractère pour importer ou exporter des données (SQL Server)
- Utiliser le format natif pour importer ou exporter des données (SQL Server)
- Utiliser le format caractère Unicode pour importer ou exporter des données (SQL Server)
- Utiliser le format natif Unicode pour importer ou exporter des données (SQL Server)
- Utiliser un fichier de format pour ignorer une colonne de table (SQL Server)
- Utiliser un fichier de format pour mapper les colonnes d’une table aux champs d’un fichier de données (SQL Server)
Fonction OPENROWSET (BULK…)
Le OPENROWSET
fournisseur d’ensembles de lignes en bloc est accessible en appelant la OPENROWSET
fonction et en spécifiant l’option BULK
. La OPENROWSET(BULK...)
fonction vous permet d’accéder aux données distantes en vous connectant à une source de données distante, telle qu’un fichier de données, via un fournisseur de données.
Pour importer en bloc des données, appelez OPENROWSET(BULK...)
à partir d’une SELECT...FROM
clause dans une INSERT
instruction.
La syntaxe de base pour l'importation en bloc de données est la suivante :
INSERT ... SELECT * FROM OPENROWSET(BULK...)
Lorsqu’elle est utilisée dans une INSERT
instruction, OPENROWSET(BULK...)
prend en charge les indicateurs de table. Outre les indicateurs de table standard, tels que TABLOCK
, la BULK
clause peut accepter les indicateurs de table spécialisés suivants :
-
IGNORE_CONSTRAINTS
(ignore uniquement les contraintes CHECK) IGNORE_TRIGGERS
KEEPDEFAULTS
KEEPIDENTITY
Pour plus d’informations, consultez indicateurs de table (Transact-SQL).
Pour plus d’informations sur les utilisations supplémentaires de l’option BULK, consultez OPENROWSET BULK (Transact-SQL).
Exemples de l'instruction INSERT...SELECT S* FROM OPENROWSET (BULK...)
- Exemples d'importation et d'exportation en bloc de documents XML (SQL Server)
- Conserver des valeurs d'identité lors de l'importation de données en bloc (SQL Server)
- Conserver les valeurs null ou les valeurs par défaut lors de l’importation en bloc (SQL Server)
- Utiliser un fichier de format pour importer des données en bloc (SQL Server)
- Utiliser le format caractère pour importer ou exporter des données (SQL Server)
- Utiliser un fichier de format pour ignorer une colonne de table (SQL Server)
- Utiliser un fichier de format pour ignorer un champ de données (SQL Server)
- Utiliser un fichier de format pour mapper les colonnes d’une table aux champs d’un fichier de données (SQL Server)
Considérations de sécurité
Si un utilisateur a recours à une connexion SQL Server , le profil de sécurité du compte du processus SQL Server est alors utilisé. Une connexion via l’authentification SQL Server ne peut pas être authentifiée en dehors du Moteur de base de données. Par conséquent, lorsqu’une commande est lancée par une BULK INSERT
connexion à l’aide de l’authentification SQL Server, la connexion aux données est établie à l’aide du contexte de sécurité du compte de processus SQL Server (compte utilisé par le service moteur de base de données SQL Server).
Pour pouvoir lire les données sources, vous devez octroyer au compte utilisé par le moteur de base de données SQL Server l'accès aux données sources. Par opposition, si un utilisateur SQL Server s'est connecté via l'authentification Windows, il peut lire uniquement les fichiers accessibles par le compte d'utilisateur, indépendamment du profil de sécurité du processus SQL Server .
Prenons l'exemple d'un utilisateur qui s'est connecté à une instance de SQL Server à l'aide de l'authentification Windows. Pour que cet utilisateur puisse utiliser BULK INSERT ou OPENROWSET en vue d'importer les données d'un fichier dans une table SQL Server, le compte d'utilisateur nécessite des droits d'accès en lecture au fichier de données. En bénéficiant de droits accès au fichier de données, l'utilisateur peut importer les données du fichier dans une table même si le processus SQL Server n'a pas l'autorisation d'accéder au fichier. L'utilisateur n'est pas obligé d'accorder au processus SQL Server une autorisation d'accès au fichier.
SQL Server et Microsoft Windows peuvent être configurés afin de permettre à une instance de SQL Server de se connecter à une autre instance de SQL Server en transmettant les informations d'un utilisateur Windows authentifié. Ce procédé est appelé emprunt d'identité ou délégation. Il importe de comprendre comment la version de SQL Server gère les aspects de sécurité en matière d'emprunt d'identité lorsque vous utilisez l'instruction BULK INSERT ou OPENROWSET. L'emprunt d'identité permet au fichier de données de résider sur un ordinateur différent du processus SQL Server ou de l'utilisateur. Par exemple, si un utilisateur sur Ordinateur_A a accès à un fichier de données sur Ordinateur_B, et que la délégation des informations d'identification a été correctement définie, l'utilisateur peut se connecter à une instance de SQL Server s'exécutant sur Ordinateur_C, accéder au fichier de données sur Ordinateur_B, et importer les données en bloc de ce fichier dans une table résidant sur Ordinateur_C.
Importation en bloc dans SQL Server à partir d’un fichier de données distant
Pour utiliser BULK INSERT
ou INSERT...SELECT * FROM OPENROWSET(BULK...)
importer en bloc des données à partir d’un autre ordinateur, le fichier de données doit être partagé entre les deux ordinateurs. Pour spécifier un fichier de données partagé, utilisez son nom de convention d’affectation de noms universelle (UNC), qui prend la forme générale. \\Servername\Sharename\Path\Filename
En outre, le compte utilisé pour accéder au fichier de données doit avoir reçu les autorisations requises pour lire le fichier sur le disque distant.
Par exemple, l'instruction BULK INSERT
ci-dessous importe en bloc des données dans la table SalesOrderDetail
de la base de données AdventureWorks
à partir d'un fichier de données nommé newdata.txt
. Ce fichier de données réside dans un dossier partagé nommé \dailyorders
, dans un répertoire partagé du réseau nommé salesforce
, sur un système nommé computer2
.
BULK INSERT AdventureWorks2022.Sales.SalesOrderDetail
FROM '\\computer2\salesforce\dailyorders\neworders.txt';
Remarque
Cette restriction ne s’applique pas à l’utilitaire bcp
, car le client lit le fichier indépendamment de SQL Server.
Importation en bloc à partir du stockage d’objets blob Azure
Lors de l’importation à partir du stockage Blob Azure et que les données ne sont pas publiques (accès anonyme), créez une instruction CREATE DATABASE SCOPED CREDENTIAL basée sur une clé SAP chiffrée avec une clé CREATE MASTER KEY, puis créez une source de base de données externe à utiliser dans votre BULK INSERT
commande.
Vous pouvez également créer des informations d’identification CREATE DATABASE SCOPED en fonction de l’autorisation MANAGED IDENTITY
des demandes d’accès aux données dans des comptes de stockage non publics. Quand MANAGED IDENTITY
est utilisé, le Stockage Azure doit accorder des autorisations à l’identité managée de l’instance en ajoutant le rôle de contrôle d’accès en fonction du rôle (RBAC) Azure intégré Contributeur aux données Blob du stockage. Ce rôle fournit un accès en lecture/écriture à l’identité managée sur les conteneurs Stockage Blob Azure nécessaires. Les instances Azure SQL Managed Instance ont une identité managée affectée par le système, et peuvent également avoir une ou plusieurs identités managées affectées par l’utilisateur. Vous pouvez utiliser aussi bien des identités managées affectées par le système que des identités managées affectées par l’utilisateur pour autoriser les demandes. Pour l'autorisation, l'identité default
de la Managed Instance est utilisée (c'est-à-dire l'identité managée affectée par l'utilisateur principal ou l'identité managée affectée par le système si l'identité managée affectée par l'utilisateur n'est pas spécifiée).
Important
L’identité managée n’est pas prise en charge sur les versions de SQL Server avant SQL Server 2025.
Remarque
N’utilisez pas de transaction explicite ou vous recevrez une erreur 4861.
Utiliser BULK INSERT
L’exemple suivant montre comment utiliser la BULK INSERT
commande pour charger des données à partir d’un fichier csv dans un emplacement de stockage Blob Azure sur lequel vous avez créé une clé SAP. L’emplacement du stockage Blob Azure est configuré comme source de données externe. Ceci nécessite des informations d’identification délimitées à la base de données avec une signature d’accès partagé chiffrée à l’aide d’une clé principale dans la base de données utilisateur.
--> 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');
L’exemple suivant montre comment utiliser la BULK INSERT
commande pour charger des données à partir d’un fichier csv dans un emplacement de stockage Blob Azure à l’aide de l’identité managée. L’emplacement du stockage Blob Azure est configuré comme source de données externe.
--> 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');
Important
Azure SQL Database ne prend pas en charge la lecture dans des fichiers Windows.
Utiliser OPENROWSET
L’exemple suivant montre comment utiliser la OPENROWSET
commande pour charger des données à partir d’un fichier csv dans un emplacement de stockage Blob Azure sur lequel vous avez créé une clé SAP. L’emplacement du stockage Blob Azure est configuré comme source de données externe. Ceci nécessite des informations d’identification délimitées à la base de données avec une signature d’accès partagé chiffrée à l’aide d’une clé principale dans la base de données utilisateur.
--> 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;
Important
Azure SQL Database ne prend pas en charge la lecture dans des fichiers Windows.