Delen via

Een indelingsbestand gebruiken om tabelkolommen toe te wijzen aan gegevensbestandsvelden (SQL Server)

  • Artikel

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Een gegevensbestand kan velden bevatten die in een andere volgorde zijn gerangschikt dan de bijbehorende kolommen in de tabel. In dit artikel worden bestanden met niet-XML- en XML-indelingen weergegeven die zijn aangepast voor een gegevensbestand waarvan de velden in een andere volgorde zijn gerangschikt dan de tabelkolommen. Het gewijzigde indelingsbestand wijst de gegevensvelden toe aan de bijbehorende tabelkolommen. Bekijk Een indelingsbestand maken met bcp (SQL Server) voor meer informatie.

Een niet-XML- of XML-indelingsbestand kan worden gebruikt om een gegevensbestand bulksgewijs in de tabel te importeren met behulp van een bcp-hulpprogramma opdracht, BULK INSERT instructie, of INSERT ... SELECT * FROM OPENROWSET instructie. Zie Een indelingsbestand gebruiken om gegevens (SQL Server) bulksgewijs te importerenvoor meer informatie.

Notitie

Deze syntaxis, inclusief bulksgewijs invoegen, wordt niet ondersteund in Azure Synapse Analytics. In Azure Synapse Analytics en andere integraties van het clouddatabaseplatform kunt u gegevensverplaatsing uitvoeren via de COPY-instructie in Azure Data Factoryof met behulp van T-SQL-instructies zoals COPY INTO en PolyBase.

Voorbeeldtestvoorwaarden

De voorbeelden van gewijzigde indelingsbestanden in dit artikel zijn gebaseerd op de tabel en het gegevensbestand die verderop in dit artikel zijn gedefinieerd.

Voorbeeldtabel

Met het volgende Transact-SQL script maakt u een testdatabase en een tabel met de naam myRemap.

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;

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

Voorbeeldgegevensbestand

De volgende gegevens bevatten FirstName en LastName in de omgekeerde volgorde, zoals weergegeven in de tabel myRemap. Maak met behulp van een teksteditor zoals Kladblok een leeg bestand D:\BCP\myRemap.bcp en voeg de volgende gegevens in:

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

De indelingsbestanden maken

Als u gegevens van myRemap.bcp bulksgewijs wilt importeren in de myRemap tabel, moet het indelingsbestand de volgende acties uitvoeren:

  • Wijs het eerste gegevensveld toe aan de eerste kolom, PersonID.
  • Wijs het tweede gegevensveld toe aan de derde kolom LastName.
  • Wijs het derde gegevensveld toe aan de tweede kolom, FirstName.
  • Wijs het vierde gegevensveld toe aan de vierde kolom, Gender.

De eenvoudigste methode voor het maken van het indelingsbestand is met behulp van het bcp-hulpprogramma. Maak eerst een basisformaatbestand op basis van de bestaande tabel. Vervolgens het basisindelingsbestand wijzigen om het werkelijke gegevensbestand weer te geven.

Een bestand met een niet-XML-indeling maken

Raadpleeg Gebruik bestanden in niet-XML-indeling (SQL Server) voor gedetailleerde informatie. De volgende opdracht maakt gebruik van het hulpprogramma bcp voor het genereren van een bestand met een niet-XML-indeling, myRemap.fmt, op basis van het schema van myRemap. Bovendien wordt de kwalificatie c gebruikt om tekengegevens op te geven, t, wordt gebruikt om een komma op te geven als veldeindteken en T wordt gebruikt om een vertrouwde verbinding op te geven met geïntegreerde beveiliging. Voer bij een opdrachtprompt de volgende opdracht in:

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

Het niet-XML-indelingsbestand wijzigen

Zie Structuur van bestanden zonder XML-indelingvoor meer informatie over terminologie. Open D:\BCP\myRemap.fmt in Kladblok en voer de volgende wijzigingen uit:

  1. Rangschik de volgorde van de rijen in het indelingsbestand, zodat de rijen zich in dezelfde volgorde bevinden als de gegevens in myRemap.bcp.
  2. Zorg ervoor dat de volgordewaarden van het hostbestandsveld opeenvolgend zijn.
  3. Zorg ervoor dat er een regelterugloop is na de laatste rij met indelingsbestanden.

Vergelijk de wijzigingen:

Vóór

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

Na

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

Het gewijzigde formaatbestand geeft nu weer:

  • Het eerste gegevensveld in myRemap.bcp wordt toegewezen aan de eerste kolom, myRemap.. PersonID
  • Het tweede gegevensveld in myRemap.bcp wordt toegewezen aan de derde kolom, myRemap.. LastName
  • Het derde gegevensveld in myRemap.bcp wordt toegewezen aan de tweede kolom, myRemap.. FirstName
  • Het vierde gegevensveld in myRemap.bcp wordt toegewezen aan de vierde kolom, myRemap.. Gender

Een XML-indelingsbestand maken

Bekijk SQL Server- (XML Format Files) voor gedetailleerde informatie. De volgende opdracht maakt gebruik van het hulpprogramma bcp voor het maken van een XML-indelingsbestand, myRemap.xml, op basis van het schema van myRemap. Bovendien wordt de kwalificatie c gebruikt om tekengegevens op te geven, t, wordt gebruikt om een komma op te geven als veldeindteken en T wordt gebruikt om een vertrouwde verbinding op te geven met geïntegreerde beveiliging. De x kwalificator moet worden gebruikt om een op XML-gebaseerd indelingsbestand te genereren. Voer bij een opdrachtprompt de volgende opdracht in:

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

Het XML-indelingsbestand wijzigen

Bekijk schemasyntaxis voor XML-indelingsbestanden voor terminologie. Open D:\BCP\myRemap.xml in Kladblok en voer de volgende wijzigingen uit:

  1. De volgorde waarin de <FIELD> elementen worden gedeclareerd in het indelingsbestand, is de volgorde waarin deze velden worden weergegeven in het gegevensbestand. Draai dus de volgorde voor de <FIELD> elementen om met id-kenmerken 2 en 3.

  2. Zorg ervoor dat de <FIELD> id-kenmerkwaarden opeenvolgend zijn.

  3. De volgorde van de <COLUMN>-elementen in het element <RIJ> definieert de volgorde waarin de bulkbewerking deze naar het doel verzendt. Het XML-indelingsbestand wijst elk <COLUMN> element een lokale naam toe die geen relatie heeft met de kolom in de doeltabel van een bulkimportbewerking. De volgorde van de <COLUMN> elementen is onafhankelijk van de volgorde van <FIELD> elementen in een <RECORD> definitie. Elk <COLUMN> element komt overeen met een <FIELD> element (waarvan de id is opgegeven in het kenmerk SOURCE van het <COLUMN>-element). De waarden voor <COLUMN> SOURCE zijn dus de enige kenmerken waarvoor revisie is vereist. De volgorde voor <COLUMN> SOURCE-kenmerken 2 en 3 omkeren.

Vergelijk de wijzigingen:

Vóór

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

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="," 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>

Het gewijzigde indelingsbestand weerspiegelt nu:

  • FIELD 1, dat overeenkomt met KOLOM 1, wordt toegewezen aan de eerste tabelkolom, myRemap.. PersonID
  • VELD 2, dat overeenkomt met KOLOM 2, wordt opnieuw toegewezen aan de derde tabelkolom, myRemap.. LastName
  • VELD 3, dat overeenkomt met KOLOM 3, wordt opnieuw toegewezen aan de tweede tabelkolom, myRemap.. FirstName
  • FIELD 4, dat overeenkomt met KOLOM 4, wordt toegewezen aan de vierde tabelkolom, myRemap.. Gender

Gegevens importeren met een formaatbestand om tabelkolommen toe te wijzen aan velden in het gegevensbestand

In de volgende voorbeelden worden de database-, gegevensbestand- en indelingsbestanden gebruikt die u eerder hebt gemaakt.

BCP- en niet-XML-indelingsbestand gebruiken

Voer bij een opdrachtprompt de volgende opdracht in.

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

BCP- en XML-indelingsbestand gebruiken

Voer bij een opdrachtprompt de volgende opdracht in.

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

Gebruik BULK INSERT en een niet-XML-indelingsbestand

Voer het volgende Transact-SQL script uit.

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;

Gebruik BULK INSERT en XML-indelingsbestand

Voer het volgende Transact-SQL script uit.

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;

Gebruik OPENROWSET en een niet-XML-indelingsbestand

Voer het volgende Transact-SQL script uit.

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;

OPENROWSET- en XML-indelingsbestand gebruiken

Voer het volgende Transact-SQL script uit.

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;

Verwante inhoud