Een indelingsbestand gebruiken om een gegevensveld (SQL Server) over te slaan

Artikel
2025-03-05

van toepassing op:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Een gegevensbestand kan meer velden bevatten dan het aantal kolommen in de tabel. In dit onderwerp wordt beschreven hoe u bestanden met niet-XML- en XML-indeling wijzigt om ruimte te bieden aan een gegevensbestand met meer velden door de tabelkolommen toe te wijzen aan de bijbehorende gegevensvelden en de extra velden te negeren. Raadpleeg Maak een bestandsindeling (Format File) voor SQL Server voor meer informatie.

Notitie

Een niet-XML- of XML-indelingsbestand kan worden gebruikt om een gegevensbestand bulkgewijs in de tabel te importeren met behulp van een bcp-hulpprogramma commando, BULK INSERT instructie of INSERT ... SELECT * FROM OPENROWSET(BULK...) 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 onderwerp zijn gebaseerd op de tabel en het gegevensbestand dat hieronder is gedefinieerd.

Voorbeeldtabel

Met het onderstaande script maakt u een testdatabase en een tabel met de naam myTestSkipField. Voer de volgende Transact-SQL uit in Microsoft SQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

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

Voorbeeldgegevensbestand

Maak een leeg bestand D:\BCP\myTestSkipField.bcp en voeg de volgende gegevens in:

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

De indelingsbestanden maken

Als u gegevens uit myTestSkipField.bcp bulksgewijs wilt importeren in de myTestSkipField tabel, moet het indelingsbestand het volgende doen:

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

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

Een bestand met een niet-XML-indeling maken

Bekijk Non-XML Format Files (SQL Server) voor meer gedetailleerde informatie. Met de volgende opdracht wordt het hulpprogramma bcp gebruikt om een bestand met een niet-XML-indeling te genereren, myTestSkipField.fmt, op basis van het schema van myTestSkipField. 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.myTestSkipField format nul -c -f D:\BCP\myTestSkipField.fmt -t, -T

Het bestand in niet-XML-indeling wijzigen

Bekijk de -structuur van bestanden die niet in XML-formaat zijn voor terminologie. Open D:\BCP\myTestSkipField.fmt in Kladblok en voer de volgende wijzigingen uit:

Kopieer de volledige rij met opmaakbestanden voor FirstName en plak deze direct na FirstName op de volgende regel.
Verhoog de orderwaarde voor het hostbestandsveld met één voor de nieuwe rij en alle volgende rijen.
Verhoog het aantal kolommen om het werkelijke aantal velden in het gegevensbestand weer te geven.
Wijzig de volgorde van de serverkolom van 2 naar 0 voor de tweede rij van het opmaakbestand.

Vergelijk de aangebrachte wijzigingen:

Vóór

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

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

Het gewijzigde indelingsbestand weerspiegelt nu:

4 gegevensvelden
Het eerste gegevensveld in myTestSkipField.bcp wordt toegewezen aan de eerste kolom, myTestSkipField.. PersonID
Het tweede gegevensveld in myTestSkipField.bcp is niet toegewezen aan een kolom.
Het derde gegevensveld in myTestSkipField.bcp wordt toegewezen aan de tweede kolom, myTestSkipField.. FirstName
Het vierde gegevensveld in myTestSkipField.bcp wordt toegewezen aan de derde kolom, myTestSkipField.. LastName

Een XML-indelingsbestand maken

Bekijk SQL Server- (XML Format Files) voor gedetailleerde informatie. Met de volgende opdracht wordt het hulpprogramma bcp gebruikt om een XML-indelingsbestand te maken, myTestSkipField.xml, op basis van het schema van myTestSkipField. 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 XML-bestand in indelingsformaat te genereren. Voer bij een opdrachtprompt de volgende opdracht in:

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

Het XML-indelingsbestand wijzigen

Bekijk de schemasyntax voor XML-bestanden voor terminologie. Open D:\BCP\myTestSkipField.xml in Kladblok en voer de volgende wijzigingen uit:

Kopieer het hele tweede veld en plak het direct na het tweede veld op de volgende regel.
Verhoog de waarde 'VELD-id' met 1 voor het nieuwe VELD en voor elk volgend VELD.
Verhoog de waarde KOLOMBRON met 1 voor FirstNameen LastName om de gewijzigde toewijzing weer te geven.

Vergelijk de aangebrachte 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="\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>

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

Het gewijzigde indelingsbestand weerspiegelt nu:

4 gegevensvelden
FIELD 1 die overeenkomt met KOLOM 1 wordt toegewezen aan de eerste tabelkolom, myTestSkipField.. PersonID
FIELD 2 komt niet overeen met een KOLOM en wordt dus niet toegewezen aan een tabelkolom.
FIELD 3 die overeenkomt met KOLOM 3 wordt toegewezen aan de tweede tabelkolom, myTestSkipField.. FirstName
FIELD 4 die overeenkomt met KOLOM 4 wordt toegewezen aan de derde tabelkolom, myTestSkipField.. LastName

Gegevens importeren met een indelingsbestand om een gegevensveld over te slaan

In de onderstaande voorbeelden worden de hierboven gemaakte database-, gegevensbestand- en indelingsbestanden gebruikt.

Gebruik bcp en bestand met een niet-XML-indeling

Voer bij een opdrachtprompt de volgende opdracht in:

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

Gebruik bcp en- en XML-indelingsbestand

Voer bij een opdrachtprompt de volgende opdracht in:

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

Gebruik BULK INSERT- en bestand met een niet-XML-indeling

Voer de volgende Transact-SQL uit in Microsoft SQL Server Management Studio (SSMS):

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;

Gebruik BULK INSERT en XML-indelingsbestand

Voer de volgende Transact-SQL uit in Microsoft SQL Server Management Studio (SSMS):

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;

Gebruik OPENROWSET(BULK...) en bestand met een niet-XML-formaat

Voer de volgende Transact-SQL uit in Microsoft SQL Server Management Studio (SSMS):

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;

Gebruik OPENROWSET(BULK...), en XML-indelingsbestand

Voer de volgende Transact-SQL uit in Microsoft SQL Server Management Studio (SSMS):

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;

Delen via

Een indelingsbestand gebruiken om een gegevensveld (SQL Server) over te slaan

Voorbeeldtestvoorwaarden

Voorbeeldtabel

Voorbeeldgegevensbestand

De indelingsbestanden maken

Een bestand met een niet-XML-indeling maken

Het bestand in niet-XML-indeling wijzigen

Een XML-indelingsbestand maken

Het XML-indelingsbestand wijzigen

Gegevens importeren met een indelingsbestand om een gegevensveld over te slaan

Gebruik bcp en bestand met een niet-XML-indeling

Gebruik bcp en- en XML-indelingsbestand

Gebruik BULK INSERT- en bestand met een niet-XML-indeling

Gebruik BULK INSERT en XML-indelingsbestand

Gebruik OPENROWSET(BULK...) en bestand met een niet-XML-formaat

Gebruik OPENROWSET(BULK...), en XML-indelingsbestand

Volgende stappen

Feedback

Aanvullende resources