Używanie pliku formatu do zbiorczego importowania danych (SQL Server)

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

W tym artykule przedstawiono użycie pliku formatu w operacjach importowania zbiorczego. Plik formatu mapuje pola pliku danych na kolumny tabeli. Aby uzyskać dodatkowe informacje, przejrzyj Create a Format File (SQL Server) (Tworzenie pliku formatu (SQL Server).

Przed rozpoczęciem

• Aby plik formatu działał z plikiem danych znaków Unicode, wszystkie pola wejściowe muszą być ciągami tekstowymi Unicode (czyli ciągami unicode o stałym rozmiarze lub ciągami Unicode zakończonymi znakami).
• Aby wyeksportować lub zaimportować dane SQLXML, użyj jednego z następujących typów danych w pliku formatowym:
  • SQLCHAR lub SQLVARCHAR (dane są wysyłane na stronie kodu klienta lub na stronie kodowej implikowanej przez sortowanie)
  • SQLNCHAR lub SQLNVARCHAR (dane są wysyłane jako Unicode)
  • SQLBINARY lub SQLVARBIN (dane są wysyłane bez żadnej konwersji).
• Usługi Azure SQL Database i Azure Synapse Analytics obsługują tylko bcp. Aby uzyskać dodatkowe informacje, zobacz:
  • ładowanie danych do usługi Azure Synapse Analytics
  • ładowanie danych z programu SQL Server do usługi Azure Synapse Analytics (plików prostych)
  • Przenieś swoje dane
• Ta składnia, w tym wstawianie zbiorcze, nie jest obsługiwana w usłudze Azure Synapse Analytics. W usłudze Azure Synapse Analytics i innych integracjach platformy baz danych w chmurze, przenieś dane za pomocą instrukcji COPY w usłudze Azure Data Factorylub za pomocą instrukcji języka T-SQL takich jak COPY INTO i PolyBase.

Przykładowe warunki testu

Przykłady plików formatu w tym temacie są oparte na tabeli i pliku danych zdefiniowanym poniżej.

Przykładowa tabela

Poniższy skrypt tworzy testową bazę danych i tabelę o nazwie myFirstImport. Wykonaj polecenie Transact-SQL w programie Microsoft SQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.MyFirstImport (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30),
   BirthDate Date
   );

Przykładowy plik danych

Za pomocą Notatnika utwórz pusty plik D:\BCP\myFirstImport.bcp i wstaw następujące dane:

1,Anthony,Grosse,1980-02-23
2,Alica,Fatnowna,1963-11-14
3,Stella,Rosenhain,1992-03-02

Alternatywnie możesz wykonać następujący skrypt programu PowerShell, aby utworzyć i wypełnić plik danych:

Clear-Host
# revise directory as desired

$dir = 'D:\BCP\';

$bcpFile = Join-Path -Path $dir -ChildPath 'MyFirstImport.bcp';

# Confirm directory exists

IF ((Test-Path -Path $dir) -eq 0)
{
    Write-Host "The path $dir does not exist; please create or modify the directory.";
    RETURN;
};

# Clear content, will error if file does not exist, can be ignored

Clear-Content -Path $bcpFile -ErrorAction SilentlyContinue;

# Add data

Add-Content -Path $bcpFile -Value '1,Anthony,Grosse,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,1963-11-14';
Add-Content -Path $bcpFile -Value '3,Stella,Rosenhain,1992-03-02';

# Review content

Get-Content -Path $bcpFile;
Notepad.exe $bcpfile;

Tworzenie plików formatu

Program SQL Server obsługuje dwa typy plików formatu: format inny niż XML i format XML. Format inny niż XML jest oryginalnym formatem obsługiwanym przez wcześniejsze wersje programu SQL Server.

Tworzenie pliku w formacie innego niż XML

Przejrzyj Pliki w formacie innym niż XML (SQL Server), aby uzyskać szczegółowe informacje. Następujące polecenie użyje narzędzia bcp do wygenerowania pliku formatu innego niż xml, myFirstImport.fmt, na podstawie schematu myFirstImport. Aby użyć polecenia bcp do utworzenia pliku formatu, określ format argumentu i użyj nul zamiast ścieżki pliku danych. Opcja formatowania wymaga również opcji -f. Ponadto w tym przykładzie kwalifikator c służy do określenia danych znakowych, t, służy do określenia przecinka jako terminatora pola, a T służy do określenia zaufanego połączenia przy użyciu zintegrowanych zabezpieczeń. W wierszu polecenia wprowadź następujące polecenie:

bcp TestDatabase.dbo.myFirstImport format nul -c -f D:\BCP\myFirstImport.fmt -t, -T

REM Review file
Notepad D:\BCP\myFirstImport.fmt

Plik formatu innego niż XML D:\BCP\myFirstImport.fmt powinien wyglądać następująco:

13.0
4
1       SQLCHAR             0       7       ","      1     PersonID               ""
2       SQLCHAR             0       25      ","      2     FirstName              SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             0       30      ","      3     LastName               SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR             0       11      "\r\n"   4     BirthDate              ""

Ważny

Upewnij się, że plik w formacie innym niż XML kończy się powrotem karetki\znakiem nowej linii. W przeciwnym razie prawdopodobnie zostanie wyświetlony następujący komunikat o błędzie:

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format file

Tworzenie pliku formatu XML

Przejrzyj pliki formatu XML (SQL Server) dla szczegółowych informacji. Następujące polecenie użyje narzędzia bcp do utworzenia pliku formatu XML, myFirstImport.xml, na podstawie schematu myFirstImport. Aby użyć polecenia bcp do utworzenia pliku formatu, określ argument formatu i użyj nul zamiast ścieżki pliku danych. Opcja formatowania zawsze wymaga opcji -f i aby utworzyć plik formatu XML, należy również określić opcję -x. Ponadto w tym przykładzie, kwalifikator c jest używany do wskazania danych znakowych, t, służy do określenia przecinka jako terminatora pola, a T określa zaufane połączenie za pomocą zintegrowanych zabezpieczeń. W wierszu polecenia wprowadź następujące polecenie:

bcp TestDatabase.dbo.myFirstImport format nul -c -x -f D:\BCP\myFirstImport.xml -t, -T

REM Review file
Notepad D:\BCP\myFirstImport.xml

Plik formatu XML D:\BCP\myFirstImport.xml powinien wyglądać następująco:

<?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="," MAX_LENGTH="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="11"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="FirstName" xsi:type="SQLVARCHAR"/>
  <COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARCHAR"/>
  <COLUMN SOURCE="4" NAME="BirthDate" xsi:type="SQLDATE"/>
</ROW>
</BCPFORMAT>

Używanie pliku formatu do zbiorczego importowania danych

W poniższych przykładach użyto bazy danych, pliku danych i plików formatowania utworzonych powyżej.

Użyj bcp i pliku w formacie nienależącym do XML

W wierszu polecenia wprowadź następujące polecenie:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.MyFirstImport;"

REM Import data
bcp TestDatabase.dbo.myFirstImport IN D:\BCP\myFirstImport.bcp -f D:\BCP\myFirstImport.fmt -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.MyFirstImport"

Użyj bcp i pliku w formacie XML

W wierszu polecenia wprowadź następujące polecenie:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.MyFirstImport;"

REM Import data
bcp TestDatabase.dbo.myFirstImport IN D:\BCP\myFirstImport.bcp -f D:\BCP\myFirstImport.xml -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.MyFirstImport;"

Użyj BULK INSERT i pliku w formacie innego niż XML

Uruchom następujące Transact-SQL w programie Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myFirstImport; -- (for testing)
BULK INSERT dbo.myFirstImport  
   FROM 'D:\BCP\myFirstImport.bcp'  
   WITH (FORMATFILE = 'D:\BCP\myFirstImport.fmt');  
GO

-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;

Użycie BULK INSERT i pliku formatu XML

Wykonaj następujące polecenie Transact-SQL w Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myFirstImport; -- (for testing)
BULK INSERT dbo.myFirstImport  
   FROM 'D:\BCP\myFirstImport.bcp'  
   WITH (FORMATFILE = 'D:\BCP\myFirstImport.xml');  
GO

-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;

Użyj OPENROWSET(BULK...) oraz pliku formatu innego niż XML

Wykonaj następujące Transact-SQL w programie Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE myFirstImport; -- (for testing)
INSERT INTO dbo.myFirstImport
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myFirstImport.bcp',
        FORMATFILE = 'D:\BCP\myFirstImport.fmt'
        ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;

Użyj OPENROWSET(BULK...) i pliku formatu XML

Wykonaj następujące Transact-SQL w programie Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myFirstImport; -- (for testing)
INSERT INTO dbo.myFirstImport  
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myFirstImport.bcp',
        FORMATFILE = 'D:\BCP\myFirstImport.xml'  
       ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;

Więcej przykładów

tworzenie pliku formatu (SQL Server)
użyć pliku formatu, aby pominąć kolumny tabeli (SQL Server)
użyć pliku formatu, aby pominąć pole danych (SQL Server)
Użyj pliku formatu do mapowania kolumn tabeli na pola Data-File ( SQL Server)

Następne kroki

• narzędzia bcp
• BULK INSERT (Transact-SQL)
• OPENROWSET (Transact-SQL)
• plików formatowania innego niż XML (SQL Server)
• Pliki formatu XML (SQL Server)
• pl-PL: Formaty plików do importowania lub eksportowania danych (SQL Server)