Použití formátu souboru k přeskočení sloupce tabulky (SQL Server)

platí pro:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Tento článek popisuje, jak pomocí formátového souboru přeskočit import sloupce tabulky, když data pro vynechaný sloupec ve zdrojovém datovém souboru neexistují. Datový soubor může obsahovat méně polí než počet sloupců v cílové tabulce – to znamená, že import sloupce můžete přeskočit – pouze pokud platí alespoň jedna z následujících dvou podmínek v cílové tabulce:

• Vynechaný sloupec má hodnotu null.
• Vynechaný sloupec má výchozí hodnotu.

Poznámka

Tato syntaxe, včetně hromadného vložení, se ve službě Azure Synapse Analytics nepodporuje. V Azure Synapse Analytics a dalších integracích cloudové databázové platformy můžete provádět přesun dat prostřednictvím příkazu COPY ve službě Azure Data Factorynebo pomocí příkazů T-SQL, jako je COPY INTO a PolyBase.

Ukázková tabulka a datový soubor

Příklady v tomto článku očekávají tabulku s názvem myTestSkipCol ve schématu dbo. Tuto tabulku můžete vytvořit v ukázkové databázi, jako je WideWorldImporters nebo AdventureWorks nebo v jakékoli jiné databázi. Vytvořte tuto tabulku následujícím způsobem:

USE WideWorldImporters;  
GO  
CREATE TABLE myTestSkipCol  
   (  
   Col1 smallint,  
   Col2 nvarchar(50) NULL,  
   Col3 nvarchar(50) not NULL  
   );  
GO

Příklady v tomto článku také používají ukázkový datový soubor myTestSkipCol2.dat. Tento datový soubor obsahuje pouze dvě pole, i když cílová tabulka obsahuje tři sloupce.

1,DataForColumn3  
1,DataForColumn3  
1,DataForColumn3

Základní kroky

K přeskočení sloupce tabulky můžete použít soubor jiného formátu než XML nebo soubor formátu XML. V obou případech existují dva kroky:

1. Pomocí nástroje příkazového řádku bcp vytvořte výchozí formátový soubor.
2. Upravte výchozí formátový soubor v textovém editoru.

Upravený formát souboru musí namapovat každé existující pole na odpovídající sloupec v cílové tabulce. Musí také uvádět, který nebo které sloupce tabulky přeskočit.

Pokud chcete například hromadně importovat data z myTestSkipCol2.dat do tabulky myTestSkipCol, musí formátový soubor namapovat první datové pole na Col1, přeskočit Col2a namapovat druhé pole na Col3.

Možnost č. 1 – Použití souboru jiného formátu než XML

Krok 1 – Vytvoření výchozího souboru formátu mimo XML

Spuštěním následujícího příkazu b cp na příkazovém řádku vytvořte výchozí formátový soubor bez formátu XML pro ukázkovou tabulku myTestSkipCol:

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

Důležitý

Možná budete muset zadat název instance serveru, ke které se připojujete pomocí argumentu -S. Je také možné, že budete muset zadat uživatelské jméno a heslo pomocí argumentů -U a -P. Další informace naleznete v tématu bcp Utility.

Předchozí příkaz vytvoří soubor jiného formátu než XML, myTestSkipCol_Default.fmt. Tento soubor formátu se nazývá výchozí formátový soubor, protože jde o formát vygenerovaný bcp. Výchozí formátový soubor popisuje korespondenci 1:1 mezi poli datových souborů a sloupci tabulky.

Následující snímek obrazovky ukazuje hodnoty v tomto ukázkovém souboru výchozího formátu.

Poznámka

Další informace o polích formát-soubor naleznete v tématu soubory formátu jiné než XML (SQL Server).

Krok 2 – Úprava souboru formátu jiného formátu než XML

Chcete-li upravit výchozí formátový soubor jiného formátu než XML, existují dvě alternativy. Obě alternativy označují, že datové pole v datovém souboru neexistuje a že se do odpovídajícího sloupce tabulky nevloží žádná data.

Pokud chcete sloupec tabulky přeskočit, upravte výchozí formátový soubor jiného formátu než XML a upravte ho pomocí jedné z následujících alternativních metod:

Možnost č. 1 – Odstranit řádek

Upřednostňovaná metoda vynechání sloupce zahrnuje následující tři kroky:

1. Nejprve odstraňte libovolný řádek souboru formátu, který popisuje pole, které chybí ve zdrojovém datovém souboru.
2. Potom snižte hodnotu pořadí polí v souboru hostitele u každého řádku formátového souboru, který následuje za odstraněným řádkem. Cílem je mít sekvenční hodnoty v "Pořadí polí hostitelského souboru", 1 až n, které odrážejí skutečnou pozici každého datového pole v datovém souboru.
3. Nakonec zmenšete hodnotu v poli Počet sloupců tak, aby odrážela skutečný počet polí v datovém souboru.

Následující příklad vychází z výchozího formátu souboru tabulky myTestSkipCol. Tento upravený formát souboru mapuje první datové pole na Col1, přeskočí Col2a mapuje druhé datové pole na Col3. Řádek s označením Col2 byl odstraněn. Oddělovač za prvním polem byl také změněn 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

Možnost č. 2 – Úprava definice řádku

Pokud chcete sloupec tabulky přeskočit, můžete také upravit definici řádku souboru formátu, který odpovídá sloupci tabulky. V tomto řádku souboru formátu musí být hodnoty "délka předpony", "délka dat souboru hostitele" a pořadí sloupců serveru nastaveny na hodnotu 0. Také pole „ukončovací znak“ a „kolace sloupce“ musí být nastavena na „“ (to znamená na prázdnou hodnotu nebo hodnotu NULL). Hodnota "název sloupce serveru" vyžaduje neprázdný řetězec, i když skutečný název sloupce není nutný. Zbývající pole formátu vyžadují výchozí hodnoty.

Následující příklad je také odvozen z výchozího formátu souboru pro myTestSkipCol tabulku.

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

Příklady se souborem jiného formátu než XML

Následující příklady jsou založené na ukázkové tabulce myTestSkipCol a ukázkovém datovém souboru myTestSkipCol2.dat popsaném výše v tomto článku.

Použijte BULK INSERT

Tento příklad funguje pomocí některého z upravených souborů formátu mimo XML vytvořených, jak je popsáno v předchozí části. V tomto příkladu má upravený formátový soubor název myTestSkipCol2.fmt. Pokud chcete použít BULK INSERT k hromadnému importu datového souboru myTestSkipCol2.dat, spusťte v aplikaci SQL Server Management Studio (SSMS) následující kód. Aktualizujte cesty systému souborů pro umístění ukázkových souborů v počítači.

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

Možnost č. 2 – Použití souboru formátu XML

Krok 1 – Vytvoření výchozího souboru formátu XML

Vytvořte výchozí soubor formátu XML pro ukázkovou tabulku myTestSkipCol spuštěním následujícího příkazu bcp na příkazovém řádku:

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

Důležitý

Možná budete muset zadat název instance serveru, ke které se připojujete pomocí argumentu -S. Je také možné, že budete muset zadat uživatelské jméno a heslo pomocí argumentů -U a -P. Další informace naleznete v tématu bcp Utility.

Předchozí příkaz vytvoří soubor formátu XML myTestSkipCol_Default.xml. Tento formátový soubor se nazývá výchozí formátový soubor, protože je to formát vygenerovaný nástrojem bcp. Výchozí formátový soubor popisuje korespondenci 1:1 mezi poli datových souborů a sloupci tabulky.

<?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>

Poznámka

Informace o struktuře souborů formátu XML naleznete v tématu XML Format Files (SQL Server).

Krok 2 – úprava souboru formátu XML

Tady je upravený soubor formátu XML, myTestSkipCol2.xml, který přeskočí Col2. Položky FIELD a ROW pro Col2 byly odebrány a položky byly přečíslovány. Oddělovač za prvním polem byl také změněn 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>

Příklady se souborem formátu XML

Následující příklady jsou založené na ukázkové tabulce myTestSkipCol a ukázkovém datovém souboru myTestSkipCol2.dat popsaném výše v tomto článku.

Pokud chcete importovat data z myTestSkipCol2.dat do tabulky myTestSkipCol, příklady používají upravený soubor formátu XML myTestSkipCol2.xml.

Použití funkce BULK INSERT se zobrazením

Při importu přímo do tabulky pomocí příkazu bcp nebo příkazu BULK INSERT nelze sloupec přeskočit u souboru formátu XML. Můžete ale importovat do všech kromě posledního sloupce tabulky. Pokud potřebujete přeskočit jakýkoli jiný sloupec než poslední sloupec, musíte vytvořit zobrazení cílové tabulky, která obsahuje pouze sloupce obsažené v datovém souboru. Pak můžete data z tohoto souboru hromadně importovat do zobrazení.

Následující příklad vytvoří zobrazení v_myTestSkipCol v tabulce myTestSkipCol. Toto zobrazení přeskočí druhý sloupec tabulky Col2. Příklad pak použije BULK INSERT k importu datového souboru myTestSkipCol2.dat do tohoto zobrazení.

V nástroji SSMS spusťte následující kód. Aktualizujte cesty systému souborů pro umístění ukázkových souborů v počítači.

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

Použijte OPENROWSET(BULK...)

Pokud chcete použít soubor formátu XML pro přeskočení sloupce tabulky pomocí OPENROWSET(BULK...), musíte zadat explicitní seznam sloupců v seznamu výběru a také v cílové tabulce následujícím způsobem:

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

Následující příklad používá OPENROWSET zprostředkovatele hromadné sady řádků a myTestSkipCol2.xml formát souboru. Příklad hromadně importuje datový soubor myTestSkipCol2.dat do tabulky myTestSkipCol. Příkaz obsahuje explicitní seznam sloupců v seznamu select a také v cílové tabulce podle potřeby.

V nástroji SSMS spusťte následující kód. Aktualizujte cesty systému souborů pro umístění ukázkových souborů v počítači.

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

Další kroky

• bcp Utility
• hromadné vložení (Transact-SQL)
• OPENROWSET (Transact-SQL)
• Přeskočení datového pole (SQL Server) pomocí formátového souboru
• Mapování sloupců tabulky na pole Data-File (SQL Server) pomocí formátového souboru
• použití formátu souboru k hromadnému importu dat (SQL Server)