Sdílet prostřednictvím

Použití formátu souboru k mapování sloupců tabulky na pole datových souborů (SQL Server)

  • Článek

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

Datový soubor může obsahovat pole uspořádaná v jiném pořadí než odpovídající sloupce v tabulce. Tento článek obsahuje soubory formátu jiného formátu než XML a XML, které jsou upraveny tak, aby vyhovovaly datovému souboru, jehož pole jsou uspořádána v jiném pořadí než sloupce tabulky. Upravený formát souboru mapuje datová pole na odpovídající sloupce tabulky. Další informace najdete v tématu Vytvoření formátu souboru s bcp (SQL Server).

Soubor ve formátu XML nebo jiném než XML lze použít k hromadnému importu datového souboru do tabulky pomocí příkazu nástroje bcp, příkazu BULK INSERT nebo příkazu INSERT ... SELECT * FROM OPENROWSET. 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í, není ve službě Azure Synapse Analytics podporovaná. 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 souborů formátu v tomto článku jsou založené na tabulce a datovém souboru definovaném dále v tomto článku.

Ukázková tabulka

Následující skript Transact-SQL vytvoří testovací databázi a tabulku s názvem myRemap.

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;

CREATE TABLE myRemap
(
    PersonID SMALLINT,
    FirstName VARCHAR (25),
    LastName VARCHAR (30),
    Gender CHAR (1)
);

Ukázkový datový soubor

Následující data představují FirstName a LastName v obráceném pořadí, jak je znázorněno v tabulce myRemap. Pomocí textového editoru, jako je Poznámkový blok, vytvořte prázdný soubor D:\BCP\myRemap.bcp a vložte následující data:

1,Grosse,Anthony,M
2,Fatnowna,Alica,F
3,Rosenhain,Stella,F

Vytvoření formátových souborů

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

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

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žívá nástroj bcp k vygenerování souboru jiného formátu než XML, myRemap.fmt, na základě schématu myRemap. Kromě toho se kvalifikátor c používá k určení znakových dat, t, slouží k zadání čá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.myRemap format nul -c -f D:\BCP\myRemap.fmt -t, -T

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

Informace o terminologii naleznete v tématu Struktura souborů formátu mimo XML. Otevřete D:\BCP\myRemap.fmt v Poznámkovém bloku a proveďte následující úpravy:

  1. Změňte pořadí řádků souboru formátu tak, aby byly řádky ve stejném pořadí jako data v myRemap.bcp.
  2. Ujistěte se, že hodnoty pořadí polí souboru hostitele jsou sekvenční.
  3. Ujistěte se, že za posledním řádkem souboru ve formátu je návrat na začátek řádku.

Porovnejte změny:

před

13.0
4
1       SQLCHAR    0       7       ","      1     PersonID               ""
2       SQLCHAR    0       25      ","      2     FirstName              SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR    0       30      ","      3     LastName               SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR    0       1       "\r\n"   4     Gender                 SQL_Latin1_General_CP1_CI_AS

po

13.0
4
1       SQLCHAR    0       7       ","      1     PersonID               ""
2       SQLCHAR    0       30      ","      3     LastName               SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR    0       25      ","      2     FirstName              SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR    0       1       "\r\n"   4     Gender                 SQL_Latin1_General_CP1_CI_AS

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

  • První datové pole v myRemap.bcp je namapováno na první sloupec myRemap.. PersonID
  • Druhé datové pole v myRemap.bcp se mapuje na třetí sloupec myRemap.. LastName
  • Třetí datové pole v myRemap.bcp se mapuje na druhý sloupec myRemap.. FirstName
  • Čtvrté datové pole v myRemap.bcp se mapuje na čtvrtý sloupec myRemap.. Gender

Vytvoření souboru formátu XML

Podrobné informace najdete v souborech formátu XML (SQL Server). Následující příkaz používá nástroj bcp k vytvoření souboru formátu XML, myRemap.xml, na základě schématu myRemap. Kromě toho se kvalifikátor c používá k určení znakových dat, t, slouží k zadání čá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.myRemap format nul -c -x -f D:\BCP\myRemap.xml -t, -T

Úprava souboru formátu XML

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

  1. Pořadí, ve kterém jsou prvky <FIELD> deklarovány ve formátu souboru, je pořadí, ve kterém se tato pole zobrazují v datovém souboru. Obraťte pořadí prvků <FIELD> s ID 2 a 3.

  2. Ujistěte se, že hodnoty atributů ID <FIELD> jsou sekvenční.

  3. Pořadí prvků <COLUMN> v prvku <ŘÁDEK> definuje pořadí, ve kterém skupinová operace je odesílá do cíle. Soubor formátu XML přiřadí každému prvku <COLUMN> místní název, který nemá žádný vztah ke sloupci v cílové tabulce hromadné operace importu. Pořadí prvků <COLUMN> je nezávislé na pořadí prvků <FIELD> v definici <RECORD>. Každý prvek <COLUMN> odpovídá elementu <FIELD> (jehož ID je zadáno v atributu SOURCE elementu <COLUMN>). Hodnoty pro <COLUMN> SOURCE jsou tedy jedinými atributy, které vyžadují revizi. Obrácení pořadí atributů <COLUMN> SOURCE 2 a 3

Porovnejte 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="," MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="1" 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"/>
  <COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
</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="30" 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="1" 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="2" NAME="LastName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
</ROW>
</BCPFORMAT>

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

  • POLE 1, které odpovídá sloupci 1, se mapuje na první sloupec tabulky myRemap.. PersonID
  • POLE 2, které odpovídá sloupci 2, se přemapuje na třetí sloupec tabulky myRemap.. LastName
  • POLE 3, které odpovídá sloupci 3, se přemapuje na druhý sloupec tabulky myRemap.. FirstName
  • POLE 4, které odpovídá SLOUPCI 4, je mapováno na čtvrtý sloupec tabulky myRemap.. Gender

Import dat se souborem formátu pro mapování sloupců tabulky na pole datového souboru

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

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

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

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

Použití souboru formátu bcp a XML

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

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

Použití souboru ve formátu BULK INSERT a jiného formátu než XML

Spusťte následující Transact-SQL skript.

USE TestDatabase;
GO

TRUNCATE TABLE myRemap;

BULK INSERT dbo.myRemap FROM 'D:\BCP\myRemap.bcp'
    WITH (FORMATFILE = 'D:\BCP\myRemap.fmt');
GO

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

Použití BULK INSERT a souboru ve formátu XML

Spusťte následující Transact-SQL skript.

USE TestDatabase;
GO

TRUNCATE TABLE myRemap;

BULK INSERT dbo.myRemap FROM 'D:\BCP\myRemap.bcp'
    WITH (FORMATFILE = 'D:\BCP\myRemap.xml');
GO

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

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

Spusťte následující Transact-SQL skript.

USE TestDatabase;
GO

TRUNCATE TABLE myRemap;

INSERT INTO dbo.myRemap
SELECT *
FROM OPENROWSET (
    BULK 'D:\BCP\myRemap.bcp',
    FORMATFILE = 'D:\BCP\myRemap.fmt'
) AS t1;
GO

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

Použijte OPENROWSET a soubor ve formátu XML

Spusťte následující Transact-SQL skript.

USE TestDatabase;
GO

TRUNCATE TABLE myRemap;

INSERT INTO dbo.myRemap
SELECT *
FROM OPENROWSET (
    BULK 'D:\BCP\myRemap.bcp',
    FORMATFILE = 'D:\BCP\myRemap.xml'
) AS t1;
GO

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

Související obsah