Megosztás a következőn keresztül:

Adatok importálása az SQL Serverbe a BULK INSERT vagy AZ OPENROWSET(BULK...) használatával

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Ez a cikk áttekintést nyújt arról, hogyan használhatja a Transact-SQL BULK INSERT utasítást és az INSERT...SELECT * FROM OPENROWSET(BULK...) utasítást adatok tömeges importálására egy adatfájlból egy SQL Server- vagy Azure SQL Database-táblába.

Ez a cikk azt is ismerteti, hogy milyen biztonsági szempontokat kell figyelembe vennie BULK INSERTOPENROWSET(BULK...)a távoli adatforrásból történő tömeges importáláshoz, és hogyan használhatja ezeket a módszereket.

BULK INSERT utasítás

BULK INSERT adatokat tölt be egy adatfájlból egy táblába. Ez a funkció hasonló a in parancs lehetőségéhez bcp . Az adatfájlt azonban az SQL Server-folyamat olvassa be. A szintaxis leírását a BULK INSERTBULK INSERT (Transact-SQL) című témakörben talál.

PÉLDÁK TÖMEGES BESZÚRÁSRA

OPENROWSET(BULK...) Funkció

A OPENROWSET tömeges sorhalmaz-szolgáltató a OPENROWSET függvény meghívásával és a BULK beállítás megadásával érhető el. A OPENROWSET(BULK...) függvény lehetővé teszi a távoli adatok elérését úgy, hogy egy távoli adatforráshoz, például egy adatfájlhoz csatlakozik egy adatszolgáltatón keresztül.

Az adatok tömeges importálásához egy utasításon OPENROWSET(BULK...) belüli SELECT...FROM záradékból kell hívást kezdeményeznieINSERT.

Az adatok tömeges importálásának alapszintaxisa a következő:

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

Ha INSERT utasításban használjuk, a OPENROWSET(BULK...) támogatja a tábla tippeket. A normál táblázatos tippeken kívül, például TABLOCKa BULK záradék a következő speciális táblázattippeket is elfogadhatja:

  • IGNORE_CONSTRAINTS (csak a CHECK korlátozásokat hagyja figyelmen kívül)
  • IGNORE_TRIGGERS
  • KEEPDEFAULTS
  • KEEPIDENTITY

További információ: Table hints (Transact-SQL).

A BULK beállítás további használatával kapcsolatos információkért lásd: OPENROWSET BULK (Transact-SQL).

BEILLESZT... SELECT * FROM OPENROWSET(BULK...) utasítások – példák

Biztonsági szempontok

Ha egy felhasználó SQL Server-bejelentkezést használ, a rendszer az SQL Server-folyamatfiók biztonsági profilját használja. Az SQL Server-hitelesítést használó bejelentkezések nem hitelesíthetők az adatbázismotoron kívül. Ezért ha egy BULK INSERT parancsot SQL Server-hitelesítéssel történő bejelentkezés indít el, az adatokhoz való kapcsolódás az SQL Server-folyamatfiók (az SQL Server Adatbázismotor szolgáltatás által használt fiók) biztonsági környezetével történik.

A forrásadatok sikeres olvasásához hozzáférést kell adnia az SQL Server adatbázismotor által használt fióknak a forrásadatokhoz. Ezzel szemben, ha egy SQL Server-felhasználó a Windows-hitelesítéssel jelentkezik be, a felhasználó csak azokat a fájlokat olvassa be, amelyeket a felhasználói fiók érhet el, függetlenül az SQL Server-folyamat biztonsági profiljától.

Vegyük például azt a felhasználót, aki Windows-hitelesítéssel jelentkezett be az SQL Server egy példányára. Ahhoz, hogy a felhasználó a BULK INSERT vagy az OPENROWSET használatával adatokat importálhasson egy adatfájlból egy SQL Server-táblába, a felhasználói fiók olvasási hozzáférést igényel az adatfájlhoz. Az adatfájlhoz való hozzáféréssel a felhasználó akkor is importálhat adatokat a fájlból egy táblába, ha az SQL Server-folyamat nem rendelkezik hozzáféréssel a fájlhoz. A felhasználónak nem kell fájlhozzáférési engedélyt adnia az SQL Server-folyamathoz.

Az SQL Server és a Microsoft Windows konfigurálható úgy, hogy az SQL Server egy példánya csatlakozzon egy másik SQL Server-példányhoz egy hitelesített Windows-felhasználó hitelesítő adatainak továbbításával. Ez az eljárás megszemélyesítés vagy delegációnéven ismert. A TÖMEGES BESZÚRÁS vagy AZ OPENROWSET használatakor fontos megérteni, hogy az SQL Server verziója hogyan kezeli a felhasználói megszemélyesítés biztonságát. A felhasználó megszemélyesítése lehetővé teszi, hogy az adatfájl egy másik számítógépen legyen, mint az SQL Server-folyamat vagy a felhasználó. Ha például egy Computer_A felhasználó hozzáfér egy adatfájlhoz a Computer_B, és a hitelesítő adatok delegálása megfelelően lett beállítva, a felhasználó csatlakozhat az Computer_Cfutó SQL Server-példányhoz, hozzáférhet az adatfájlhoz a Computer_B, és tömegesen importálja az adatokat a fájlból a Computer_Cegyik táblájába.

Tömeges importálás az SQL Serverre távoli adatfájlból

BULK INSERT Ha egy másik számítógépről szeretne adatokat importálni vagy INSERT...SELECT * FROM OPENROWSET(BULK...) tömegesen importálni, az adatfájlt meg kell osztani a két számítógép között. Megosztott adatfájl megadásához használja az univerzális elnevezési konvenció (UNC) nevét, amely az általános formát veszi fel. \\Servername\Sharename\Path\Filename

Emellett az adatfájl eléréséhez használt fióknak rendelkeznie kell a fájl távoli lemezen való olvasásához szükséges engedélyekkel.

Az alábbi BULK INSERT utasítás például tömegesen importál adatokat a SalesOrderDetail adatbázis AdventureWorks táblájába egy newdata.txtnevű adatfájlból. Ez az adatfájl egy \dailyorders nevű megosztott mappában található egy salesforce nevű hálózati megosztási könyvtárban egy computer2nevű rendszeren.

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

Jegyzet

Ez a korlátozás nem vonatkozik a bcp segédprogramra, mert az ügyfél az SQL Servertől függetlenül olvassa be a fájlt.

Tömeges importálás az Azure Blob Storage-ból

Amikor az Azure Blob Storage-ból importál, és az adatok nem nyilvánosak (névtelen hozzáférés), hozzon létre egy CREATE DATABASE SCOPED CREDENTIAL azonosítót a CREATE MASTER KULCcsal titkosított SAS-kulcs alapján, majd hozzon létre egy külső adatbázis-forrást a BULK INSERT parancsban való használatra.

Másik lehetőségként hozzon létre egy CREATE DATABASE SCOPED CREDENTIAL azonosítót a MANAGED IDENTITY nem nyilvános tárfiókok adathozzáférési kérelmeinek engedélyezéséhez. A MANAGED IDENTITYhasználatakor az Azure Storage-nak engedélyeket kell adnia a példány felügyelt identitásához a Storage Blob Data Contributor beépített Azure-szerepköralapú hozzáférés-vezérlési (RBAC) szerepkör hozzáadásával, amely olvasási/írási hozzáférést biztosít a felügyelt identitáshoz a szükséges Azure Blob Storage-tárolókhoz. Az Azure SQL Managed Instance rendszer által hozzárendelt felügyelt identitással rendelkezik, és egy vagy több felhasználó által hozzárendelt felügyelt identitással is rendelkezhet. A kérések engedélyezéséhez használhat rendszer által hozzárendelt felügyelt identitásokat vagy felhasználó által hozzárendelt felügyelt identitásokat. Engedélyezéshez a felügyelt példány default identitása lesz használva (ez az elsődleges felhasználó által hozzárendelt felügyelt identitás, vagy rendszer által hozzárendelt felügyelt identitás, ha nincs megadva a felhasználó által hozzárendelt felügyelt identitás).

Fontos

A felügyelt identitás nem támogatott az SQL Server 2025 előtti SQL Server-verziókban.

Jegyzet

Ne használjon explicit tranzakciót, különben 4861-es hibát kap.

A BULK INSERT használata

Az alábbi példa bemutatja, hogyan tölthet be adatokat egy csv-fájlból egy Azure Blob Storage tárolóhelyen, amelyen SAS-kulcsot hozott létre. Az Azure Blob Storage helye külső adatforrásként van konfigurálva. Ehhez adatbázis-hatókörű hitelesítő adatokra van szükség egy megosztott hozzáférésű aláírással, amely a felhasználói adatbázisban található főkulcs használatával van titkosítva.

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

Az alábbi példa bemutatja, hogyan használhatja az BULK INSERT parancsot arra, hogy Managed Identity alkalmazásával adatokat töltsön be egy csv-fájlból egy Azure Blob tárolóhelyszínről. Az Azure Blob Storage helye külső adatforrásként van konfigurálva.

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

Fontos

Az Azure SQL Database nem támogatja a Windows-fájlokból való olvasást.

OPENROWSET használata

Az alábbi példa bemutatja, hogyan tölthet be adatokat egy csv-fájlból egy Azure Blob Storage tárolóhelyen, amelyen SAS-kulcsot hozott létre. Az Azure Blob Storage helye külső adatforrásként van konfigurálva. Ehhez adatbázis-hatókörű hitelesítő adatokra van szükség egy megosztott hozzáférésű aláírással, amely a felhasználói adatbázisban található főkulcs használatával van titkosítva.

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

Fontos

Az Azure SQL Database nem támogatja a Windows-fájlokból való olvasást.

Kapcsolódó tartalom

  • Last updated on