Używanie pliku formatu do mapowania kolumn tabeli na pola pliku danych (SQL Server)

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

Plik danych może zawierać pola ułożone w innej kolejności niż odpowiednie kolumny w tabeli. W tym artykule przedstawiono zarówno pliki formatu innego niż XML, jak i XML, które są modyfikowane w celu uwzględnienia pliku danych, którego pola są rozmieszczone w innej kolejności niż kolumny tabeli. Zmodyfikowany plik formatu mapuje pola danych na odpowiadające im kolumny tabeli. Aby uzyskać dodatkowe informacje, przejrzyj Create a format file with bcp (SQL Server) (Tworzenie pliku formatu przy użyciu programu bcp (SQL Server).

Plik formatu innego niż XML lub XML może służyć do zbiorczego importowania pliku danych do tabeli przy użyciu narzędzia bcp polecenia BULK INSERT instrukcji lub INSERT ... SELECT * FROM INSTRUKCJI OPENROWSET. Aby uzyskać więcej informacji, zobacz Używanie pliku formatu do zbiorczego importowania danych (SQL Server).

Notatka

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 integracji platformy bazy danych w chmurze wykonaj przenoszenie danych za pośrednictwem 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 zmodyfikowanych plików formatu w tym artykule są oparte na tabeli i pliku danych zdefiniowanym w dalszej części tego artykułu.

Przykładowa tabela

Poniższy skrypt Transact-SQL tworzy testową bazę danych i tabelę o nazwie myRemap.

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;

CREATE TABLE myRemap
(
    PersonID SMALLINT,
    FirstName VARCHAR (25),
    LastName VARCHAR (30),
    Gender CHAR (1)
);

Przykładowy plik danych

Poniższe dane przedstawiają FirstName i LastName w odwrotnej kolejności, jak pokazano w tabeli myRemap. Za pomocą edytora tekstów, takiego jak Notatnik, utwórz pusty plik D:\BCP\myRemap.bcp i wstaw następujące dane:

1,Grosse,Anthony,M
2,Fatnowna,Alica,F
3,Rosenhain,Stella,F

Tworzenie plików formatu

Aby zbiorczo zaimportować dane z myRemap.bcp do tabeli myRemap, plik formatu musi wykonać następujące czynności:

• Przypisz pierwsze pole danych do pierwszej kolumny, PersonID.
• Przypisz drugie pole danych do trzeciej kolumny, LastName.
• Przypisz trzecią kolumnę danych do drugiej kolumny, FirstName.
• Przypisz czwarte pole danych do czwartej kolumny, Gender.

Najprostszą metodą utworzenia pliku formatu jest użycie narzędzia bcp. Najpierw utwórz plik formatu podstawowego z istniejącej tabeli. Po drugie zmodyfikuj plik formatu podstawowego, aby odzwierciedlał rzeczywisty plik danych.

Tworzenie pliku w formacie innego niż XML

Aby uzyskać szczegółowe informacje, zapoznaj się z Użyj plików w formacie non-XML (SQL Server). Następujące polecenie używa narzędzia bcp do generowania pliku formatu innego niż XML, myRemap.fmt, na podstawie schematu myRemap. Ponadto kwalifikator c służy do określania danych znaków, t, służy do określania przecinka jako terminatora pola, a T służy do określania zaufanego połączenia przy użyciu zintegrowanych zabezpieczeń. W wierszu polecenia wprowadź następujące polecenie:

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

Modyfikowanie pliku formatu innego niż XML

Aby uzyskać informacje na temat terminologii, zobacz Struktura plików formatu innego niż XML. Otwórz D:\BCP\myRemap.fmt w Notatniku i wykonaj następujące modyfikacje:

1. Zmień kolejność wierszy pliku formatu, tak aby wiersze znajdują się w tej samej kolejności co dane w myRemap.bcp.
2. Upewnij się, że wartości kolejności pól pliku hosta są sekwencyjne.
3. Upewnij się, że po ostatnim wierszu pliku jest znak powrotu karetki.

Porównaj zmiany:

przed

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       1       "\r\n"   4     Gender                 SQL_Latin1_General_CP1_CI_AS

po

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

Zmodyfikowany plik formatu odzwierciedla teraz:

• Pierwsze pole danych w myRemap.bcp jest mapowane na pierwszą kolumnę, myRemap.. PersonID
• Drugie pole danych w myRemap.bcp jest przypisane do trzeciej kolumny, myRemap.. LastName
• Trzecie pole danych w myRemap.bcp jest mapowane na drugą kolumnę, myRemap.. FirstName
• Czwarte pole danych w myRemap.bcp odpowiada czwartej kolumnie, myRemap.. Gender

Tworzenie pliku formatu XML

Przejrzyj pliki formatu XML (SQL Server), aby uzyskać szczegółowe informacje. Następujące polecenie używa narzędzia bcp do utworzenia pliku formatu XML, myRemap.xml, na podstawie schematu myRemap. Ponadto kwalifikator c służy do określania danych znaków, t, służy do określania przecinka jako terminatora pola, a T służy do określania zaufanego połączenia przy użyciu zintegrowanych zabezpieczeń. Kwalifikator x musi służyć do generowania pliku formatu opartego na formacie XML. W wierszu polecenia wprowadź następujące polecenie:

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

Modyfikowanie pliku formatu XML

Zapoznaj się z składnią schematu formatu XML dla plików pod kątem terminologii. Otwórz D:\BCP\myRemap.xml w Notatniku i wykonaj następujące modyfikacje:

1. Kolejność, w jakiej elementy <FIELD> są deklarowane w pliku formatu, to kolejność, w jakiej te pola są wyświetlane w pliku danych. W związku z tym należy odwrócić kolejność elementów <FIELD> z identyfikatorami 2 i 3.

2. Upewnij się, że wartości atrybutów identyfikatora <FIELD> są sekwencyjne.

3. Kolejność elementów <COLUMN> w elemencie wiersza <> określa kolejność, w jakiej operacja zbiorcza wysyła je do obiektu docelowego. Plik formatu XML przypisuje każdemu elementowi <COLUMN> nazwę lokalną, która nie ma relacji z kolumną w tabeli docelowej operacji importowania zbiorczego. Kolejność elementów <COLUMN> jest niezależna od kolejności elementów <FIELD> w definicji <RECORD>. Każdy element <COLUMN> odpowiada elementowi <FIELD> (którego identyfikator jest określony w atrybucie SOURCE elementu <COLUMN>). W związku z tym wartości <COLUMN> SOURCE są jedynymi atrybutami, które wymagają poprawki. Odwróć kolejność atrybutów <COLUMN> SOURCE 2 i 3.

Porównaj zmiany:

przed

<?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="1" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
</ROW>
</BCPFORMAT>

po

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

Zmodyfikowany plik formatu odzwierciedla teraz:

• POLE 1, które odpowiada kolumnie KOLUMNA 1, jest odwzorowane na pierwszą kolumnę tabeli, myRemap.. PersonID
• POLE 2, które odpowiada kolumnie COLUMN 2, jest ponownie mapowane na trzecią kolumnę tabeli, myRemap.. LastName
• POLE 3, które odpowiada kolumnie 3, jest przypisane do drugiej kolumny tabeli, myRemap.. FirstName
• POLE 4, które odpowiada KOLUMNA 4, jest przypisane do czwartej kolumny tabeli, myRemap.. Gender

Importowanie danych za pomocą pliku formatu, aby mapować kolumny tabeli na pola pliku danych.

W poniższych przykładach używane są plik bazy danych, plik danych i plik formatu utworzone wcześniej.

Użyj pliku formatu bcp i formatu innego niż XML

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

bcp TestDatabase.dbo.myRemap IN D:\BCP\myRemap.bcp -f D:\BCP\myRemap.fmt -T

Używanie pliku formatu bcp i XML

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

bcp TestDatabase.dbo.myRemap IN D:\BCP\myRemap.bcp -f D:\BCP\myRemap.xml -T

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

Uruchom następujący skrypt Transact-SQL.

USE TestDatabase;
GO

TRUNCATE TABLE myRemap;

BULK INSERT dbo.myRemap FROM 'D:\BCP\myRemap.bcp'
    WITH (FORMATFILE = 'D:\BCP\myRemap.fmt');
GO

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

Użyj BULK INSERT i pliku w formacie XML

Uruchom następujący skrypt Transact-SQL.

USE TestDatabase;
GO

TRUNCATE TABLE myRemap;

BULK INSERT dbo.myRemap FROM 'D:\BCP\myRemap.bcp'
    WITH (FORMATFILE = 'D:\BCP\myRemap.xml');
GO

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

Użycie OPENROWSET i pliku w formacie innym niż XML

Uruchom skrypt Transact-SQL, który jest następujący.

USE TestDatabase;
GO

TRUNCATE TABLE myRemap;

INSERT INTO dbo.myRemap
SELECT *
FROM OPENROWSET (
    BULK 'D:\BCP\myRemap.bcp',
    FORMATFILE = 'D:\BCP\myRemap.fmt'
) AS t1;
GO

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

Używanie pliku w formacie OPENROWSET i XML

Uruchom następujący skrypt Transact-SQL.

USE TestDatabase;
GO

TRUNCATE TABLE myRemap;

INSERT INTO dbo.myRemap
SELECT *
FROM OPENROWSET (
    BULK 'D:\BCP\myRemap.bcp',
    FORMATFILE = 'D:\BCP\myRemap.xml'
) AS t1;
GO

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

Powiązana zawartość

• bcp narzędzia
• Użyć plik formatu, aby pominąć kolumnę tabeli (SQL Server)
• Użyj pliku formatu, aby pominąć pole danych (SQL Server)