Używanie pliku formatu do pomijania pola danych (SQL Server)

Artykuł
03/05/2025

Dotyczy:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Plik danych może zawierać więcej pól niż liczba kolumn w tabeli. W tym temacie opisano modyfikowanie plików formatu innego niż XML i XML w celu uwzględnienia pliku danych z większą ilością pól przez mapowanie kolumn tabeli na odpowiednie pola danych i ignorowanie dodatkowych pól. Aby uzyskać dodatkowe informacje, zapoznaj się z Create a Format File (SQL Server).

Notatka

Plik w formacie XML lub innym niż XML można użyć do zbiorczego importowania pliku danych do tabeli za pomocą polecenia narzędzia bcp, instrukcji BULK INSERT lub instrukcji INSERT ... SELECT * FROM OPENROWSET(BULK...). 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 integracjach platform baz danych w chmurze realizuj przenoszenie danych za pomocą instrukcji COPY w usłudze Azure Data Factorylub przy użyciu instrukcji języka T-SQL , takich jak COPY INTO i PolyBase.

Przykładowe warunki testu

Przykłady zmodyfikowanych 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 myTestSkipField. Wykonaj polecenie Transact-SQL w programie Microsoft SQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE myTestSkipField
   (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30)
   );

Przykładowy plik danych

Utwórz pusty plik D:\BCP\myTestSkipField.bcp i wstaw następujące dane:

1,SkipMe,Anthony,Grosse
2,SkipMe,Alica,Fatnowna
3,SkipMe,Stella,Rosenhain

Tworzenie plików formatu

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

Dopasuj pierwsze pole danych do pierwszej kolumny, PersonID.
Pomiń drugie pole danych.
Przypisz trzecie pole danych do drugiej kolumny, FirstName.
Przypisz czwartą wartość danych do trzeciej kolumny, LastName.

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

Przejrzyj pliki w formacie innym niż XML () w SQL Serverze, aby uzyskać szczegółowe informacje. Następujące polecenie użyje narzędzia bcp do wygenerowania pliku formatu innego niż xml, myTestSkipField.fmt, na podstawie schematu myTestSkipField. 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.myTestSkipField format nul -c -f D:\BCP\myTestSkipField.fmt -t, -T

Modyfikowanie pliku formatu innego niż XML

Zapoznaj się z struktury plików w formacie innego niż XML terminologii. Otwórz D:\BCP\myTestSkipField.fmt w Notatniku i wykonaj następujące modyfikacje:

Skopiuj cały wiersz pliku formatu dla FirstName i wklej go bezpośrednio po FirstName w następnym wierszu.
Zwiększ wartość kolejności pola pliku hosta o jeden dla nowego wiersza i wszystkich kolejnych wierszy.
Zwiększ liczbę kolumn, aby odzwierciedlić rzeczywistą liczbę pól w pliku danych.
Zmodyfikuj kolejność kolumn serwera z 2 do 0 dla drugiego wiersza pliku formatu.

Porównaj wprowadzone zmiany:

przed

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

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

Zmodyfikowany plik formatu odzwierciedla teraz:

4 pola danych
Pierwsze pole danych w myTestSkipField.bcp jest mapowane na pierwszą kolumnę, myTestSkipField.. PersonID
Drugie pole danych w myTestSkipField.bcp nie jest mapowane na żadną kolumnę.
Trzecie pole danych w myTestSkipField.bcp jest mapowane na drugą kolumnę, myTestSkipField.. FirstName
Czwarte pole danych w myTestSkipField.bcp jest przypisane do trzeciej kolumny myTestSkipField.. LastName.

Tworzenie pliku formatu XML

Aby uzyskać szczegółowe informacje, zapoznaj się z plikami formatu XML (SQL Server), . Następujące polecenie użyje narzędzia bcp do utworzenia pliku formatu XML, myTestSkipField.xml, na podstawie schematu myTestSkipField. 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.myTestSkipField format nul -c -x -f D:\BCP\myTestSkipField.xml -t, -T

Modyfikowanie pliku formatu XML

Przejrzyj składnię schematu dla plików w formacie XML pod kątem terminologii. Otwórz D:\BCP\myTestSkipField.xml w Notatniku i wykonaj następujące modyfikacje:

Skopiuj całe drugie pole i wklej je bezpośrednio po drugim polu w następnym wierszu.
Zwiększ wartość "IDENTYFIKATOR POLA" o 1 dla nowego pola i dla każdego kolejnego POLA.
Zwiększ wartość "ŹRÓDŁO KOLUMNY" o 1 dla FirstNamei LastName, aby odzwierciedlić poprawione mapowanie.

Porównaj wprowadzone 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="\r\n" MAX_LENGTH="30" 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"/>
</ROW>
</BCPFORMAT>

<?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="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30" 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="4" NAME="LastName" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

Zmodyfikowany plik formatu odzwierciedla teraz:

4 pola danych
POLE 1, które odpowiada kolumnie COLUMN 1, jest mapowane na pierwszą kolumnę tabeli, myTestSkipField.. PersonID
POLE 2 nie odpowiada żadnej kolumnie i dlatego nie jest mapowane do żadnej kolumny tabeli.
POLE 3, które odpowiada KOLUMNIE 3, jest mapowane na drugą kolumnę tabeli, myTestSkipField.. FirstName
POLE 4, które odpowiada kolumnie COLUMN 4, jest mapowane na trzecią kolumnę tabeli, myTestSkipField.. LastName

Importowanie danych za pomocą pliku formatu w celu pominięcia pola danych

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

Użyj oraz pliku w formacie innym niż XML

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

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

Użyj bcp i pliku w formacie XML

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

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

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

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

USE TestDatabase;  
GO

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

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

Użyj BULK INSERT i plików w formacie XML

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

USE TestDatabase;  
GO

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

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

Użyj OPENROWSET(BULK...) i pliku w formacie nie-XML

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

USE TestDatabase;
GO

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

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

Użyj OPENROWSET(BULK...) i pliku w formacie XML

Wykonaj polecenie Transact-SQL w programie Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

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

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

Następne kroki

narzędzia bcp
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
użyć pliku formatu, aby pominąć kolumny tabeli (SQL Server)
Użyj pliku formatu, aby zmapować kolumny tabeli na pola Data-File (SQL Server).

Udostępnij za pośrednictwem