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

Articolo
06/13/2017

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 usare un file di formato non XML o XML per importare in blocco i dati quando si usa un comando bcp o un elemento BULK INSERT o INSERT ... SELECT * FROM OPENROWSET(BULK...) Comando Transact-SQL.

Importante

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

Nota

Se non si ha familiarità con i file di formato, vedere File di formato non XML (SQL Server) e file di 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 ... Pollici	-fformat_file

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

Nota

Per eseguire l'esportazione o l'importazione bulk di 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).

Esempio

Gli esempi di questa sezione illustrano come usare i file di formato per importare in blocco i dati usando il comando bcp e bulk INSERT e INSERT ... SELECT * FROM OPENROWSET(BULK...) istruzioni. 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 myTestFormatFiles venga creata nel database di esempio AdventureWorks2012 nello schema dbo . Per creare questa tabella, in SQL Server Management Studio Editor di query 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 immettere:

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

L'esempio seguente usa 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 usare 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 altre informazioni sulla creazione di un file di formato, vedere Creare un file di formato (SQL Server).

File di formato XML di esempio

L'esempio seguente viene usa 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 usare 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 myTestFormatFiles-c.Dat seguente viene usato bcp per importare in blocco i dati dal file di dati nella HumanResources.myTestFormatFiles tabella nel 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 altre informazioni su questo comando, vedere Utilità bcp.

Utilizzo di BULK INSERT

Nell'esempio myTestFormatFiles-c.Dat seguente viene usato BULK INSERT per importare in blocco i dati dal file di dati nella HumanResources.myTestFormatFiles tabella nel 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.

In SQL Server Management Studio Editor di query 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 altre 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.

In SQL Server Management Studio Editor di query 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: