Przeczytaj w języku angielskim

Udostępnij za pośrednictwem

BULK INSERT (Transact-SQL)

  • Artykuł

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Importuje plik danych do tabeli bazy danych lub widoku w formacie określonym przez użytkownika w programie SQL Server.

Transact-SQL konwencje składni

Składnia

syntaxsql 
BULK INSERT
   { database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }
      FROM 'data_file'
     [ WITH
    (
   [ [ , ] DATA_SOURCE = 'data_source_name' ]

   -- text formatting options
   [ [ , ] CODEPAGE = { 'RAW' | 'code_page' | 'ACP' | 'OEM' } ]
   [ [ , ] DATAFILETYPE = { 'char' | 'native' | 'widechar' | 'widenative' } ]
   [ [ , ] ROWTERMINATOR = 'row_terminator' ]
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ]
   [ [ , ] FORMAT = 'CSV' ]
   [ [ , ] FIELDQUOTE = 'quote_characters']

   [ [ , ] FIRSTROW = first_row ]
   [ [ , ] LASTROW = last_row ]

   -- input file format options
   [ [ , ] FORMATFILE = 'format_file_path' ]
   [ [ , ] FORMATFILE_DATA_SOURCE = 'data_source_name' ]

   -- error handling options
   [ [ , ] MAXERRORS = max_errors ]
   [ [ , ] ERRORFILE = 'file_name' ]
   [ [ , ] ERRORFILE_DATA_SOURCE = 'errorfile_data_source_name' ]

   -- database options
   [ [ , ] KEEPIDENTITY ]
   [ [ , ] KEEPNULLS ]
   [ [ , ] FIRE_TRIGGERS ]
   [ [ , ] CHECK_CONSTRAINTS ]
   [ [ , ] TABLOCK ]

   -- source options
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ]
   [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch 
   [ [ , ] BATCHSIZE = batch_size ]

    )]

Argumenty

Instrukcja BULK INSERT ma różne argumenty i opcje na różnych platformach. Różnice zostały podsumowane w poniższej tabeli:

Cecha SQL Server Usługi Azure SQL Database i Azure SQL Managed Instance Magazyn danych sieci szkieletowej
Źródło danych Ścieżka lokalna, ścieżka sieciowa (UNC) lub usługa Azure Storage Azure Storage Azure Storage
Uwierzytelnianie źródłowe Uwierzytelnianie systemu Windows, sygnatura dostępu współdzielonego Microsoft Entra ID, token SAS, tożsamość zarządzana Microsoft Entra ID
Nieobsługiwane opcje * symbole wieloznaczne w ścieżce * symbole wieloznaczne w ścieżce DATA_SOURCE, FORMATFILE_DATA_SOURCE, ERRORFILE, ERRORFILE_DATA_SOURCE
Włączone opcje, ale bez efektu KEEPIDENTITY, FIRE_TRIGGERS, CHECK_CONSTRAINTS, TABLOCK, ORDER, ROWS_PER_BATCH, KILOBYTES_PER_BATCHi BATCHSIZE nie mają zastosowania. Nie będą zgłaszać błędu składniowego, ale nie będą miały żadnego efektu

Uwaga

Instrukcja BULK INSERT jest dostępna w wersji zapoznawczej w usłudze Fabric Data Warehouse.

database_name

Nazwa bazy danych, w której znajduje się określona tabela lub widok. Jeśli nie zostanie określony, database_name jest bieżącą bazą danych.

schema_name

Określa nazwę tabeli lub schematu widoku. schema_name jest opcjonalny, jeśli domyślny schemat dla użytkownika wykonującego operację importowania zbiorczego jest schematem określonej tabeli lub widoku. Jeśli schemat nie zostanie określony, a domyślny schemat użytkownika wykonującego operację importowania zbiorczego różni się od określonej tabeli lub widoku, program SQL Server zwraca komunikat o błędzie, a operacja importowania zbiorczego zostanie anulowana.

table_name

Określa nazwę tabeli lub widoku do zbiorczego importowania danych. Można używać tylko widoków, w których można odwoływać się do tej samej tabeli bazowej. Aby uzyskać więcej informacji na temat ograniczeń ładowania danych do widoków, zobacz INSERT (Transact-SQL).

FROM 'data_file'

Określa pełną ścieżkę pliku danych zawierającego dane do zaimportowania do określonej tabeli lub widoku. FUNKCJA BULK INSERT może importować dane z dysku lub usługi Azure Blob Storage (w tym z sieci, dyskietki, dysku twardego itd.).

SQL 
BULK INSERT bing_covid_19_data
FROM 'C:\\bing_covid-19_data\public\curated\covid-19\latest\bing_covid-19_data.csv';

data_file musi określić prawidłową ścieżkę z serwera, na którym działa program SQL Server. Jeśli data_file jest plikiem zdalnym, określ nazwę Universal Naming Convention (UNC). Nazwa UNC ma postać \\SystemName\ShareName\Path\FileName. Na przykład:

SQL 
BULK INSERT bing_covid_19_data
FROM '\\ShareX\bing_covid-19_data\public\curated\covid-19\latest\bing_covid-19_data.csv';

Usługa Azure SQL Database i Fabric Warehouse obsługują tylko odczyt z usługi Azure Blob Storage.

Począwszy od programu SQL Server 2017 (14.x), data_file może znajdować się w usłudze Azure Blob Storage. W takim przypadku należy również określić opcję data_source_name. Aby zapoznać się z przykładem, zobacz Importowanie danych z pliku w usłudze Azure Blob Storage.

Magazyn sieci szkieletowej obsługuje dwa różne style ścieżki do określania ścieżki źródłowej:

  • https://<storage account>.blob.core.windows.net/<container name>/<path to file>
  • abfss://<container name>@<storage account>.dfs.core.windows.net/<path to file>

Magazyn sieci szkieletowej obsługuje * symbole wieloznaczne, które mogą być zgodne z dowolnym znakiem w identyfikatorze URI i umożliwiają zdefiniowanie wzorca identyfikatora URI dla plików, które mają zostać zaimportowane. Na przykład:

SQL 
BULK INSERT bing_covid_19_data
FROM 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/*.csv';

BATCHSIZE = batch_size

Określa liczbę wierszy w partii. Każda partia jest kopiowana na serwer jako jedna transakcja. Jeśli to się nie powiedzie, program SQL Server zatwierdzi lub wycofa transakcję dla każdej partii. Domyślnie wszystkie dane w określonym pliku danych są jedną partią. Aby uzyskać informacje o zagadnieniach dotyczących wydajności, zobacz Zagadnienia dotyczące wydajności w dalszej części tego artykułu.

CHECK_CONSTRAINTS

Określa, że wszystkie ograniczenia w tabeli docelowej lub widoku muszą być sprawdzane podczas operacji importowania zbiorczego. Bez opcji CHECK_CONSTRAINTS wszelkie ograniczenia CHECK i FOREIGN KEY są ignorowane, a po operacji ograniczenie w tabeli jest oznaczone jako niezauważone.

Ograniczenia UNIKATOWE i KLUCZ PODSTAWOWY są zawsze wymuszane. Podczas importowania do kolumny znaków zdefiniowanej z ograniczeniem NOT NULL funkcja BULK INSERT wstawia pusty ciąg, gdy w pliku tekstowym nie ma żadnej wartości.

W pewnym momencie należy zbadać ograniczenia dotyczące całej tabeli. Jeśli tabela nie była pusta przed operacją importowania zbiorczego, koszt ponownego stosowania ograniczenia może przekroczyć koszt zastosowania ograniczeń CHECK do danych przyrostowych.

Sytuacja, w której mogą być wyłączone ograniczenia (zachowanie domyślne), to jeśli dane wejściowe zawierają wiersze naruszające ograniczenia. Po wyłączeniu ograniczeń CHECK można zaimportować dane, a następnie użyć instrukcji Transact-SQL, aby usunąć nieprawidłowe dane.

Uwaga

Opcja MAXERRORS nie ma zastosowania do sprawdzania ograniczeń.

CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }

Określa stronę kodów danych w pliku danych. FUNKCJA CODEPAGE jest odpowiednia tylko wtedy, gdy dane zawierają char, varcharlub kolumny tekstowe z wartościami znaków większymi niż 127 lub mniejsza niż 32. Aby zapoznać się z przykładem, zobacz Określanie strony kodowej.

FUNKCJA CODEPAGE nie jest obsługiwaną opcją w systemie Linux dla programu SQL Server 2017 (14.x). W przypadku programu SQL Server 2019 (15.x) tylko opcja "RAW" jest dozwolona dla funkcji CODEPAGE.

Należy określić nazwę sortowania dla każdej kolumny w pliku formatu .

Wartość STRONY KODOWEJ Opis
AKP Kolumny char, varcharlub tekst typu danych są konwertowane ze strony kodowej ANSI/Microsoft Windows (ISO 1252) na stronę kodową programu SQL Server.
OEM (ustawienie domyślne) Kolumny char, varcharlub tekst typu danych są konwertowane ze strony kodowej systemu OEM na stronę kodów programu SQL Server.
SUROWY Nie ma konwersji z jednej strony kodu na inną. RAW to najszybsza opcja.
code_page Określony numer strony kodowej, na przykład 850.

Wersje wcześniejsze niż sql Server 2016 (13.x) nie obsługują strony kodowej 65001 (kodowanie UTF-8).

DATAFILETYPE = { 'char' | 'native' | "widechar" | "widenative" }

Określa, że funkcja BULK INSERT wykonuje operację importowania przy użyciu określonej wartości typu pliku danych.

Wartość DATAFILETYPE Wszystkie dane reprezentowane w:
znaków (ustawienie domyślne) Format znaku.

Aby uzyskać więcej informacji, zobacz Używanie formatu znaków do importowania lub eksportowania danych (SQL Server).
natywne Natywne (bazy danych) typy danych. Utwórz plik danych natywnych przez zbiorcze importowanie danych z programu SQL Server przy użyciu narzędzia bcp.

Wartość natywna oferuje wyższą wydajność alternatywną dla wartości char. Format natywny jest zalecany w przypadku zbiorczego transferu danych między wieloma wystąpieniami programu SQL Server przy użyciu pliku danych, który nie zawiera żadnych znaków zestawu znaków rozszerzonych/dwubajtowych (DBCS).

Aby uzyskać więcej informacji, zobacz Używanie formatu natywnego do importowania lub eksportowania danych (SQL Server).
widechar Znaki Unicode.

Aby uzyskać więcej informacji, zobacz Używanie formatu znaków Unicode do importowania lub eksportowania danych (SQL Server).
Natywne (bazy danych) typy danych, z wyjątkiem char , varchari kolumn tekstowych, w których dane są przechowywane jako Unicode. Utwórz plik danych , importując zbiorczo dane z programu SQL Server przy użyciu narzędzia bcp.

Poszerna wartość oferuje wyższą wydajność alternatywną dla widechar. Jeśli plik danych zawiera znaki rozszerzone ANSI, określ rozszerzone.

Aby uzyskać więcej informacji, zobacz Używanie formatu natywnego Unicode do importowania lub eksportowania danych (SQL Server).

DATA_SOURCE = 'data_source_name'

Dotyczy: SQL Server 2017 (14.x) i Azure SQL Database.

Określa nazwane zewnętrzne źródło danych wskazujące lokalizację usługi Azure Blob Storage pliku, który zostanie zaimportowany. Zewnętrzne źródło danych musi zostać utworzone przy użyciu opcji TYPE = BLOB_STORAGE dodanej w programie SQL Server 2017 (14.x). Aby uzyskać więcej informacji, zobacz CREATE EXTERNAL DATA SOURCE. Aby zapoznać się z przykładem, zobacz Importowanie danych z pliku w usłudze Azure Blob Storage.

SQL 
CREATE EXTERNAL DATA SOURCE pandemicdatalake
WITH (LOCATION='https://pandemicdatalake.blob.core.windows.net/public/',TYPE=BLOB_STORAGE)
GO
BULK INSERT bing_covid_19_data
FROM 'curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv'
WITH (DATA_SOURCE='pandemicdatalake',FIRSTROW = 2,LASTROW = 100,FIELDTERMINATOR = ',');

ERRORFILE = 'error_file_path'

Określa plik używany do zbierania wierszy, które mają błędy formatowania i nie można przekonwertować na zestaw wierszy OLE DB. Te wiersze są kopiowane do tego pliku błędu z pliku danych "tak, jak to jest".

Plik błędu jest tworzony podczas wykonywania polecenia. Błąd występuje, jeśli plik już istnieje. Ponadto tworzony jest plik kontrolny z rozszerzeniem .ERROR.txt, który odwołuje się do każdego wiersza w pliku błędu i zapewnia diagnostykę błędów. Po skorygowaniu błędów można załadować dane.

Począwszy od programu SQL Server 2017 (14.x), error_file_path może znajdować się w usłudze Azure Blob Storage.

ERRORFILE_DATA_SOURCE = 'errorfile_data_source_name'

Dotyczy: SQL Server 2017 (14.x).

Określa nazwane zewnętrzne źródło danych wskazujące lokalizację usługi Azure Blob Storage pliku błędu, który będzie zawierać błędy znalezione podczas importowania. Zewnętrzne źródło danych musi zostać utworzone przy użyciu opcji TYPE = BLOB_STORAGE dodanej w programie SQL Server 2017 (14.x). Aby uzyskać więcej informacji, zobacz CREATE EXTERNAL DATA SOURCE.

FIRSTROW = first_row

Określa liczbę pierwszego wiersza do załadowania. Wartość domyślna to pierwszy wiersz w określonym pliku danych. FIRSTROW jest oparty na 1.

Atrybut FIRSTROW nie jest przeznaczony do pomijania nagłówków kolumn. Pomijanie nagłówków nie jest obsługiwane przez instrukcję BULK INSERT. Jeśli zdecydujesz się pominąć wiersze, aparat bazy danych programu SQL Server sprawdza tylko terminatory pól i nie weryfikuje danych w polach pominiętych wierszy.

FIRE_TRIGGERS

Określa, że wszystkie wyzwalacze wstawiania zdefiniowane w tabeli docelowej są wykonywane podczas operacji importowania zbiorczego. Jeśli wyzwalacze są zdefiniowane dla operacji INSERT w tabeli docelowej, są uruchamiane dla każdej ukończonej partii.

Jeśli nie określono FIRE_TRIGGERS, nie są wykonywane wyzwalacze wstawiania.

FORMATFILE_DATA_SOURCE = 'data_source_name'

Dotyczy: SQL Server 2017 (14.x).

Określa nazwane zewnętrzne źródło danych wskazujące lokalizację usługi Azure Blob Storage w pliku formatu, który zdefiniuje schemat zaimportowanych danych. Zewnętrzne źródło danych musi zostać utworzone przy użyciu opcji TYPE = BLOB_STORAGE dodanej w programie SQL Server 2017 (14.x). Aby uzyskać więcej informacji, zobacz CREATE EXTERNAL DATA SOURCE.

KEEPIDENTITY

Określa, że wartość tożsamości lub wartości w zaimportowanych plikach danych mają być używane dla kolumny tożsamości. Jeśli parametr KEEPIDENTITY nie jest określony, wartości tożsamości dla tej kolumny są weryfikowane, ale nie są importowane, a program SQL Server automatycznie przypisuje unikatowe wartości na podstawie wartości inicjacji i przyrostów określonych podczas tworzenia tabeli. Jeśli plik danych nie zawiera wartości kolumny tożsamości w tabeli lub widoku, użyj pliku formatu, aby określić, że kolumna tożsamości w tabeli lub widoku ma zostać pominięta podczas importowania danych; Program SQL Server automatycznie przypisuje unikatowe wartości dla kolumny. Aby uzyskać więcej informacji, zobacz DBCC CHECKIDENT (Transact-SQL).

Aby uzyskać więcej informacji, zobacz artykuł dotyczący zachowywania wartości identyfikacji, zobacz Zachowaj wartości tożsamości podczas zbiorczego importowania danych (SQL Server).

KEEPNULLS

Określa, że puste kolumny powinny zachować wartość null podczas operacji importowania zbiorczego, zamiast mieć jakiekolwiek wartości domyślne dla wstawionych kolumn. Aby uzyskać więcej informacji, zobacz Zachowaj wartości null lub Użyj wartości domyślnych podczas importowania zbiorczego (SQL Server).

KILOBYTES_PER_BATCH = kilobytes_per_batch

Określa przybliżoną liczbę kilobajtów (KB) danych na partię jako kilobytes_per_batch. Domyślnie KILOBYTES_PER_BATCH jest nieznany. Aby uzyskać informacje o zagadnieniach dotyczących wydajności, zobacz Zagadnienia dotyczące wydajności w dalszej części tego artykułu.

LASTROW = last_row

Określa liczbę ostatniego wiersza do załadowania. Wartość domyślna to 0, która wskazuje ostatni wiersz w określonym pliku danych.

MAXERRORS = max_errors

Określa maksymalną liczbę błędów składni dozwolonych w danych przed anulowaniem operacji zbiorczego importowania. Każdy wiersz, którego nie można zaimportować przez operację importowania zbiorczego, jest ignorowany i liowany jako jeden błąd. Jeśli max_errors nie zostanie określona, wartość domyślna to 10.

Opcja MAX_ERRORS nie ma zastosowania do kontroli ograniczeń ani konwersji pieniędzy i typów danych bigint.

ORDER ( { kolumna [ ASC | DESC ] } [ ,... n ] )

Określa sposób sortowania danych w pliku danych. Wydajność importowania zbiorczego jest lepsza, jeśli importowane dane są sortowane zgodnie z indeksem klastrowanym w tabeli, jeśli istnieją. Jeśli plik danych jest sortowany w kolejności innej niż kolejność klastrowanego klucza indeksu lub jeśli w tabeli nie ma indeksu klastrowanego, klauzula ORDER jest ignorowana. Podane nazwy kolumn muszą być prawidłowymi nazwami kolumn w tabeli docelowej. Domyślnie operacja wstawiania zbiorczego zakłada, że plik danych jest nieurządzony. W przypadku zoptymalizowanego importu zbiorczego program SQL Server sprawdza również, czy zaimportowane dane są sortowane.

n jest symbolem zastępczym wskazującym, że można określić wiele kolumn.

ROWS_PER_BATCH = rows_per_batch

Wskazuje przybliżoną liczbę wierszy danych w pliku danych.

Domyślnie wszystkie dane w pliku danych są wysyłane do serwera jako pojedyncza transakcja, a liczba wierszy w partii jest nieznana optymalizatorowi zapytań. Jeśli określisz ROWS_PER_BATCH (z wartością > 0), serwer użyje tej wartości do zoptymalizowania operacji importowania zbiorczego. Wartość określona dla ROWS_PER_BATCH powinna być w przybliżeniu taka sama jak rzeczywista liczba wierszy. Aby uzyskać informacje o zagadnieniach dotyczących wydajności, zobacz Zagadnienia dotyczące wydajności w dalszej części tego artykułu.

TABLOCK

Określa, że blokada na poziomie tabeli jest uzyskiwana przez czas trwania operacji importowania zbiorczego. Tabelę można załadować współbieżnie przez wielu klientów, jeśli tabela nie ma indeksów, a parametr TABLOCK jest określony. Domyślnie zachowanie blokowania jest określane przez opcję tabeli blokada tabeli podczas ładowania zbiorczego. Utrzymywanie blokady na czas trwania operacji importowania zbiorczego zmniejsza rywalizację o blokadę w tabeli, w niektórych przypadkach może znacznie poprawić wydajność. Aby uzyskać informacje o zagadnieniach dotyczących wydajności, zobacz Zagadnienia dotyczące wydajności w dalszej części tego artykułu.

W przypadku indeksu magazynu kolumn zachowanie blokowania jest inne, ponieważ jest wewnętrznie podzielone na wiele zestawów wierszy. Każdy wątek ładuje dane wyłącznie do każdego zestawu wierszy, stosując blokadę X w zestawie wierszy, umożliwiając równoległe ładowanie danych z równoczesnymi sesjami ładowania danych. Użycie opcji TABLOCK spowoduje, że wątek zostanie zablokowany X w tabeli (w przeciwieństwie do blokady BU dla tradycyjnych zestawów wierszy), co uniemożliwi innym współbieżnym wątkom ładowanie danych współbieżnie.

Opcje formatu pliku wejściowego

FORMAT = "CSV"

Dotyczy: SQL Server 2017 (14.x).

Określa plik wartości rozdzielonych przecinkami zgodny ze standardem RFC 4180.

SQL 
BULK INSERT Sales.Orders
FROM '\\SystemX\DiskZ\Sales\data\orders.csv'
WITH ( FORMAT = 'CSV');

FIELDQUOTE = 'field_quote'

Dotyczy: SQL Server 2017 (14.x).

Określa znak, który będzie używany jako znak cudzysłowu w pliku CSV. Jeśli nie zostanie określony, znak cudzysłowu (") będzie używany jako znak cudzysłowu zdefiniowany w standardzie RFC 4180.

FORMATFILE = 'format_file_path'

Określa pełną ścieżkę pliku formatu. Plik formatu opisuje plik danych zawierający przechowywane odpowiedzi utworzone przy użyciu narzędzia bcp w tej samej tabeli lub widoku. Plik formatu powinien być używany, jeśli:

  • Plik danych zawiera kolumny większe lub mniejsze niż tabela lub widok.
  • Kolumny są w innej kolejności.
  • Ograniczniki kolumn różnią się.
  • Istnieją inne zmiany w formacie danych. Pliki formatu są zwykle tworzone przy użyciu narzędzia bcp i modyfikowane za pomocą edytora tekstów zgodnie z potrzebami. Aby uzyskać więcej informacji, zobacz bcp Utility i Create a format file.

Począwszy od programu SQL Server 2017 (14.x) i usługi Azure SQL Database, format_file_path mogą znajdować się w usłudze Azure Blob Storage.

FIELDTERMINATOR = 'field_terminator'

Określa terminator pola, który ma być używany dla char i widechar plików danych. Domyślny terminator pola to \t (znak tabulacji). Aby uzyskać więcej informacji, zobacz Określanie pól i terminatorów wierszy (SQL Server).

ROWTERMINATOR = 'row_terminator'

Określa terminator wierszy, który ma być używany dla char i widechar plików danych. Domyślny terminator wierszy to \r\n (znak nowego wiersza). Aby uzyskać więcej informacji, zobacz Określanie pól i terminatorów wierszy (SQL Server).

Zgodność

Funkcja BULK INSERT wymusza ścisłą walidację danych i sprawdzanie danych odczytanych z pliku, co może spowodować niepowodzenie istniejących skryptów po wykonaniu nieprawidłowych danych. Na przykład funkcja BULK INSERT sprawdza, czy:

  • Natywne reprezentacje zmiennoprzecinkowych lub rzeczywistych typów danych są prawidłowe.
  • Dane Unicode mają długość parzystową.

Typy danych

Konwersje typu danych ciąg-dziesiętny

Konwersje typu danych typu ciąg-dziesiętny używane w funkcji BULK INSERT są zgodne z tymi samymi regułami co funkcja Transact-SQL CONVERT, która odrzuca ciągi reprezentujące wartości liczbowe, które używają notacji naukowej. W związku z tym funkcja BULK INSERT traktuje takie ciągi jak nieprawidłowe wartości i zgłasza błędy konwersji.

Aby obejść to zachowanie, użyj pliku formatu, aby zbiorczo zaimportować notację naukową zmiennoprzecinkowe danych do kolumny dziesiętnej. W pliku formatu jawnie opisz kolumnę jako rzeczywistych lub danych zmiennoprzecinkowych. Aby uzyskać więcej informacji na temat tych typów danych, zobacz float i real (Transact-SQL).

Pliki formatu reprezentują rzeczywiste dane jako typ danych SQLFLT4 i dane zmiennoprzecinkowe jako typ danych SQLFLT8. Aby uzyskać informacje na temat plików w formacie innych niż XML, zobacz Określanie typu magazynu plików przy użyciu programu bcp (SQL Server).

Przykład importowania wartości liczbowej korzystającej z notacji naukowej

W tym przykładzie użyto następującej tabeli w bazie danych bulktest:

SQL 
CREATE TABLE dbo.t_float(c1 FLOAT, c2 DECIMAL (5,4));

Użytkownik chce zbiorczo zaimportować dane do tabeli t_float. Plik danych C:\t_float-c.dat zawiera notację naukową danych zmiennoprzecinkowych; na przykład:

input 
8.0000000000000002E-2 8.0000000000000002E-2

Podczas kopiowania tego przykładu należy pamiętać o różnych edytorach tekstów i kodowaniu, które zapisują znaki tabulatorów (\t) jako spacje. W dalszej części tego przykładu oczekiwano znaku tabulatora.

Jednak funkcja BULK INSERT nie może zaimportować tych danych bezpośrednio do t_float, ponieważ jej druga kolumna c2używa typu danych decimal. W związku z tym wymagany jest plik formatu. Plik formatu musi mapować notację naukową danych zmiennoprzecinkowych do formatu dziesiętnego kolumny c2.

Następujący plik formatu używa typu danych SQLFLT8 do mapowania drugiego pola danych na drugą kolumnę:

XML 
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30"/> </RECORD> <ROW>
<COLUMN SOURCE="1" NAME="c1" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="2" NAME="c2" xsi:type="SQLFLT8"/> </ROW> </BCPFORMAT>

Aby użyć tego pliku formatu (przy użyciu nazwy pliku C:\t_floatformat-c-xml.xml) w celu zaimportowania danych testowych do tabeli testowej, wydaj następującą instrukcję Transact-SQL:

SQL 
BULK INSERT bulktest.dbo.t_float
FROM 'C:\t_float-c.dat' WITH (FORMATFILE = 'C:\t_floatformat-c-xml.xml');

Ważne

Usługa Azure SQL Database obsługuje tylko odczyt z usługi Azure Blob Storage.

Typy danych do zbiorczego eksportowania lub importowania dokumentów SQLXML

Aby zbiorczo wyeksportować lub zaimportować dane SQLXML, użyj jednego z następujących typów danych w pliku formatu:

Typ danych Efekt
SQLCHAR lub SQLVARCHAR Dane są wysyłane na stronie kodu klienta lub na stronie kodowej implikowanej przez sortowanie). Efekt jest taki sam, jak określanie parametru DATAFILETYPE = "char" bez określania pliku formatu.
SQLNCHAR lub SQLNVARCHAR Dane są wysyłane jako Unicode. Efekt jest taki sam jak określanie DATAFILETYPE = 'widechar' bez określania pliku formatu.
SQLBINARY lub SQLVARBIN Dane są wysyłane bez żadnej konwersji.

Uwagi

Aby uzyskać porównanie instrukcji BULK INSERT, instrukcji INSERT ... SELECT * FROM OPENROWSET(BULK...) i polecenia bcp, zobacz zbiorcze importowanie i eksportowanie danych.

Aby uzyskać informacje na temat przygotowywania danych do importowania zbiorczego, zobacz Prepare Data for Bulk Export or Import.

Instrukcję BULK INSERT można wykonać w ramach transakcji zdefiniowanej przez użytkownika w celu zaimportowania danych do tabeli lub widoku. Opcjonalnie, aby użyć wielu dopasowań do zbiorczego importowania danych, transakcja może określić klauzulę BATCHSIZE w instrukcji BULK INSERT. Jeśli transakcja z wieloma partiami zostanie wycofana, każda partia, która została wysłana do programu SQL Server, zostanie wycofana.

Współdziałanie

Importowanie danych z pliku CSV

Począwszy od programu SQL Server 2017 (14.x), funkcja BULK INSERT obsługuje format CSV, podobnie jak usługa Azure SQL Database.

Przed programem SQL Server 2017 (14.x) pliki wartości rozdzielanych przecinkami (CSV) nie są obsługiwane przez operacje importowania zbiorczego programu SQL Server. Jednak w niektórych przypadkach plik CSV może służyć jako plik danych do zbiorczego importowania danych do programu SQL Server. Aby uzyskać informacje o wymaganiach dotyczących importowania danych z pliku danych CSV, zobacz Prepare Data for Bulk Export or Import (SQL Server).

Zachowanie dziennika

Aby uzyskać informacje o tym, kiedy operacje wstawiania wierszy wykonywane przez zbiorcze importowanie do programu SQL Server są rejestrowane w dzienniku transakcji, zobacz Wymagania wstępne dotyczące minimalnego rejestrowania w importowaniu zbiorczym. Minimalne rejestrowanie nie jest obsługiwane w usłudze Azure SQL Database.

Ograniczenia

W przypadku korzystania z pliku formatu z funkcją BULK INSERT można określić tylko 1024 pola. Jest to takie samo, jak maksymalna liczba kolumn dozwolonych w tabeli. Jeśli używasz pliku formatu z funkcją BULK INSERT z plikiem danych zawierającym więcej niż 1024 pola, funkcja BULK INSERT generuje błąd 4822. Narzędzie bcp nie ma tego ograniczenia, dlatego w przypadku plików danych zawierających więcej niż 1024 pól użyj funkcji BULK INSERT bez pliku formatu lub użyj polecenia bcp.

Zagadnienia dotyczące wydajności

Jeśli liczba stron do opróżnienia w pojedynczej partii przekroczy próg wewnętrzny, może wystąpić pełne skanowanie puli w celu zidentyfikowania stron do opróżnienia po zatwierdzeniu partii. To pełne skanowanie może zaszkodzić wydajności importowania zbiorczego. Prawdopodobny przypadek przekroczenia progu wewnętrznego występuje, gdy duża pula jest połączona z powolnym podsystemem we/wy. Aby uniknąć przepełnienia buforu na dużych maszynach, nie używaj wskazówki TABLOCK (która spowoduje usunięcie optymalizacji zbiorczych) lub użyj mniejszego rozmiaru partii (co zachowuje optymalizacje zbiorcze).

Należy przetestować różne rozmiary partii z obciążeniem danych, aby dowiedzieć się, co działa najlepiej. Należy pamiętać, że rozmiar partii ma częściowe konsekwencje wycofania. Jeśli proces zakończy się niepowodzeniem i przed ponownym użyciem instrukcji BULK INSERT, może być konieczne wykonanie dodatkowej pracy ręcznej w celu usunięcia części wierszy, które zostały pomyślnie wstawione, zanim wystąpi błąd.

W przypadku usługi Azure SQL Database rozważ tymczasowe zwiększenie poziomu wydajności bazy danych lub wystąpienia przed zaimportowaniem, jeśli importujesz dużą ilość danych.

Bezpieczeństwo

Delegowanie konta zabezpieczeń (personifikacja)

Jeśli użytkownik używa identyfikatora logowania programu SQL Server, używany jest profil zabezpieczeń konta procesu programu SQL Server. Nie można uwierzytelnić logowania przy użyciu uwierzytelniania programu SQL Server poza aparatem bazy danych. W związku z tym po zainicjowaniu polecenia BULK INSERT przy użyciu uwierzytelniania programu SQL Server połączenie z danymi odbywa się przy użyciu kontekstu zabezpieczeń konta procesu programu SQL Server (konta używanego przez usługę aparatu bazy danych programu SQL Server).

Aby pomyślnie odczytać dane źródłowe, musisz przyznać konto używane przez aparat bazy danych programu SQL Server, dostęp do danych źródłowych. Natomiast jeśli użytkownik programu SQL Server loguje się przy użyciu uwierzytelniania systemu Windows, użytkownik może odczytywać tylko te pliki, do których można uzyskać dostęp przy użyciu konta użytkownika, niezależnie od profilu zabezpieczeń procesu programu SQL Server.

Podczas wykonywania instrukcji BULK INSERT przy użyciu sqlcmd lub osql, z jednego komputera, wstawiania danych do programu SQL Server na drugim komputerze i określania data_file na trzecim komputerze przy użyciu ścieżki UNC, może zostać wyświetlony błąd 4861.

Aby rozwiązać ten problem, użyj uwierzytelniania programu SQL Server i określ identyfikator logowania programu SQL Server, który używa profilu zabezpieczeń konta procesu programu SQL Server, lub skonfiguruj system Windows w celu włączenia delegowania konta zabezpieczeń. Aby uzyskać informacje na temat włączania zaufanego konta użytkownika na potrzeby delegowania, zobacz Pomoc systemu Windows.

Aby uzyskać więcej informacji na temat tego i innych zagadnień dotyczących zabezpieczeń dotyczących używania funkcji BULK INSERT, zobacz Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) (SQL Server).

Podczas importowania z usługi Azure Blob Storage i danych nie są publiczne (dostęp anonimowy), utwórz POŚWIADCZENIA o zakresie bazy danych na podstawie klucza SAS zaszyfrowanego przy użyciu klucza głównegoMASTER, a następnie utwórz zewnętrzne źródło bazy danych do użycia w poleceniu BULK INSERT.

Alternatywnie utwórz DATABASE SCOPED CREDENTIAL na podstawie MANAGED IDENTITY autoryzowania żądań dostępu do danych na kontach magazynu niepublikowego. W przypadku korzystania z MANAGED IDENTITYusługa Azure Storage musi udzielić uprawnień tożsamości zarządzanej wystąpienia przez dodanie współautora danych obiektu blob usługi Storage wbudowaną rolę kontroli dostępu opartej na rolach (RBAC) platformy Azure, która zapewnia dostęp do odczytu/zapisu tożsamości zarządzanej dla niezbędnych kontenerów usługi Azure Blob Storage. Usługa Azure SQL Managed Instance ma przypisaną przez system tożsamość zarządzaną i może również mieć co najmniej jedną tożsamość zarządzaną przypisaną przez użytkownika. Do autoryzowania żądań można użyć tożsamości zarządzanych przypisanych przez system lub tożsamości zarządzanych przypisanych przez użytkownika. W przypadku autoryzacji zostanie użyta default tożsamość wystąpienia zarządzanego (podstawowa tożsamość zarządzana przypisana przez użytkownika lub tożsamość zarządzana przypisana przez system, jeśli nie określono tożsamości zarządzanej przypisanej przez użytkownika). Aby zapoznać się z przykładem, zobacz Importowanie danych z pliku w usłudze Azure Blob Storage.

Ważne

Tożsamość zarządzana ma zastosowanie tylko do usługi Azure SQL. Program SQL Server nie obsługuje tożsamości zarządzanej.

Uprawnienia

Wymaga uprawnień INSERT i ADMINISTER BULK OPERATIONS. W usłudze Azure SQL Database wymagane są uprawnienia INSERT and ADMINISTER DATABASE BULK OPERATIONS (WSTAWIANIE i ADMINISTROWANIE OPERACJAMI ZBIORCZYM BAZY DANYCH). Administrowanie uprawnieniami OPERACJI ZBIORCZYCH lub rola bulkadmin nie jest obsługiwana dla programu SQL Server w systemie Linux. Tylko sysadmin może wykonywać operacje wstawiania zbiorczego dla programu SQL Server w systemie Linux.

Ponadto uprawnienie ALTER TABLE jest wymagane, jeśli spełniony jest co najmniej jeden z następujących warunków:

  • Istnieją ograniczenia i nie określono opcji CHECK_CONSTRAINTS.

    Wyłączenie ograniczeń jest zachowaniem domyślnym. Aby jawnie sprawdzić ograniczenia, użyj opcji CHECK_CONSTRAINTS.

  • Wyzwalacze istnieją i nie określono opcji FIRE_TRIGGER.

    Domyślnie wyzwalacze nie są wyzwalane. Aby jawnie uruchamiać wyzwalacze, użyj opcji FIRE_TRIGGER.

  • Opcja KEEPIDENTITY służy do importowania wartości tożsamości z pliku danych.

Przykłady

A. Importowanie danych z pliku przy użyciu potoków

Poniższy przykład importuje szczegółowe informacje o kolejności do tabeli AdventureWorks2022.Sales.SalesOrderDetail z określonego pliku danych przy użyciu potoku (|) jako terminatora pola i |\n jako terminator wiersza.

SQL 
BULK INSERT AdventureWorks2022.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH
      (
         FIELDTERMINATOR = ' |'
         , ROWTERMINATOR = ' |\n'
      );

Ważne

Usługa Azure SQL Database obsługuje tylko odczyt z usługi Azure Blob Storage.

B. Użyj argumentu FIRE_TRIGGERS

W poniższym przykładzie określono argument FIRE_TRIGGERS.

SQL 
BULK INSERT AdventureWorks2022.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH
     (
         FIELDTERMINATOR = ' |'
         , ROWTERMINATOR = ':\n'
         , FIRE_TRIGGERS
      );

Ważne

Usługa Azure SQL Database obsługuje tylko odczyt z usługi Azure Blob Storage.

C. Używanie kanału informacyjnego wiersza jako terminatora wierszy

W poniższym przykładzie importuje plik, który używa kanału informacyjnego wiersza jako terminatora wiersza, takiego jak dane wyjściowe systemu UNIX:

SQL 
DECLARE @bulk_cmd VARCHAR(1000);
SET @bulk_cmd = 'BULK INSERT AdventureWorks2022.Sales.SalesOrderDetail
FROM ''<drive>:\<path>\<filename>''
WITH (ROWTERMINATOR = '''+CHAR(10)+''')';
EXEC(@bulk_cmd);

Uwaga

Ze względu na sposób, w jaki system Microsoft Windows traktuje pliki tekstowe, \n jest automatycznie zastępowany \r\n.

Ważne

Usługa Azure SQL Database obsługuje tylko odczyt z usługi Azure Blob Storage.

D. Określanie strony kodowej

W poniższym przykładzie pokazano, jak określić stronę kodową.

SQL 
BULK INSERT MyTable
FROM 'D:\data.csv'
WITH
( CODEPAGE = '65001'
   , DATAFILETYPE = 'char'
   , FIELDTERMINATOR = ','
);

Ważne

Usługa Azure SQL Database obsługuje tylko odczyt z usługi Azure Blob Storage.

E. Importowanie danych z pliku CSV

W poniższym przykładzie pokazano, jak określić plik CSV, pomijając nagłówek (pierwszy wiersz), używając ; jako terminatora pól i 0x0a jako terminator wiersza:

SQL 
BULK INSERT Sales.Invoices
FROM '\\share\invoices\inv-2016-07-25.csv'
WITH (FORMAT = 'CSV'
      , FIRSTROW = 2
      , FIELDQUOTE = '\'
      , FIELDTERMINATOR = ';'
      , ROWTERMINATOR = '0x0a');

W poniższym przykładzie pokazano, jak określić plik CSV w formacie UTF-8 (przy użyciu CODEPAGE65001), pomijając nagłówek (pierwszy wiersz), używając ; jako terminatora pola i 0x0a jako terminator wiersza:

SQL 
BULK INSERT Sales.Invoices
FROM '\\share\invoices\inv-2016-07-25.csv'
WITH ( CODEPAGE = '65001'
      , FORMAT = 'CSV'
      , FIRSTROW = 2
      , FIELDQUOTE = '\'
      , FIELDTERMINATOR = ';'
      , ROWTERMINATOR = '0x0a');

Ważne

Usługa Azure SQL Database obsługuje tylko odczyt z usługi Azure Blob Storage.

F. Importowanie danych z pliku w usłudze Azure Blob Storage

W poniższym przykładzie pokazano, jak załadować dane z pliku CSV w lokalizacji usługi Azure Blob Storage, w której utworzono sygnaturę dostępu współdzielonego (SAS). Lokalizacja usługi Azure Blob Storage jest skonfigurowana jako zewnętrzne źródło danych, które wymaga poświadczeń o zakresie bazy danych przy użyciu klucza SAS szyfrowanego przy użyciu klucza głównego w bazie danych użytkownika.

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

W poniższym przykładzie pokazano, jak za pomocą polecenia BULK INSERT załadować dane z pliku CSV w lokalizacji magazynu obiektów blob platformy Azure przy użyciu tożsamości zarządzanej. Lokalizacja usługi Azure Blob Storage jest skonfigurowana jako zewnętrzne źródło danych.

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

Ważne

Tożsamość zarządzana ma zastosowanie tylko do usługi Azure SQL. Program SQL Server nie obsługuje tożsamości zarządzanej.

Ważne

Usługa Azure SQL obsługuje tylko odczyt z usługi Azure Blob Storage.

G. Importowanie danych z pliku w usłudze Azure Blob Storage i określanie pliku błędu

W poniższym przykładzie pokazano, jak załadować dane z pliku CSV w lokalizacji usługi Azure Blob Storage, która została skonfigurowana jako zewnętrzne źródło danych, a także określić plik błędu. Będzie potrzebne poświadczenie o zakresie bazy danych przy użyciu sygnatury dostępu współdzielonego. W przypadku uruchamiania w usłudze Azure SQL Database opcja ERRORFILE powinna być dołączona przez ERRORFILE_DATA_SOURCE w przeciwnym razie importowanie może zakończyć się niepowodzeniem z powodu błędu uprawnień. Plik określony w pliku ERRORFILE nie powinien istnieć w kontenerze.

SQL 
BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (
         DATA_SOURCE = 'MyAzureInvoices'
         , FORMAT = 'CSV'
         , ERRORFILE = 'MyErrorFile'
         , ERRORFILE_DATA_SOURCE = 'MyAzureInvoices');

Aby uzyskać pełne BULK INSERT przykłady, w tym konfigurowanie poświadczeń i zewnętrznego źródła danych, zobacz Przykłady zbiorczego dostępu do danych w usłudze Azure Blob Storage.

Więcej przykładów

Inne przykłady BULK INSERT znajdują się w następujących artykułach:

Zobacz też