Sdílet prostřednictvím

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

  • Článek

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. Toto téma popisuje úpravu souborů formátu jiného typu než XML a XML tak, aby se přizpůsobily datovému souboru s více poli, a to tak, že sloupce tabulky namapují na odpovídající datová pole a ignorují další pole. Podívejte se na Vytvoření souboru formátu (SQL Server) pro další informace.

Poznámka

K hromadnému importu datového souboru do tabulky lze použít soubor jiného formátu než XML nebo XML pomocí nástroje bcp, příkazu BULK INSERT nebo INSERT ... SELECT * FROM OPENROWSET(BULK...). 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 upravených formátových souborů v tomto tématu jsou založené na tabulce a datovém souboru definovaném níže.

Ukázková tabulka

Následující 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

Podrobné informace najdete v souborů ve formátu, který není XML (SQL Server). 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 u souborů 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. 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í. 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. Zvětšete hodnotu "ID POLE" o 1 pro nové POLE a pro každé další POLE.
  3. Zvyšte hodnotu COLUMN SOURCE o 1 pro FirstNamea LastName tak, aby odrážela 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
  • POLE 1, které odpovídá SLOUPCI 1, se mapuje na první sloupec tabulky myTestSkipField.. PersonID
  • POLE 2 neodpovídá žádnému sloupci, a proto není mapováno na žádný sloupec tabulky.
  • POLE 3, které odpovídá sloupci 3, se mapuje na druhý sloupec tabulky myTestSkipField.. FirstName
  • POLE 4, které odpovídá SLOUPCI 4, je mapováno na třetí sloupec tabulky myTestSkipField.. LastName

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

Následující příklady používají soubory databáze, datového souboru a formátu vytvořené výše.

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žijte bcp a soubor ve formátu 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.xml -T

Použití BULK INSERT 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;
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í formátu BULK INSERT a 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.xml');  
GO

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

Použijte OPENROWSET(BULK...) a soubor ne-XML formátu

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...) a soubor formátu 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.xml'  
       ) AS t1;
GO

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

Další kroky