Delen via

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

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

Een gegevensbestand kan meer velden bevatten dan het aantal kolommen in de tabel. In dit artikel wordt beschreven hoe u bestanden zonder XML- en XML-indeling wijzigt om een gegevensbestand met meer velden toe te passen door de tabelkolommen toe te wijzen aan de bijbehorende gegevensvelden en de extra velden te negeren.

Zie Een indelingsbestand maken met bcp (SQL Server) voor meer informatie.

Notitie

Een XML- of niet-XML-indelingsbestand kan worden gebruikt om een gegevensbestand bulkgewijs in de tabel te importeren met behulp van een bcp-hulpprogramma, BULK INSERT (Transact-SQL) instructie, of INSERT ... SELECT * FROM OPENROWSET BULK (Transact-SQL) 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 voorbeeldtabel myTestSkipField en het D:\BCP\myTestSkipField.bcpgegevensbestand. Wijzig de locatie van het lokale bestand in het codevoorbeeld in een bestandslocatie op uw computer.

Voorbeeldtabel

Met het 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

Raadpleeg Gebruik bestanden in niet-XML-indeling (SQL Server) voor 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:

  1. Kopieer de volledige rij met opmaakbestanden voor FirstName en plak deze direct na FirstName op de volgende regel.
  2. Verhoog de orderwaarde voor het hostbestandsveld met één voor de nieuwe rij en alle volgende rijen.
  3. Verhoog het aantal kolommen om het werkelijke aantal velden in het gegevensbestand weer te geven.
  4. 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

Na

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 XML-indelingsbestanden (SQL Server) 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.

  • De kwalificatie c wordt gebruikt om tekengegevens op te geven
  • t, wordt gebruikt om een komma op te geven als een veldeindteken
  • 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:

  1. Kopieer het hele tweede veld en plak het direct na het tweede veld op de volgende regel.
  2. Verhoog de FIELD ID waarde met 1 voor de nieuwe FIELD en voor elke volgende FIELD.
  3. Verhoog de COLUMN SOURCE waarde met 1 voor FirstNameen LastName om de herziene 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>

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="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 COLUMN 1 is toegewezen aan de eerste tabelkolom, myTestSkipField.. PersonID
  • FIELD 2 komt met geen enkele COLUMN overeen en is daarom niet gekoppeld aan een tabelkolom.
  • FIELD 3 die overeenkomt met COLUMN 3 wordt toegewezen aan de tweede tabelkolom, myTestSkipField.. FirstName
  • FIELD 4 die overeenkomt met COLUMN 4 is toegewezen aan de derde tabelkolom, myTestSkipField.. LastName

Gegevens importeren met een indelingsbestand om een gegevensveld over te slaan

In de voorbeelden worden de voorbeelddatabase, het gegevensbestand en de indelingsbestanden gebruikt die in dit artikel zijn gemaakt.

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

BCP- en XML-indelingsbestanden (SQL Server) gebruiken

Voer bij een opdrachtprompt de volgende opdracht in:

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

BULK INSERT (Transact-SQL) en niet-XML-indelingsbestand gebruiken

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;

BULK INSERT (Transact-SQL) en XML-indelingsbestanden (SQL Server) gebruiken

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 (Transact-SQL) en een niet-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.fmt'
        ) AS t1;
GO

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

OPENROWSET BULK (Transact-SQL) en XML-indelingsbestanden (SQL Server) gebruiken

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;

Verwante inhoud