Używanie pliku formatu do pomijania kolumny tabeli (SQL Server)

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

W tym artykule opisano sposób użycia pliku formatu do pomijania importowania kolumny tabeli, gdy dane pominiętej kolumny nie istnieją w pliku danych źródłowych. Plik danych może zawierać mniej pól niż liczba kolumn w tabeli docelowej — oznacza to, że można pominąć importowanie kolumny — tylko wtedy, gdy w tabeli docelowej spełniony jest co najmniej jeden z następujących dwóch warunków:

Pominięta kolumna jest dopuszczana do wartości null.
Pominięta kolumna ma wartość domyślną.

Uwaga / 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 pomocą instrukcji COPY w usłudze Azure Data Factory lub za pomocą instrukcji T-SQL, takich jak COPY INTO i PolyBase.

Przykładowa tabela i plik danych

Przykłady w tym artykule oczekują tabeli o nazwie myTestSkipCol w schemacie dbo . Tę tabelę można utworzyć w przykładowej bazie danych, takiej jak WideWorldImporters lub AdventureWorks w dowolnej innej bazie danych. Utwórz tę tabelę w następujący sposób:

USE WideWorldImporters;
GO

CREATE TABLE myTestSkipCol
(
    Col1 SMALLINT,
    Col2 NVARCHAR (50) NULL,
    Col3 NVARCHAR (50) NOT NULL
);
GO

W przykładach w tym artykule użyto również przykładowego pliku danych . myTestSkipCol2.dat Ten plik danych zawiera tylko dwa pola, chociaż tabela docelowa zawiera trzy kolumny.

1,DataForColumn3
1,DataForColumn3
1,DataForColumn3

Podstawowe kroki

Aby pominąć kolumnę tabeli, możesz użyć pliku formatu innego niż XML lub pliku formatu XML. W obu przypadkach istnieją dwa kroki:

Użyj narzędzia wiersza polecenia bcp , aby utworzyć domyślny plik formatu.
Zmodyfikuj domyślny plik formatu w edytorze tekstów.

Zmodyfikowany plik formatu musi mapować każde istniejące pole na odpowiednią kolumnę w tabeli docelowej. Musi również wskazywać kolumnę tabeli lub kolumny do pominięcia.

Aby na przykład zbiorczo zaimportować dane z myTestSkipCol2.dat tabeli myTestSkipCol , plik formatu musi mapować pierwsze pole danych na Col1, pomijać Col2i mapować drugie pole na Col3.

Opcja 1 — używanie pliku w formacie innego niż XML

Krok 1. Tworzenie domyślnego pliku formatu innego niż XML

Utwórz domyślny plik formatu innego niż XML dla przykładowej myTestSkipCol tabeli, uruchamiając następujące polecenie bcp w wierszu polecenia:

bcp WideWorldImporters..myTestSkipCol format nul -f myTestSkipCol_Default.fmt -c -T

Ważne

Może być konieczne określenie nazwy wystąpienia serwera, z którym nawiązujesz połączenie z argumentem -S . Ponadto może być konieczne określenie nazwy użytkownika i hasła przy użyciu -U argumentów i -P . Aby uzyskać więcej informacji, zobacz narzędzie bcp.

Poprzednie polecenie tworzy plik formatu innego niż XML. myTestSkipCol_Default.fmt Ten plik formatu jest nazywany plikiem formatu domyślnego , ponieważ jest to formularz generowany przez narzędzie bcp. Domyślny plik formatu opisuje korespondencję jeden do jednego między polami danych i kolumnami tabeli.

Poniższy zrzut ekranu przedstawia wartości w tym przykładowym pliku formatu domyślnego.

Diagram przedstawiający szczegółowy domyślny plik formatu innego niż XML dla pliku mytestskipcol.

Uwaga / Notatka

Aby uzyskać więcej informacji na temat pól format-plik, zobacz pliki formatujące inne niż XML (SQL Server).

Krok 2. Modyfikowanie pliku formatu innego niż XML

Aby zmodyfikować domyślny plik formatu innego niż XML, istnieją dwie alternatywy. Każda alternatywa wskazuje, że pole danych nie istnieje w pliku danych i że żadne dane nie mają być wstawione do odpowiedniej kolumny tabeli.

Aby pominąć kolumnę tabeli, zmodyfikuj domyślny plik formatu innego niż XML i zmodyfikuj plik przy użyciu jednej z następujących alternatywnych metod:

Opcja 1 — usuwanie wiersza

Preferowana metoda pomijania kolumny obejmuje następujące trzy kroki:

Najpierw usuń dowolny wiersz pliku formatu, który opisuje pole, którego brakuje w pliku danych źródłowych.
Następnie zmniejsz wartość "Kolejność pól pliku hosta" każdego wiersza pliku formatu, który następuje po usuniętym wierszu. Celem są sekwencyjne wartości "Kolejność pól pliku hosta", od 1 do n, które odzwierciedlają rzeczywiste położenie każdego pola danych w pliku danych.
Na koniec zmniejsz wartość w polu "Liczba kolumn", aby odzwierciedlić rzeczywistą liczbę pól w pliku danych.

Poniższy przykład jest oparty na domyślnym pliku formatu tabeli myTestSkipCol . Ten zmodyfikowany plik formatu mapuje pierwsze pole danych na Col1, pomija i mapuje Col2drugie pole danych na Col3. Wiersz dla Col2 elementu został usunięty. Ogranicznik po pierwszym polu został również zmieniony z \t na ,.

14.0
2
1       SQLCHAR       0       7       ","      1     Col1         ""
2       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

Opcja 2 — modyfikowanie definicji wiersza

Alternatywnie, aby pominąć kolumnę tabeli, można zmodyfikować definicję wiersza pliku formatu, który odpowiada kolumnie tabeli. W tym wierszu format-plik wartości "długość prefiksu", "długość danych pliku hosta" i "kolejność kolumn serwera" muszą być ustawione na 0. Ponadto pola "terminator" i "sortowanie kolumn" muszą być ustawione na "" (czyli na wartość pustą lub NULL wartość). Wartość "nazwa kolumny serwera" wymaga niepustego ciągu, choć rzeczywista nazwa kolumny nie jest konieczna. Pozostałe pola formatu wymagają wartości domyślnych.

Poniższy przykład pochodzi również z domyślnego pliku formatu dla myTestSkipCol tabeli.

14.0
3
1       SQLCHAR       0       7       ","      1     Col1         ""
2       SQLCHAR       0       0       ""       0     Col2         ""
3       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

Przykłady z plikiem formatu innego niż XML

Poniższe przykłady są oparte na przykładowej myTestSkipCol tabeli i myTestSkipCol2.dat przykładowym pliku danych opisanym wcześniej w tym artykule.

Używanie funkcji BULK INSERT

Ten przykład działa przy użyciu dowolnych zmodyfikowanych plików formatu innego niż XML utworzonych zgodnie z opisem w poprzedniej sekcji. W tym przykładzie zmodyfikowany plik formatu ma nazwę myTestSkipCol2.fmt. BULK INSERT Aby zbiorczo zaimportować myTestSkipCol2.dat plik danych, uruchom następujący kod w programie SQL Server Management Studio (SSMS). Zaktualizuj ścieżki systemu plików dla lokalizacji przykładowych plików na komputerze.

USE WideWorldImporters;
GO

BULK INSERT myTestSkipCol FROM 'C:\myTestSkipCol2.dat'
    WITH (FORMATFILE = 'C:\myTestSkipCol2.fmt');
GO

SELECT *
FROM myTestSkipCol;
GO

Opcja 2 — używanie pliku formatu XML

Krok 1. Tworzenie domyślnego pliku formatu XML

Utwórz domyślny plik formatu XML dla przykładowej myTestSkipCol tabeli, uruchamiając następujące polecenie bcp w wierszu polecenia:

bcp WideWorldImporters..myTestSkipCol format nul -f myTestSkipCol_Default.xml -c -x -T

Ważne

Poprzednie polecenie tworzy plik formatu XML, myTestSkipCol_Default.xml. Ten plik formatu jest nazywany plikiem formatu domyślnego , ponieważ jest to formularz generowany przez narzędzie bcp. Domyślny plik formatu opisuje korespondencję jeden do jednego między polami danych i kolumnami tabeli.

<?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="7" />
    <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
    <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
  </RECORD>
  <ROW>
    <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT" />
    <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR" />
    <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLNVARCHAR" />
  </ROW>
</BCPFORMAT>

Uwaga / Notatka

Aby uzyskać informacje na temat struktury plików formatu XML, zobacz Pliki formatu XML (SQL Server).

Krok 2. Modyfikowanie pliku formatu XML

Oto zmodyfikowany plik formatu XML , myTestSkipCol2.xmlktóry pomija Col2element . Wpisy FIELD i ROW zostały Col2 usunięte, a wpisy zostały ponownie ponumerowane. Ogranicznik po pierwszym polu został również zmieniony z \t na ,.

<?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="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
  </RECORD>
  <ROW>
    <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT" />
    <COLUMN SOURCE="2" NAME="Col3" xsi:type="SQLNVARCHAR" />
  </ROW>
</BCPFORMAT>

Przykłady z plikiem formatu XML

Poniższe przykłady są oparte na przykładowej myTestSkipCol tabeli i myTestSkipCol2.dat przykładowym pliku danych opisanym wcześniej w tym artykule.

Aby zaimportować dane z myTestSkipCol2.dat tabeli do myTestSkipCol tabeli, przykłady używają zmodyfikowanego pliku formatu XML, myTestSkipCol2.xml.

Używanie funkcji BULK INSERT z widokiem

Przy użyciu pliku formatu XML nie można pominąć kolumny podczas importowania bezpośrednio do tabeli przy użyciu polecenia bcp lub BULK INSERT instrukcji . Można jednak zaimportować do wszystkich, ale do ostatniej kolumny tabeli. Jeśli musisz pominąć dowolną kolumnę inną niż ostatnia kolumna, musisz utworzyć widok tabeli docelowej zawierającej tylko kolumny zawarte w pliku danych. Następnie możesz zbiorczo zaimportować dane z tego pliku do widoku.

Poniższy przykład tworzy v_myTestSkipCol widok w myTestSkipCol tabeli. Ten widok pomija drugą kolumnę tabeli. Col2 W tym przykładzie użyto BULK INSERT metody do zaimportowania myTestSkipCol2.dat pliku danych do tego widoku.

W programie SSMS uruchom następujący kod. Zaktualizuj ścieżki systemu plików dla lokalizacji przykładowych plików na komputerze.

USE WideWorldImporters;
GO

CREATE VIEW v_myTestSkipCol AS
    SELECT Col1,
           Col3
    FROM myTestSkipCol;
GO

BULK INSERT v_myTestSkipCol FROM 'C:\myTestSkipCol2.dat'
    WITH (FORMATFILE = 'C:\myTestSkipCol2.xml');
GO

Użyj FUNKCJI OPENROWSET(BULK...)

Aby użyć pliku formatu XML, aby pominąć kolumnę tabeli przy użyciu polecenia OPENROWSET(BULK...), należy podać jawną listę kolumn na liście wyboru, a także w tabeli docelowej w następujący sposób:

INSERT ...<column_list> SELECT <column_list> FROM OPENROWSET(BULK...)

W poniższym przykładzie użyto OPENROWSET dostawcy zestawów wierszy zbiorczych i myTestSkipCol2.xml pliku formatu. Przykładowy przykład zbiorczo importuje myTestSkipCol2.dat plik danych do myTestSkipCol tabeli. Instrukcja zawiera jawną listę kolumn na liście wyboru, a także w tabeli docelowej zgodnie z wymaganiami.

W programie SSMS uruchom następujący kod. Zaktualizuj ścieżki systemu plików dla lokalizacji przykładowych plików na komputerze.

USE WideWorldImporters;
GO

INSERT INTO myTestSkipCol (Col1, Col3)
SELECT Col1,
       Col3
FROM OPENROWSET (
    BULK 'C:\myTestSkipCol2.Dat',
    FORMATFILE = 'C:\myTestSkipCol2.Xml'
) AS t1;
GO

Sprzężenie zwrotne

Czy ta strona była pomocna?

Last updated on 2025-09-09

Udostępnij za pomocą

Używanie pliku formatu do pomijania kolumny tabeli (SQL Server)

Przykładowa tabela i plik danych

Podstawowe kroki

Opcja 1 — używanie pliku w formacie innego niż XML

Krok 1. Tworzenie domyślnego pliku formatu innego niż XML

Krok 2. Modyfikowanie pliku formatu innego niż XML

Opcja 1 — usuwanie wiersza

Opcja 2 — modyfikowanie definicji wiersza

Przykłady z plikiem formatu innego niż XML

Używanie funkcji BULK INSERT

Opcja 2 — używanie pliku formatu XML

Krok 1. Tworzenie domyślnego pliku formatu XML

Krok 2. Modyfikowanie pliku formatu XML

Przykłady z plikiem formatu XML

Używanie funkcji BULK INSERT z widokiem

Użyj FUNKCJI OPENROWSET(BULK...)

Treści powiązane

Sprzężenie zwrotne

Dodatkowe zasoby