Usare un file di formato per l'importazione bulk dei dati (SQL Server)
Si applica a:
SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse AnalyticsPiattaforma di sistemi analitici (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)
Passaggi successivi
Commenti e suggerimenti
Presto disponibile: Nel corso del 2024 verranno gradualmente disattivati i problemi di GitHub come meccanismo di feedback per il contenuto e ciò verrà sostituito con un nuovo sistema di feedback. Per altre informazioni, vedere https://aka.ms/ContentUserFeedback.
Invia e visualizza il feedback per