Utilizzo di un file di formato per l'importazione bulk dei dati (SQL Server)

Articolo
06/25/2013

In questo argomento viene illustrato l'utilizzo di un file di formato per operazioni di importazione bulk. Il file di formato esegue il mapping dei campi del file di dati alle colonne della tabella. È possibile utilizzare un file in formato XML o non XML per eseguire un'importazione bulk dei dati quando si utilizza un comando bcp o un comando BULK INSERT o INSERT. Comando SELECT * FROM OPENROWSET(BULK...) Transact-SQL.

Importante
Affinché un file di formato sia funzionante con un file di dati di caratteri 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.

[!NOTA]

Se non si ha familiarità con i file di formato, vedere File in formato non XML (SQL Server) e File in formato XML (SQL Server).

Opzioni del file di formato per comandi di importazione bulk

Nella tabella seguente vengono riepilogate le opzioni del file di formato per ogni comando di importazione bulk.

Comando di caricamento bulk	Utilizzo dell'opzione del file di formato
BULK INSERT	FORMATFILE = 'format_file_path'
INSERT ... SELECT * FROM OPENROWSET(BULK...),	FORMATFILE = 'format_file_path'
bcp … in	-fformat_file

Per ulteriori informazioni, vedere Utilità bcp, BULK INSERT (Transact-SQL) o OPENROWSET (Transact-SQL).

[!NOTA]

Per eseguire l'esportazione o l'importazione bulk dei dati SQLXML, utilizzare uno dei tipi di dati seguenti nel file di formato: SQLCHAR o SQLVARYCHAR (i dati vengono inviati nella tabella codici del client o nella tabella codici implicita nelle regole di confronto), SQLNCHAR o SQLNVARCHAR (i dati vengono inviati come Unicode) oppure SQLBINARY o SQLVARYBIN (i dati vengono inviati senza conversione).

Esempi

Negli esempi di questa sezione viene illustrato come utilizzare i file di formato per l'importazione bulk di dati utilizzando il comando bcp e le istruzioni BULK INSERT e INSERT ... Istruzioni SELECT * FROM OPENROWSET(BULK...). Prima di eseguire uno degli esempi di importazione bulk, è necessario creare una tabella, un file di dati e un file di formato di esempio.

Tabella di esempio

Gli esempi richiedono che una tabella denominata myTestFormatFiles2 venga creata nel database di esempio AdventureWorks2012 in base allo schema dbo. Per creare la tabella, nell'editor di query di SQL Server Management Studio eseguire:

USE AdventureWorks2012;
GO
CREATE TABLE myTestFormatFiles (
   Col1 smallint,
   Col2 nvarchar(50),
   Col3 nvarchar(50),
   Col4 nvarchar(50)
   );
GO

File di dati di esempio

Nell'esempio viene utilizzato un file di dati di esempio, myTestFormatFiles-c.Dat, che include i record riportati di seguito. Per creare il file di dati, al prompt dei comandi di Microsoft Windows digitare:

10,Field2,Field3,Field4
15,Field2,Field3,Field4
46,Field2,Field3,Field4
58,Field2,Field3,Field4

File di formato di esempio

In alcuni esempi di questa sezione viene utilizzato un file di formato XML, myTestFormatFiles-f-x-c.Xml, mentre in altri esempi viene utilizzato un file di formato non XML. In entrambi i file di formato viene utilizzato il formato carattere e un carattere di terminazione del campo non predefinito (,).

File di formato non XML di esempio

Nell'esempio seguente viene utilizzato il comando bcp per generare un file di formato XML dalla tabella myTestFormatFiles. Il file myTestFormatFiles.Fmt contiene le informazioni seguenti:

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

Per utilizzare bcp con l'opzione format per creare questo file di formato, al prompt dei comandi di Windows digitare:

bcp AdventureWorks2012..MyTestFormatFiles format nul -c -t, -f myTestFormatFiles.Fmt -T

Per ulteriori informazioni sulla creazione di un file di formato, vedere Creazione di un file di formato (SQL Server).

File di formato XML di esempio

Nell'esempio seguente viene utilizzato il comando bcp per creare un file di formato XML dalla tabella myTestFormatFiles. Il file myTestFormatFiles.Xml contiene le informazioni seguenti:

<?xml version="1.0"?>
<BCPFORMAT xmlns="https://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="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" 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"/>
  <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

Per utilizzare bcp con l'opzione format per creare questo file di formato, al prompt dei comandi di Windows digitare:

bcp AdventureWorks2012..MyTestFormatFiles format nul -c -t, -x -f myTestFormatFiles.Xml -T

Utilizzo di bcp

Nell'esempio seguente viene utilizzato bcp per l'importazione bulk dei dati dal file di dati myTestFormatFiles-c.Dat nella tabella HumanResources.myTestFormatFiles del database di esempio. In questo esempio viene utilizzato un file di formato XML, MyTestFormatFiles.Xml, ed eventuali righe di tabella esistenti vengono eliminate prima dell'importazione del file di dati.

Al prompt dei comandi di Windows digitare:

bcp AdventureWorks2012..myTestFormatFiles in C:\myTestFormatFiles-c.Dat -f C:\myTestFormatFiles.Xml -T

[!NOTA]

Per ulteriori informazioni su questo comando, vedere Utilità bcp.

Utilizzo di BULK INSERT

Nell'esempio seguente viene utilizzato BULK INSERT per l'importazione bulk dei dati dal file di dati myTestFormatFiles-c.Dat nella tabella HumanResources.myTestFormatFiles del database di esempio AdventureWorks2012. In questo esempio viene utilizzato un file di formato non XML, MyTestFormatFiles.Fmt, ed eventuali righe di tabella esistenti vengono eliminate prima dell'importazione del file di dati.

Nell'editor di query di SQL Server Management Studio eseguire:

USE AdventureWorks2012;
GO
DELETE myTestFormatFiles;
GO
BULK INSERT myTestFormatFiles 
   FROM 'C:\myTestFormatFiles-c.Dat' 
   WITH (FORMATFILE = 'C:\myTestFormatFiles.Fmt');
GO
SELECT * FROM myTestFormatFiles;
GO

[!NOTA]

Per ulteriori informazioni su questa istruzione, vedere BULK INSERT (Transact-SQL).

Utilizzo del provider di set di righe con lettura bulk OPENROWSET

Nell'esempio seguente viene utilizzato INSERT ... SELECT * FROM OPENROWSET(BULK...) per l'importazione bulk dei dati dal file di dati myTestFormatFiles-c.Dat nella tabella HumanResources.myTestFormatFiles del database di esempio AdventureWorks. In questo esempio viene utilizzato un file di formato XML, MyTestFormatFiles.Xml, ed eventuali righe di tabella esistenti vengono eliminate prima dell'importazione del file di dati.

Nell'editor di query di SQL Server Management Studio eseguire:

USE AdventureWorks2012;
DELETE myTestFormatFiles;
GO
INSERT INTO myTestFormatFiles
    SELECT *
      FROM  OPENROWSET(BULK  'C:\myTestFormatFiles-c.Dat',
      FORMATFILE='C:\myTestFormatFiles.Xml'     
      ) as t1 ;
GO
SELECT * FROM myTestFormatFiles;
GO

Al termine dell'utilizzo della tabella di esempio, è possibile rimuoverla mediante l'istruzione seguente: