Mantenimento dei valori Null o utilizzo dei valori predefiniti durante un'importazione bulk (SQL Server)Keep Nulls or Use Default Values During Bulk Import (SQL Server)

QUESTO ARGOMENTO SI APPLICA A: sìSQL Server (a partire dalla versione 2008)sìDatabase SQL di AzuresìAzure SQL Data Warehouse sìParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Per impostazione predefinita, durante l'importazione di dati in una tabella il comando bcp e l'istruzione BULK INSERT osservano gli eventuali valori predefiniti che sono stati specificati per le colonne della tabella.By default, when data is imported into a table, the bcp command and BULK INSERT statement observe any defaults that are defined for the columns in the table. Ad esempio, se un file di dati contiene un campo Null, verrà caricato nel campo il valore predefinito della colonna.For example, if there is a null field in a data file, the default value for the column is loaded instead. Il comando bcp e l'istruzione BULK INSERT consentono entrambi di specificare che dovranno essere mantenuti i valori Null.The bcp command and BULK INSERT statement both allow you to specify that nulls values be retained.

Un'istruzione INSERT regolare mantiene invece il valore Null anziché inserire un valore predefinito.In contrast, a regular INSERT statement retains the null value instead of inserting a default value. L'istruzione INSERT ... SELECT * FROM OPENROWSET(BULK...) funziona in modo analogo a un'istruzione INSERT regolare, ma supporta inoltre un hint di tabella per l'inserimento di valori predefiniti.The INSERT ... SELECT * FROM OPENROWSET(BULK...) statement provides the same basic behavior as regular INSERT but additionally supports a table hint for inserting the default values.

RiquadroOutline
Mantenimento dei valori NullKeeping Null Values
Uso dei valori predefiniti con INSERT ... SELECT * FROM OPENROWSET(BULK...)Using Default Values with INSERT ... SELECT * FROM OPENROWSET(BULK...)
Condizioni di test di esempioExample Test Conditions
 ● Tabella di esempio ● Sample Table
 ● File di dati di esempio ● Sample Data File
 ● File di formato non XML di esempio ● Sample Non-XML Format File
Mantenimento dei valori Null o utilizzo dei valori predefiniti durante un'importazione bulkKeep Nulls or Use Default Values During Bulk Import
 ● Uso di bcp e mantenimento dei valori Null senza un file di formato ● Using bcp and Keeping Null Values without a Format File
 ● Uso di bcp e mantenimento dei valori Null con un file di formato non XML ● Using bcp and Keeping Null Values with a Non-XML Format File
 ● Uso di bcp e dei valori predefiniti senza un file di formato ● Using bcp and Using Default Values without a Format File
 ● Uso di bcp e dei valori predefiniti con un file di formato non XML ● Using bcp and Using Default Values with a Non-XML Format File
 ● Uso di BULK INSERT e mantenimento dei valori Null senza un file di formato ● Using BULK INSERT and Keeping Null Values without a Format File
 ● Uso di BULK INSERT e mantenimento dei valori Null con un file di formato non XML ● Using BULK INSERT and Keeping Null Values with a Non-XML Format File
 ● Uso di BULK INSERT e dei valori predefiniti senza un file di formato ● Using BULK INSERT and Using Default Values without a Format File
 ● Uso di BULK INSERT e dei valori predefiniti con un file di formato non XML ● Using BULK INSERT and Using Default Values with a Non-XML Format File
 ● Uso di OPENROWSET(BULK...) e mantenimento dei valori Null con un file di formato non XML ● Using OPENROWSET(BULK...) and Keeping Null Values with a Non-XML Format File
 ● Uso di OPENROWSET(BULK...) e dei valori predefiniti con un file di formato non XML ● Using OPENROWSET(BULK...) and Using Default Values with a Non-XML Format File

Mantenimento dei valori NullKeeping Null Values

I qualificatori seguenti specificano che un campo vuoto del file di dati mantiene il relativo valore Null durante l'importazione bulk anziché ereditare un valore predefinito (se disponibile) per le colonne della tabella.The following qualifiers specify that an empty field in the data file retains its null value during the bulk-import operation, rather than inheriting a default value (if any) for the table columns. Per impostazione predefinita, per OPENROWSETle colonne non specificate nell'operazione di importazione vengono impostate su NULL.For OPENROWSET, by default, any columns that are not specified in the bulk-load operation are set to NULL.

CommandCommand QualifierQualifier Tipo di qualificatoreQualifier type
bcpbcp -k-k OpzioneSwitch
BULK INSERTBULK INSERT KEEPNULLS\KEEPNULLS**** ArgomentoArgument
INSERT ... SELECT * FROM OPENROWSET(BULK...).INSERT ... SELECT * FROM OPENROWSET(BULK...) N/DN/A N/DN/A

\* Per BULK INSERT, se i valori predefiniti non sono disponibili, è necessario impostare la colonna della tabella in modo da consentire valori Null.\* For BULK INSERT, if default values are not available, the table column must be defined to allow null values.

Nota

Questi qualificatori disabilitano il controllo delle definizioni DEFAULT di una tabella mediante i comandi per l'importazione bulk.These qualifiers disable checking of DEFAULT definitions on a table by these bulk-import commands. Per le istruzioni INSERT simultanee, le definizioni DEFAULT sono tuttavia previste.However, for any concurrent INSERT statements, DEFAULT definitions are expected.

Uso dei valori predefiniti con INSERT ... SELECT * FROM OPENROWSET(BULK...)Using Default Values with INSERT ... SELECT * FROM OPENROWSET(BULK...)

È possibile specificare che la colonna della tabella corrispondente a un campo vuoto del file di dati userà il relativo valore predefinito (se disponibile).You can specify that for an empty field in the data file, the corresponding table column uses its default value (if any). Per usare i valori predefiniti, specificare l'hint di tabella KEEPDEFAULTS.To use default values, use the table hint KEEPDEFAULTS.

Condizioni di test di esempioExample Test Conditions

Gli esempi riportati in questo argomento sono basati sulla tabella, il file di dati e il file di formato definiti di seguito.The examples in this topic are based on the table, data file, and format file defined below.

Tabella di esempioSample Table

Lo script seguente crea un database di prova e una tabella denominata myNulls.The script below creates a test database and a table named myNulls. Si noti che la quarta colonna della tabella, Kids, ha un valore predefinito.Notice that the fourth table column, Kids, has a default value. Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.myNulls ( 
   PersonID smallint not null,
   FirstName varchar(25),
   LastName varchar(30),
   Kids varchar(13) DEFAULT 'Default Value',
   BirthDate date
   );

File di dati di esempioSample Data File

Usando il Blocco note, creare un file D:\BCP\myNulls.bcp vuoto e inserire i dati seguenti.Using Notepad, create an empty file D:\BCP\myNulls.bcp and insert the data below. Si noti che nel terzo record della quarta colonna non è presente alcun valore.Note that there is no value in the third record, fourth column.

1,Anthony,Grosse,Yes,1980-02-23
2,Alica,Fatnowna,No,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:Alternatively, you can execute the following PowerShell script to create and populate the data file:

cls
# revise directory as desired
$dir = 'D:\BCP\';

$bcpFile = $dir + 'MyNulls.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,Yes,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,No,1963-11-14';
Add-Content -Path $bcpFile -Value '3,Stella,Rosenhain,,1992-03-02';

#Review content
Get-Content -Path $bcpFile;
Invoke-Item $bcpFile;

File di formato non XML di esempioSample Non-XML Format File

SQL Server supporta due tipi di file di formato, ovvero non XML e XML.SQL Server support two types of format file: non-XML format and XML format. Il formato non XML è il formato originale supportato dalle versioni precedenti di SQL Server.The non-XML format is the original format that is supported by earlier versions of SQL Server. Per informazioni dettagliate, vedere File in formato non XML (SQL Server) .Please review Non-XML Format Files (SQL Server) for detailed information. Il comando seguente userà l' utility bcp per generare un formato di file non XML, myNulls.fmt, sulla base dello schema di myNulls.The following command will use the bcp utility to generate a non-xml format file, myNulls.fmt, based on the schema of myNulls. Per usare un comando bcp per creare un file di formato, specificare l'argomento format e usare nul anziché un percorso del file di dati.To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. L'opzione format richiede anche l'opzione -f .The format option also requires the -f option. 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.In addition, for this example, the qualifier c is used to specify character data, t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. Al prompt dei comandi immettere il comando seguente:At a command prompt, enter the following command:

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

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

Importante

Verificare che il file di formato non XML termini con un ritorno a capo/avanzamento riga.Ensure your non-XML format file ends with a carriage return\line feed. In caso contrario, è possibile che venga visualizzato il messaggio di errore seguente:Otherwise you will likely receive the following error message:

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

Per informazioni sulla creazione di file di formato, vedere Creare un file di formato (SQL Server).For more information about creating format files, see Create a Format File (SQL Server).

Mantenimento dei valori Null o utilizzo dei valori predefiniti durante un'importazione bulkKeep Nulls or Use Default Values During Bulk Import

Gli esempi seguenti usano il database, il file di dati e i file di formato creati in precedenza.The examples below use the database, datafile, and format files created above.

Uso di bcp e mantenimento dei valori Null senza un file di formatoUsing bcp and Keeping Null Values without a Format File

Opzione-k .-k switch. Al prompt dei comandi immettere il comando seguente:At a command prompt, enter the following command:

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

REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -c -t, -T -k

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

Uso di bcp e mantenimento dei valori Null con un file di formato non XMLUsing bcp and Keeping Null Values with a Non-XML Format File

Opzioni-k e -f .-k and -f switches. Al prompt dei comandi immettere il comando seguente:At a command prompt, enter the following command:

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

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

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

Uso di bcp e dei valori predefiniti senza un file di formatoUsing bcp and Using Default Values without a Format File

Al prompt dei comandi immettere il comando seguente:At a command prompt, enter the following command:

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

REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -c -t, -T

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

Uso di bcp e dei valori predefiniti con un file di formato non XMLUsing bcp and Using Default Values with a Non-XML Format File

Opzione-f .-f switch. Al prompt dei comandi immettere il comando seguente:At a command prompt, enter the following command:

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

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

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

Uso di BULK INSERT e mantenimento dei valori Null senza un file di formatoUsing BULK INSERT and Keeping Null Values without a Format File

ArgomentoKEEPNULLS .KEEPNULLS argument. Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

USE TestDatabase;
GO
TRUNCATE TABLE dbo.myNulls; -- for testing
BULK INSERT dbo.myNulls
    FROM 'D:\BCP\myNulls.bcp'
    WITH (
        DATAFILETYPE = 'char',  
        FIELDTERMINATOR = ',',  
        KEEPNULLS
        );

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

Uso di BULK INSERT e mantenimento dei valori Null con un file di formato non XMLUsing BULK INSERT and Keeping Null Values with a Non-XML Format File

ArgomentiKEEPNULLS e FORMATFILE .KEEPNULLS and the FORMATFILE argument. Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

USE TestDatabase;
GO

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

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

Uso di BULK INSERT e dei valori predefiniti senza un file di formatoUsing BULK INSERT and Using Default Values without a Format File

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

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls;  -- for testing
BULK INSERT dbo.myNulls
   FROM 'D:\BCP\myNulls.bcp'
   WITH (
      DATAFILETYPE = 'char',  
      FIELDTERMINATOR = ','
      );

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

Uso di BULK INSERT e dei valori predefiniti con un file di formato non XMLUsing BULK INSERT and Using Default Values with a Non-XML Format File

ArgomentoFORMATFILE .FORMATFILE argument. Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

USE TestDatabase;
GO

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

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

Uso di OPENROWSET(BULK...) e mantenimento dei valori Null con un file di formato non XMLUsing OPENROWSET(BULK...) and Keeping Null Values with a Non-XML Format File

ArgomentoFORMATFILE .FORMATFILE argument. Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

USE TestDatabase;
GO

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

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

Uso di OPENROWSET(BULK...) e dei valori predefiniti con un file di formato non XMLUsing OPENROWSET(BULK...) and Using Default Values with a Non-XML Format File

Hint di tabellaKEEPDEFAULTS e argomento FORMATFILE .KEEPDEFAULTS table hint and FORMATFILE argument. Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

USE TestDatabase;
GO

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

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

Vedere ancheSee Also

BACKUP (Transact-SQL) BACKUP (Transact-SQL)
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
bcp Utility bcp Utility
BULK INSERT (Transact-SQL) BULK INSERT (Transact-SQL)
Hint di tabella (Transact-SQL)Table Hints (Transact-SQL)