Sdílet prostřednictvím

Import dat do SQL Serveru pomocí funkce BULK INSERT nebo OPENROWSET(BULK...)

  • Článek

platí pro:SQL ServerAzure SQL Databaseazure SQL Managed Instance

Tento článek obsahuje přehled použití příkazu Transact-SQL BULK INSERT a INSERT... SELECT * FROM OPENROWSET(BULK...) příkaz pro hromadný import dat z datového souboru do tabulky SQL Serveru nebo Azure SQL Database. Tento článek také popisuje aspekty zabezpečení pro použití funkce BULK INSERT a OPENROWSET(BULK...) a použití těchto metod k hromadnému importu ze vzdáleného zdroje dat.

Poznámka

Pokud používáte FUNKCI BULK INSERT nebo OPENROWSET(BULK...), je důležité pochopit, jak verze SQL Serveru zpracovává zosobnění. Další informace najdete v části Důležité informace o zabezpečení dále v tomto tématu.

Příkaz BULK INSERT

FUNKCE BULK INSERT načte data z datového souboru do tabulky. Tato funkce je podobná té, kterou poskytuje v možnosti příkazu bcp; datový soubor je však načten procesem SQL Serveru. Podrobnosti o syntaxi BULK INSERT najdete v tématu BULK INSERT (Transact-SQL).

Příklady BULK INSERT

Funkce OPENROWSET(BULK...)

Zprostředkovatel hromadné sady řádků OPENROWSET je přístupný voláním funkce OPENROWSET a zadáním možnosti BULK. Funkce OPENROWSET(BULK...) umožňuje přístup ke vzdáleným datům připojením ke vzdálenému zdroji dat, jako je datový soubor, prostřednictvím zprostředkovatele OLE DB.

Chcete-li hromadně importovat data, volejte funkci OPENROWSET(BULK...) z klauzule SELECT...FROM uvnitř příkazu INSERT. Základní syntaxe hromadného importu dat je:

VLOŽIT... SELECT * FROM OPENROWSET(BULK...)

Při použití v příkazu INSERT podporuje funkce OPENROWSET(BULK...) nápovědu k tabulce. Kromě běžných nápověd tabulky, jako je TABLOCK, může klauzule BULK přijmout následující specializované nápovědy tabulky: IGNORE_CONSTRAINTS (ignoruje pouze omezení CHECK), IGNORE_TRIGGERS, KEEPDEFAULTS a KEEPIDENTITY. Další informace naleznete v části Tipy pro tabulky (Transact-SQL).

Informace o dalších použití možnosti BULK naleznete v tématu OPENROWSET (Transact-SQL).

vložit... příkaz SELECT * FROM OPENROWSET(BULK...) – příklady

Důležité informace o zabezpečení

Pokud uživatel používá přihlášení k SQL Serveru, použije se profil zabezpečení účtu procesu SQL Serveru. Přihlášení pomocí ověřování SQL Serveru nejde ověřit mimo databázový stroj. Proto když je příkaz BULK INSERT inicializován přihlášením pomocí ověřování SQL Serveru, připojení k datům se provádí pomocí kontextu zabezpečení účtu procesu SQL Serveru (účet používaný službou databázového stroje SQL Serveru).

Chcete-li úspěšně číst zdrojová data, musíte účtu, který používá databázový stroj SQL Serveru, udělit přístup ke zdrojovým datům. Pokud se uživatel SQL Serveru přihlásí pomocí ověřování systému Windows, může naopak číst pouze soubory, ke kterým má uživatelský účet přístup bez ohledu na profil zabezpečení procesu SQL Serveru.

Představte si například uživatele, který se přihlásil k instanci SQL Serveru pomocí ověřování systému Windows. Aby uživatel mohl k importu dat z datového souboru do tabulky SQL Serveru použít funkci BULK INSERT nebo OPENROWSET, vyžaduje uživatelský účet přístup pro čtení k datovému souboru. S přístupem k datovému souboru může uživatel importovat data ze souboru do tabulky, i když proces SQL Serveru nemá oprávnění k přístupu k souboru. Uživatel nemusí udělit oprávnění k přístupu k souborům procesu SQL Serveru.

SQL Server a Microsoft Windows je možné nakonfigurovat tak, aby se instance SYSTÉMU SQL Server mohla připojit k jiné instanci SYSTÉMU SQL Server předáním přihlašovacích údajů ověřeného uživatele systému Windows. Toto uspořádání se označuje jako imitace nebo delegace. Pochopení toho, jak SQL Server nakládá se zabezpečením zosobnění uživatele, je důležité, když používáte BULK INSERT nebo OPENROWSET. Zosobnění uživatele umožňuje, aby se datový soubor nacházel na jiném počítači než proces SQL Serveru nebo uživatel. Pokud má uživatel Computer_A například přístup k datovému souboru na Computer_Ba delegování přihlašovacích údajů bylo správně nastaveno, může se uživatel připojit k instanci SQL Serveru, která běží na Computer_C, přistupovat k datovému souboru na Computer_B, a hromadně importovat data z daného souboru do tabulky v Computer_C.

Hromadný import do SQL Serveru ze vzdáleného datového souboru

Chcete-li použít FUNKCI BULK INSERT nebo INSERT... SELECT * FROM OPENROWSET(BULK...) pro hromadný import dat z jiného počítače, musí být datový soubor sdílen mezi těmito dvěma počítači. Chcete-li zadat sdílený datový soubor, použijte název UNC (Universal Naming Convention), který má obecný tvar, \\Název serveru\Název sdílené složky\Cesta\Název souboru. Účet použitý pro přístup k datovému souboru navíc musí mít oprávnění požadovaná ke čtení souboru na vzdáleném disku.

Například následující příkaz BULK INSERT hromadně importuje data do tabulky SalesOrderDetail databáze AdventureWorks z datového souboru s názvem newdata.txt. Tento datový soubor se nachází ve sdílené složce s názvem \dailyorders v adresáři síťové sdílené složky s názvem salesforce v systému s názvem computer2.

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

Poznámka

Toto omezení se nevztahuje na nástroj bcp, protože klient čte soubor nezávisle na SQL Serveru.

Hromadný import ze služby Azure Blob Storage

Při importu z úložiště Azure Blob a pokud data nejsou veřejná (anonymní přístup), vytvořte PŘIHLAŠOVACÍ ÚDAJE V ROZSAHU DATABÁZE na základě klíče SAS šifrovaného pomocí MASTER KEYa poté vytvořte externí zdroj databáze pro použití v příkazu BULK INSERT.

Případně můžete vytvořit POVĚŘENÍ V RÁMCI DATABÁZE na základě MANAGED IDENTITY k autorizaci požadavků na přístup k datům v neveřejných účtech úložiště. Při použití MANAGED IDENTITYmusí úložiště Azure udělit oprávnění spravované identitě instance přidáním integrované role Správce objektů blob služby v rámci řízení přístupu na základě rolí (RBAC), která poskytuje přístup pro čtení a zápis ke spravované identitě pro potřebné kontejnery služby Azure Blob Storage. Spravovaná instance Azure SQL má spravovanou identitu přiřazenou systémem a může mít také jednu nebo více spravovaných identit přiřazených uživatelem. K autorizaci požadavků můžete použít spravované identity přiřazené systémem nebo spravované identity přiřazené uživatelem. Pro autorizaci by se použila default identita spravované instance (to je primární spravovaná identita přiřazená uživatelem nebo spravovaná identita přiřazená systémem, pokud není zadaná spravovaná identita přiřazená uživatelem).

Důležitý

Spravovaná identita se vztahuje pouze na Azure SQL. SQL Server nepodporuje spravovanou identitu.

Poznámka

Nepoužívejte explicitní transakci nebo se zobrazí chyba 4861.

Použití funkce BULK INSERT

Následující příklad ukazuje, jak pomocí příkazu BULK INSERT načíst data ze souboru CSV do umístění úložiště objektů blob v Azure, na kterém jste vytvořili klíč SAS. Umístění úložiště objektů blob v Azure je nakonfigurované jako externí zdroj dat. To vyžaduje přihlašovací údaje s oborem databáze pomocí sdíleného přístupového podpisu, který je šifrovaný pomocí hlavního klíče v uživatelské databázi.

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

Následující příklad ukazuje, jak pomocí příkazu BULK INSERT načíst data ze souboru CSV do umístění úložiště objektů blob v Azure pomocí spravované identity. Umístění úložiště objektů blob v Azure je nakonfigurované jako externí zdroj dat.

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

Důležitý

Spravovaná identita se vztahuje pouze na Azure SQL. SQL Server nepodporuje spravovanou identitu.

Azure SQL Database nepodporuje čtení ze souborů Windows.

Použití OPENROWSET

Následující příklad ukazuje, jak pomocí příkazu OPENROWSET načíst data ze souboru CSV do umístění úložiště objektů blob v Azure, na kterém jste vytvořili klíč SAS. Umístění úložiště objektů blob v Azure je nakonfigurované jako externí zdroj dat. To vyžaduje přihlašovací údaje s oborem databáze pomocí sdíleného přístupového podpisu, který je šifrovaný pomocí hlavního klíče v uživatelské databázi.

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

Důležitý

Azure SQL Database nepodporuje čtení ze souborů Windows.

Související obsah