Usare un file di formato per l'importazione in blocco dei dati (SQL Server)

Articolo
03/13/2024

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics Analytics Platform System (PDW)

In questo articolo viene illustrato l'utilizzo di un file di formato per operazioni di importazione in blocco. Un file di formato esegue il mapping dei campi del file di dati alle colonne della tabella. Per altre informazioni, vedi Creazione di un file di formato (SQL Server).

Operazioni preliminari

Affinché un file di formato funzioni con un file di dati di formato carattere Unicode, è necessario che tutti i campi di input siano stringhe di testo Unicode, ovvero stringhe Unicode di dimensioni fisse o che terminano con un carattere.
Per l'esportazione o l'importazione in blocchi di dati SQLXML usa uno dei tipi di dati seguenti nel file di formato:
- SQLCHAR o SQLVARCHAR (i dati vengono inviati nella tabella codici del client o nella tabella codici implicita delle regole di confronto)
- SQLNCHAR or SQLNVARCHAR (i dati vengono inviati come Unicode)
- SQLBINARY or SQLVARBIN (i dati vengono inviati senza conversione).
Azure SQL Database e Azure Synapse Analytics supportano solo bcp. Per altre informazioni, vedi:
Questa sintassi, incluso l’inserimento in blocco, non è supportata in Azure Synapse Analytics. In Azure Synapse Analytics e in altre integrazioni della piattaforma database cloud, lo spostamento dei dati si esegue tramite l'istruzione COPY in Azure Data Factory o usando istruzioni T-SQL, ad esempio COPY INTO e PolyBase.

Condizioni di test di esempio

Gli esempi di file di formato contenuti in questo argomento sono basati sulla tabella e sul file di dati definiti di seguito.

Tabella di esempio

Lo script seguente crea un database di prova e una tabella denominata myFirstImport. Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.MyFirstImport (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30),
   BirthDate Date
   );

File di dati di esempio

Usando il Blocco note, creare un file D:\BCP\myFirstImport.bcp vuoto e inserire i dati seguenti:

1,Anthony,Grosse,1980-02-23
2,Alica,Fatnowna,1963-11-14
3,Stella,Rosenhain,1992-03-02

In alternativa, è possibile eseguire lo script PowerShell seguente per creare e popolare il file di dati:

Clear-Host
# revise directory as desired

$dir = 'D:\BCP\';

$bcpFile = Join-Path -Path $dir -ChildPath 'MyFirstImport.bcp';

# Confirm directory exists

IF ((Test-Path -Path $dir) -eq 0)
{
    Write-Host "The path $dir does not exist; please create or modify the directory.";
    RETURN;
};

# Clear content, will error if file does not exist, can be ignored

Clear-Content -Path $bcpFile -ErrorAction SilentlyContinue;

# Add data

Add-Content -Path $bcpFile -Value '1,Anthony,Grosse,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,1963-11-14';
Add-Content -Path $bcpFile -Value '3,Stella,Rosenhain,1992-03-02';

# Review content

Get-Content -Path $bcpFile;
Notepad.exe $bcpfile;

Creazione dei file di formato

SQL Server supporta due tipi di file di formato, ovvero non XML e XML. Il formato non XML è il formato originale supportato dalle versioni precedenti di SQL Server.

Creazione di un file di formato non XML

Per informazioni dettagliate, vedi File in formato non XML (SQL Server). Il comando seguente userà l' utility bcp per generare un formato di file non XML, myFirstImport.fmt, sulla base dello schema di myFirstImport. Per usare un comando bcp per creare un file di formato, specificare l'argomento format e usare null anziché un percorso del file di dati. L'opzione format richiede anche l'opzione -f . Inoltre, in questo esempio il qualificatore c viene usato per specificare dati di tipo carattere, t, viene usato per specificare la virgola come carattere di terminazione del campoe T viene usato per specificare una connessione trusted che usa la sicurezza integrata. Al prompt dei comandi immettere il comando seguente:

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

REM Review file
Notepad D:\BCP\myFirstImport.fmt

Il file di formato non XML D:\BCP\myFirstImport.fmt dovrebbe essere simile a questo:

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       11      "\r\n"   4     BirthDate              ""

Importante

Verificare che il file di formato non XML termini con un ritorno a capo/avanzamento riga. In caso contrario, è possibile che venga visualizzato il messaggio di errore seguente:

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format file

Creazione di un file di formato XML

Per informazioni dettagliate, vedi File in formato XML (SQL Server). Il comando seguente userà l' utilità bcp per creare un file di formato XML, myFirstImport.xml, sulla base dello schema di myFirstImport. Per usare un comando bcp per creare un file di formato, specificare l'argomento format e usare null anziché un percorso del file di dati. L'opzione format richiede sempre l'opzione -f e per creare un file di formato XML è necessario anche specificare l'opzione -x . Inoltre, in questo esempio il qualificatore c viene usato per specificare dati di tipo carattere, t, viene usato per specificare la virgola come carattere di terminazione del campoe T viene usato per specificare una connessione trusted che usa la sicurezza integrata. Al prompt dei comandi immettere il comando seguente:

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

REM Review file
Notepad D:\BCP\myFirstImport.xml

Il file di formato XML D:\BCP\myFirstImport.xml dovrebbe essere simile al seguente:

<?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="11"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="FirstName" xsi:type="SQLVARCHAR"/>
  <COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARCHAR"/>
  <COLUMN SOURCE="4" NAME="BirthDate" xsi:type="SQLDATE"/>
</ROW>
</BCPFORMAT>

Usare un file di formato per l'importazione bulk dei dati

Gli esempi seguenti usano il database, il file di dati e i file di formato creati in precedenza.

Uso di bcp e di un file di formato non XML

Al prompt dei comandi immettere il comando seguente:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.MyFirstImport;"

REM Import data
bcp TestDatabase.dbo.myFirstImport IN D:\BCP\myFirstImport.bcp -f D:\BCP\myFirstImport.fmt -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.MyFirstImport"

Uso di bcp e di un file di formato XML

Al prompt dei comandi immettere il comando seguente:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.MyFirstImport;"

REM Import data
bcp TestDatabase.dbo.myFirstImport IN D:\BCP\myFirstImport.bcp -f D:\BCP\myFirstImport.xml -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.MyFirstImport;"

Uso di BULK INSERT e di un file di formato non XML

Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myFirstImport; -- (for testing)
BULK INSERT dbo.myFirstImport  
   FROM 'D:\BCP\myFirstImport.bcp'  
   WITH (FORMATFILE = 'D:\BCP\myFirstImport.fmt');  
GO

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

Uso di BULK INSERT e di un file di formato XML

Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myFirstImport; -- (for testing)
BULK INSERT dbo.myFirstImport  
   FROM 'D:\BCP\myFirstImport.bcp'  
   WITH (FORMATFILE = 'D:\BCP\myFirstImport.xml');  
GO

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

Uso di OPENROWSET(BULK...) e di un file di formato non XML

Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE myFirstImport; -- (for testing)
INSERT INTO dbo.myFirstImport
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myFirstImport.bcp',
        FORMATFILE = 'D:\BCP\myFirstImport.fmt'
        ) AS t1;
GO

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

Uso di OPENROWSET(BULK...) e di un file di formato XML

Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myFirstImport; -- (for testing)
INSERT INTO dbo.myFirstImport  
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myFirstImport.bcp',
        FORMATFILE = 'D:\BCP\myFirstImport.xml'  
       ) AS t1;
GO

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

Altri esempi

Creazione di un file di formato (SQL Server)
Usare un file di formato per ignorare una colonna di una tabella (SQL Server)
Usare un file di formato per escludere un campo dati (SQL Server)
Usare un file di formato per eseguire il mapping tra le colonne di tabella e i campi del file di dati (SQL Server)

Condividi tramite

Usare un file di formato per l'importazione in blocco dei dati (SQL Server)

Operazioni preliminari

Condizioni di test di esempio

Tabella di esempio

File di dati di esempio

Creazione dei file di formato

Creazione di un file di formato non XML

Creazione di un file di formato XML

Usare un file di formato per l'importazione bulk dei dati

Uso di bcp e di un file di formato non XML

Uso di bcp e di un file di formato XML

Uso di BULK INSERT e di un file di formato non XML

Uso di BULK INSERT e di un file di formato XML

Uso di OPENROWSET(BULK...) e di un file di formato non XML

Uso di OPENROWSET(BULK...) e di un file di formato XML

Altri esempi

Passaggi successivi

Commenti e suggerimenti

Risorse aggiuntive