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

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

Plik danych może zawierać więcej pól niż liczba kolumn w tabeli. W tym artykule opisano modyfikowanie plików formatu innego niż XML i XML w celu dostosowania pliku danych do większej liczby pól przez mapowanie kolumn tabeli na odpowiednie pola danych i ignorowanie dodatkowych pól.

Aby uzyskać więcej informacji, zobacz Tworzenie pliku formatu za pomocą narzędzia bcp (SQL Server).

Przykładowe warunki testu

Przykłady zmodyfikowanych plików formatu w tym artykule są oparte na przykładowej tabeli myTestSkipField i pliku D:\BCP\myTestSkipField.bcpdanych . Zmień lokalizację pliku lokalnego w przykładzie kodu na lokalizację pliku na maszynie.

Przykładowa tabela

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

Aby uzyskać szczegółowe informacje, zapoznaj się z Użyj plików w formacie non-XML (SQL Server). 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:

1. Skopiuj cały wiersz pliku formatu dla FirstName i wklej go bezpośrednio po FirstName w następnym wierszu.
2. Zwiększ wartość kolejności pola pliku hosta o jeden dla nowego wiersza i wszystkich kolejnych wierszy.
3. Zwiększ liczbę kolumn, aby odzwierciedlić rzeczywistą liczbę pól w pliku danych.
4. 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

po

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

Przejrzyj pliki formatu XML (SQL Server), aby uzyskać szczegółowe informacje. Następujące polecenie użyje narzędzia bcp do utworzenia pliku formatu XML, myTestSkipField.xml, na podstawie schematu myTestSkipField.

• Kwalifikator c służy do określania danych znaków
• t, służy do określania przecinka jako terminatora pola
• 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:

1. Skopiuj całe drugie pole i wklej je bezpośrednio po drugim polu w następnym wierszu.
2. Zwiększ wartość FIELD ID o 1 dla nowego FIELD i każdej kolejnej FIELD.
3. Zwiększ wartość COLUMN SOURCE o 1 dla FirstName elementu i LastName, by 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>

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="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
• FIELD 1 jako odpowiednik COLUMN 1 jest przypisane do pierwszej kolumny tabeli, myTestSkipField.. PersonID
• FIELD 2 nie odpowiada żadnemu COLUMN , dlatego nie jest mapowany na żadną kolumnę tabeli.
• FIELD 3, które odpowiada COLUMN 3, jest mapowane na drugą kolumnę tabeli, myTestSkipField.. FirstName
• FIELD 4 odpowiadające COLUMN 4 jest przyporządkowane trzeciej kolumnie tabeli, myTestSkipField.. LastName

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

W przykładach użyto przykładowej bazy danych, pliku danych i plików formatu utworzonych w tym artykule.

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żywanie plików formatu bcp i XML (SQL Server)

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

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

Używanie funkcji BULK INSERT (Transact-SQL) i pliku formatu innego niż XML

Wykonaj 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żywanie funkcji BULK INSERT (Transact-SQL) i plików formatu XML (SQL Server)

Wykonaj 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.xml');  
GO

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

Używanie pliku BULK OPENROWSET (Transact-SQL) i pliku formatu innego niż 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.fmt'
        ) AS t1;
GO

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

Użyj OPENROWSET BULK (Transact-SQL) i plików formatu XML (SQL Server)

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;

Treści powiązane

• narzędzia bcp
• MASOWE WSTAWIANIE (Transact-SQL)
• OPENROWSET (Transact-SQL)
• użyć pliku formatu, aby pominąć kolumny tabeli (SQL Server)
• Użyj pliku formatu do mapowania kolumn tabeli na pola pliku danych (SQL Server)