Sdílet prostřednictvím

Použití formátu souboru k přeskočení datového pole (SQL Server)

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

Datový soubor může obsahovat více polí než počet sloupců v tabulce. Tento článek popisuje úpravu souborů formátu jiného typu než XML a XML tak, aby vyhovovaly datovému souboru s více poli, a to mapováním sloupců tabulky na odpovídající datová pole a ignorováním dalších polí.

Další informace naleznete v tématu Vytvoření formátu souboru s bcp (SQL Server).

Poznámka

K hromadnému importu datového souboru do tabulky lze použít soubor ve formátu XML nebo jiného než XML pomocí příkazu nástroje bcp, příkazu BULK INSERT (Transact-SQL) nebo příkazu INSERT ... SELECT * FROM OPENROWSET BULK (Transact-SQL). Další informace najdete v tématu Použití formátu souboru k hromadnému importu dat (SQL Server).

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.

Příklady testovacích podmínek

Příklady změněných formátových souborů v tomto článku jsou založeny na ukázkové tabulce myTestSkipField a datovém souboru D:\BCP\myTestSkipField.bcp. Změňte umístění místního souboru v ukázce kódu na umístění souboru na vašem počítači.

Ukázková tabulka

Skript vytvoří testovací databázi a tabulku s názvem myTestSkipField. V aplikaci Microsoft SQL Server Management Studio (SSMS) spusťte následující Transact-SQL:

CREATE DATABASE TestDatabase;
GO

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

Ukázkový datový soubor

Vytvořte prázdný soubor D:\BCP\myTestSkipField.bcp a vložte následující data:

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

Vytvoření formátových souborů

Chcete-li hromadně importovat data z myTestSkipField.bcp do tabulky myTestSkipField, musí formátovací soubor provést následující:

  • Namapujte první datové pole na první sloupec, PersonID.
  • Přeskočte druhé datové pole.
  • Namapujte třetí datové pole na druhý sloupec, FirstName.
  • Namapujte čtvrté datové pole na třetí sloupec, LastName.

Nejjednodušší metoda vytvoření souboru formátu je pomocí nástroje bcp. Nejprve vytvořte základní formátový soubor z existující tabulky. Za druhé upravte soubor základního formátu tak, aby odrážel skutečný datový soubor.

Vytvoření souboru jiného formátu než XML

Prostudujte si použití souborů ve formátu, který není XML (SQL Server) pro podrobné informace. Následující příkaz použije nástroj bcp k vygenerování souboru jiného formátu než XML, myTestSkipField.fmt, na základě schématu myTestSkipField. Kromě toho se kvalifikátor c používá k určení znakových dat , t, slouží k určení čárky jako ukončovače pole a T slouží k určení důvěryhodného připojení pomocí integrovaného zabezpečení. Na příkazovém řádku zadejte následující příkaz:

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

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

Zkontrolujte strukturu souborů v ne-XML formátu pro terminologii. Otevřete D:\BCP\myTestSkipField.fmt v Poznámkovém bloku a proveďte následující úpravy:

  1. Zkopírujte celý řádek souboru formátu pro FirstName a vložte ho přímo za FirstName na další řádek.
  2. Zvyšte hodnotu pořadí polí souboru hostitele o jednu pro nový řádek a všechny následující řádky.
  3. Zvyšte počet sloupců tak, aby odrážel skutečný počet polí v datovém souboru.
  4. Upravte pořadí sloupců serveru z 2 na 0 pro druhý řádek souboru formátu.

Porovnejte provedené změny:

před

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

Upravený formát souboru teď odráží:

  • 4 datová pole
  • První datové pole v myTestSkipField.bcp je mapováno na první sloupec, myTestSkipField.. PersonID
  • Druhé datové pole v myTestSkipField.bcp není mapováno na žádný sloupec.
  • Třetí datové pole v myTestSkipField.bcp se mapuje na druhý sloupec myTestSkipField.. FirstName
  • Čtvrté datové pole v myTestSkipField.bcp se mapuje na třetí sloupec myTestSkipField.. LastName

Vytvoření souboru formátu XML

Podrobné informace najdete v souborech formátu XML (SQL Server ). Následující příkaz použije nástroj bcp k vytvoření souboru formátu XML, myTestSkipField.xml, na základě schématu myTestSkipField.

  • Kvalifikátor c slouží k určení dat znaků.
  • t, slouží k zadání čárky jako oddělovače pole.
  • T slouží k určení důvěryhodného připojení pomocí integrovaného zabezpečení.
  • K vygenerování souboru formátu založeného na JAZYCE XML se musí použít kvalifikátor x.

Na příkazovém řádku zadejte následující příkaz:

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

Úprava souboru formátu XML

Projděte si syntaxi schématu pro soubory formátu XML, terminologii. Otevřete D:\BCP\myTestSkipField.xml v Poznámkovém bloku a proveďte následující úpravy:

  1. Zkopírujte celé druhé pole a vložte ho přímo za druhé pole na dalším řádku.
  2. Zvyšte hodnotu FIELD ID o 1 pro novou FIELD a pro každou další FIELD.
  3. COLUMN SOURCE Zvyšte hodnotu o 1 pro FirstNamea LastName reflektujte revidované mapování.

Porovnejte provedené změny:

před

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

Upravený formát souboru teď odráží:

  • 4 datová pole
  • FIELD 1, který odpovídá COLUMN 1, je mapován na první sloupec tabulky, myTestSkipField.. PersonID
  • FIELD 2 neodpovídá žádnému COLUMN sloupci tabulky, a proto není namapován na žádný sloupec tabulky.
  • FIELD 3, který odpovídá COLUMN 3, je přiřazen druhému sloupci tabulky, myTestSkipField.. FirstName
  • FIELD 4, který odpovídá COLUMN 4, je přiřazen třetímu sloupci tabulky myTestSkipField.. LastName

Importovat data pomocí formátovacího souboru pro vynechání datového pole

Příklady používají ukázkovou databázi, datový soubor a formátovací soubory vytvořené v tomto článku.

Použití bcp a souboru jiného formátu než XML

Na příkazovém řádku zadejte následující příkaz:

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

Použití souborů formátu bcp a XML (SQL Server)

Na příkazovém řádku zadejte následující příkaz:

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

Použití souboru BULK INSERT (Transact-SQL) a jiného formátu než XML

V aplikaci Microsoft SQL Server Management Studio (SSMS) spusťte následující Transact-SQL:

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;

Použití funkce BULK INSERT (Transact-SQL) a souborů formátu XML (SQL Server)

V aplikaci Microsoft SQL Server Management Studio (SSMS) spusťte následující Transact-SQL:

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;

Použití formátu OPENROWSET BULK (Transact-SQL) a souboru jiného formátu než XML

V aplikaci Microsoft SQL Server Management Studio (SSMS) spusťte následující Transact-SQL:

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;

Použijte OPENROWSET BULK (Transact-SQL) a formátovací soubory XML (SQL Server)

V aplikaci Microsoft SQL Server Management Studio (SSMS) spusťte následující Transact-SQL:

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;

Související obsah