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

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

In dit artikel wordt beschreven hoe u een indelingsbestand gebruikt om het importeren van een tabelkolom over te slaan wanneer de gegevens voor de overgeslagen kolom niet bestaan in het brongegevensbestand. Een gegevensbestand kan minder velden bevatten dan het aantal kolommen in de doeltabel: u kunt het importeren van een kolom overslaan, alleen als aan ten minste één van de volgende twee voorwaarden in de doeltabel wordt voldaan:

• De overgeslagen kolom is null-waarde.
• De overgeslagen kolom heeft een standaardwaarde.

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.

Voorbeeldtabel en gegevensbestand

In de voorbeelden in dit artikel wordt een tabel met de naam myTestSkipCol onder het dbo schema verwacht. U kunt deze tabel maken in een voorbeelddatabase, zoals WideWorldImporters of AdventureWorks of in een andere database. Maak deze tabel als volgt:

USE WideWorldImporters;  
GO  
CREATE TABLE myTestSkipCol  
   (  
   Col1 smallint,  
   Col2 nvarchar(50) NULL,  
   Col3 nvarchar(50) not NULL  
   );  
GO

In de voorbeelden in dit artikel wordt ook een voorbeeldgegevensbestand myTestSkipCol2.datgebruikt. Dit gegevensbestand bevat slechts twee velden, hoewel de doeltabel drie kolommen bevat.

1,DataForColumn3  
1,DataForColumn3  
1,DataForColumn3

Basisstappen

U kunt een bestand met een niet-XML-indeling of een XML-indelingsbestand gebruiken om een tabelkolom over te slaan. In beide gevallen zijn er twee stappen:

1. Gebruik het opdrachtregelprogramma bcp om een standaardindelingsbestand te maken.
2. Wijzig het standaardindelingsbestand in een teksteditor.

Het gewijzigde indelingsbestand moet elk bestaand veld toewijzen aan de bijbehorende kolom in de doeltabel. Er moet ook worden aangegeven welke tabelkolom of -kolommen moeten worden overgeslagen.

Als u bijvoorbeeld gegevens bulksgewijs wilt importeren uit myTestSkipCol2.dat in de myTestSkipCol tabel, moet het indelingsbestand het eerste gegevensveld toewijzen aan Col1, Col2overslaan en het tweede veld toewijzen aan Col3.

Optie 1: een niet-XML-indelingsbestand gebruiken

Stap 1: een standaardbestand met een niet-XML-indeling maken

Maak een standaardbestand met een niet-XML-indeling voor de myTestSkipCol voorbeeldtabel door de volgende bcp opdracht uit te voeren bij de opdrachtprompt:

bcp WideWorldImporters..myTestSkipCol format nul -f myTestSkipCol_Default.fmt -c -T

Belangrijk

Mogelijk moet u de naam opgeven van het serverexemplaar waar u verbinding mee maakt met het argument -S. Mogelijk moet u ook de gebruikersnaam en het wachtwoord opgeven met de argumenten -U en -P. Zie bcp Utilityvoor meer informatie.

Met de vorige opdracht maakt u een bestand met een niet-XML-indeling, myTestSkipCol_Default.fmt. Dit indelingsbestand wordt een standaardindelingsbestand genoemd omdat het het formulier is dat wordt gegenereerd door bcp. Een standaardindelingsbestand beschrijft een een-op-een-correspondentie tussen gegevensbestandsvelden en tabelkolommen.

In de volgende schermopname ziet u de waarden in dit voorbeeldbestand met de standaardindeling.

Notitie

Voor meer informatie over de indelingsvelden, zie niet-XML-indelingsbestanden (SQL Server).

Stap 2: een bestand met een niet-XML-indeling wijzigen

Als u een standaardbestand met een niet-XML-indeling wilt wijzigen, zijn er twee alternatieven. Een van beide opties geeft aan dat het gegevensveld niet bestaat in het gegevensbestand en dat er geen gegevens moeten worden ingevoegd in de bijbehorende tabelkolom.

Als u een tabelkolom wilt overslaan, bewerkt u het standaardbestand met een niet-XML-indeling en wijzigt u het bestand met een van de volgende alternatieve methoden:

Optie 1: de rij verwijderen

De voorkeursmethode voor het overslaan van een kolom omvat de volgende drie stappen:

1. Verwijder eerst een indelingsbestandsrij die een veld beschrijft dat ontbreekt in het brongegevensbestand.
2. Verlaag vervolgens de hostbestand veldvolgorderwaarde van elke indelingsbestandsrij die volgt op een verwijderde rij. Het doel is opeenvolgende waarden voor de volgorde van hostbestandsvelden, 1 tot en met en, die de werkelijke positie van elk gegevensveld in het gegevensbestand weerspiegelen.
3. Verminder ten slotte de waarde in het veld Aantal kolommen om het werkelijke aantal velden in het gegevensbestand weer te geven.

Het volgende voorbeeld is gebaseerd op de standaardbestandsindeling voor tabel myTestSkipCol. Dit gewijzigde indelingsbestand wijst het eerste gegevensveld toe aan Col1, slaat Col2over en wijst het tweede gegevensveld toe aan Col3. De rij voor Col2 is verwijderd. Het scheidingsteken na het eerste veld is eveneens gewijzigd van \t in ,.

14.0  
2  
1       SQLCHAR       0       7       ","      1     Col1         ""  
2       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

Optie 2: de rijdefinitie wijzigen

Als u een tabelkolom wilt overslaan, kunt u ook de definitie wijzigen van de rij format-file die overeenkomt met de tabelkolom. In deze rij met indelingsbestanden moeten de waarden 'lengte van voorvoegsel', 'lengte van hostbestandsgegevens' en 'serverkolomvolgorde' worden ingesteld op 0. Bovendien moeten de velden 'afsluiter' en 'kolomsortering' worden ingesteld op '' (dat wil gezegd, op een lege waarde of NULL-waarde). De waarde voor de serverkolomnaam vereist een niet-lege tekenreeks, hoewel de werkelijke kolomnaam niet nodig is. Voor de resterende notatievelden zijn de standaardwaarden vereist.

Het volgende voorbeeld is ook afgeleid van het standaardformatbestand voor de myTestSkipCol tabel.

14.0  
3  
1       SQLCHAR       0       7       ","      1     Col1         ""  
2       SQLCHAR       0       0       ""       0     Col2         ""  
3       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

Voorbeelden met een niet-XML-bestandsformaat

De volgende voorbeelden zijn gebaseerd op de myTestSkipCol voorbeeldtabel en het myTestSkipCol2.dat voorbeeldgegevensbestand dat eerder in dit artikel wordt beschreven.

BULK INSERT gebruiken

In dit voorbeeld wordt gebruikgemaakt van een van de gewijzigde bestanden in niet-XML-indeling die waren gemaakt, zoals beschreven in de vorige sectie. In dit voorbeeld heeft het gewijzigde indelingsbestand de naam myTestSkipCol2.fmt. Als u BULK INSERT wilt gebruiken om het myTestSkipCol2.dat gegevensbestand bulksgewijs te importeren, voert u in SQL Server Management Studio (SSMS) de volgende code uit. Werk de bestandssysteempaden bij voor de locatie van de voorbeeldbestanden op uw computer.

USE WideWorldImporters;  
GO  
BULK INSERT myTestSkipCol  
   FROM 'C:\myTestSkipCol2.dat'  
   WITH (FORMATFILE = 'C:\myTestSkipCol2.fmt');  
GO  
SELECT * FROM myTestSkipCol;  
GO

Optie 2: een XML-indelingsbestand gebruiken

Stap 1: een standaard-XML-indelingsbestand maken

Maak een standaard-XML-indelingsbestand voor de myTestSkipCol voorbeeldtabel door de volgende bcp opdracht uit te voeren bij de opdrachtprompt:

bcp WideWorldImporters..myTestSkipCol format nul -f myTestSkipCol_Default.xml -c -x -T

Belangrijk

Mogelijk moet u de naam opgeven van het serverexemplaar waarbij u verbinding maakt met het argument -S. Mogelijk moet u ook de gebruikersnaam en het wachtwoord opgeven met de argumenten -U en -P. Zie bcp Utilityvoor meer informatie.

Met de vorige opdracht maakt u een XML-indelingsbestand myTestSkipCol_Default.xml. Dit indelingsbestand wordt een standaardindelingsbestand genoemd omdat het het formulier is dat wordt gegenereerd door bcp-. Een standaardindelingsbestand beschrijft een een-op-een-correspondentie tussen gegevensbestandsvelden en tabelkolommen.

<?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="\t" MAX_LENGTH="7"/>  
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
</RECORD>  
<ROW>  
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>  
  <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>  
  <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLNVARCHAR"/>  
</ROW>  
</BCPFORMAT>

Notitie

Zie XML-indelingsbestanden (SQL Server)voor meer informatie over de structuur van deze bestanden.

Stap #2 - een XML-indelingsbestand wijzigen

Hier is het gewijzigde XML-indelingsbestand, myTestSkipCol2.xml, dat Col2overslaat. De FIELD- en ROW vermeldingen voor Col2 zijn verwijderd en de vermeldingen zijn opnieuw genummerd. Ook is het scheidingsteken na het eerste veld gewijzigd van \t in ,.

<?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="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
</RECORD>  
<ROW>  
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>  
  <COLUMN SOURCE="2" NAME="Col3" xsi:type="SQLNVARCHAR"/>  
</ROW>  
</BCPFORMAT>

Voorbeelden met een XML-indelingsbestand

De volgende voorbeelden zijn gebaseerd op de myTestSkipCol voorbeeldtabel en het myTestSkipCol2.dat voorbeeldgegevensbestand dat eerder in dit artikel wordt beschreven.

Als u de gegevens uit myTestSkipCol2.dat in de myTestSkipCol tabel wilt importeren, worden in de voorbeelden het gewijzigde XML-indelingsbestand myTestSkipCol2.xmlgebruikt.

BULK INSERT gebruiken met een weergave

Met een XML-formaatbestand kunt u een kolom niet overslaan wanneer u rechtstreeks in een tabel importeert met de opdracht bcp of een BULK INSERT instructie. U kunt echter alleen importeren in de laatste kolom van een tabel. Als u een andere kolom dan de laatste kolom moet overslaan, moet u een weergave maken van de doeltabel die alleen de kolommen in het gegevensbestand bevat. Vervolgens kunt u gegevens uit dat bestand bulksgewijs importeren in de weergave.

Bij het volgende voorbeeld wordt de v_myTestSkipCol-weergave op de myTestSkipCol-tabel gemaakt. In deze weergave wordt de tweede tabelkolom Col2overgeslagen. In het voorbeeld wordt vervolgens BULK INSERT gebruikt om het myTestSkipCol2.dat gegevensbestand in deze weergave te importeren.

Voer in SSMS de volgende code uit. Werk de bestandssysteempaden bij voor de locatie van de voorbeeldbestanden op uw computer.

USE WideWorldImporters;  
GO

CREATE VIEW v_myTestSkipCol AS  
    SELECT Col1,Col3  
    FROM myTestSkipCol;  
GO

BULK INSERT v_myTestSkipCol  
FROM 'C:\myTestSkipCol2.dat'  
WITH (FORMATFILE='C:\myTestSkipCol2.xml');  
GO

OPENROWSET(BULK...) gebruiken

Als u een XML-indelingsbestand wilt gebruiken om een tabelkolom over te slaan met behulp van OPENROWSET(BULK...), moet u als volgt een expliciete lijst met kolommen opgeven in de selectielijst en ook in de doeltabel:

INSERT ...<column_list> SELECT <column_list> FROM OPENROWSET(BULK...)

In het volgende voorbeeld wordt de bulkrijsetprovider OPENROWSET en het myTestSkipCol2.xml opmaakbestand gebruikt. In het voorbeeld wordt het myTestSkipCol2.dat gegevensbestand bulksgewijs geïmporteerd in de myTestSkipCol tabel. De instructie bevat een expliciete lijst met kolommen in de selectielijst en ook in de doeltabel, indien nodig.

Voer in SSMS de volgende code uit. Werk de bestandssysteempaden bij voor de locatie van de voorbeeldbestanden op uw computer.

USE WideWorldImporters;  
GO  
INSERT INTO myTestSkipCol  
  (Col1,Col3)  
    SELECT Col1,Col3  
      FROM  OPENROWSET(BULK  'C:\myTestSkipCol2.Dat',  
      FORMATFILE='C:\myTestSkipCol2.Xml'  
       ) as t1 ;  
GO

Volgende stappen

• bcp Utility
• BULK INSERT (Transact-SQL)
• OPENROWSET (Transact-SQL)
• Een indelingsbestand gebruiken om een gegevensveld (SQL Server) over te slaan
• Een indelingsbestand gebruiken om tabelkolommen toe te wijzen aan Data-File velden (SQL Server)
• een indelingsbestand gebruiken om gegevens bulksgewijs te importeren (SQL Server)