Mantenere i valori Null o i valori predefiniti durante un'importazione in blocco (SQL Server)

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse AnalyticsAnalytics Platform System (PDW)

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. Ad esempio, se un file di dati contiene un campo Null, verrà caricato nel campo il valore predefinito della colonna. Il comando bcp e l'istruzione BULK INSERT consentono entrambi di specificare che dovranno essere mantenuti i valori Null.

Al contrario, un'istruzione regolare INSERT mantiene il valore Null anziché inserire un valore predefinito. INSERT ... L'istruzione SELECT * FROM OPENROWSET BULK fornisce lo stesso comportamento di base del normale INSERT, ma supporta inoltre un hint di tabella per l'inserimento dei valori predefiniti.

Mantenere i valori null

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. Per OPENROWSET BULK, per impostazione predefinita, tutte le colonne non specificate nell'operazione di caricamento bulk vengono impostate su NULL.

Comando	Qualificatore	Tipo di qualificatore
bcp	-k	Interruttore
BULK INSERT	KEEPNULLS*	Argomento
INSERT ... SELECT * FROM OPENROWSET(BULK...)	N/D	N/D

* Per BULK INSERT (Transact-SQL), se i valori predefiniti non sono disponibili, la colonna della tabella deve essere definita per consentire valori Null.

Nota

Questi qualificatori disabilitano il controllo delle DEFAULT definizioni su una tabella tramite questi comandi di importazione in blocco. Tuttavia, per eventuali istruzioni simultanee INSERT, sono previste le definizioni DEFAULT.

Usare i valori predefiniti con 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). Per usare i valori predefiniti, usare hint di tabella.

Per altre informazioni, vedere OPENROWSET BULK.

Condizioni di test di esempio

Negli esempi vengono usati i file di database e di formato creati in questo articolo.

Modificare il percorso del file locale dell'esempio di codice in un percorso di file nel computer.

Tabella di esempio

Lo script crea un database di test e una tabella denominata myNulls. La quarta colonna della tabella, Kids, ha un valore predefinito. Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL 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 esempio

Usando Blocco note, creare un file D:\BCP\myNulls.bcp vuoto e inserire i dati di esempio seguenti. Non esiste alcun valore nel terzo record, quarta colonna.

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:

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 esempio

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. Per altre informazioni, vedere Usare file in formato non XML (SQL Server).

Il comando seguente userà l'utilità bcp per generare un file di formato non XML, , myNulls.fmtin base allo schema di 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.
• L'opzione di formattazione richiede anche l'opzione -f.
• c viene usato per specificare i dati di tipo testo
• t, viene usato per specificare una virgola come carattere di terminazione del campo
• T viene usato per specificare una connessione attendibile tramite la sicurezza integrata.

Al prompt dei comandi immettere il comando seguente:

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. 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

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

Mantenere i valori Null o usare i valori predefiniti durante l'importazione di massa

Negli esempi vengono usati i file di database, file di dati e formato creati in questo articolo.

Usare bcp e mantenere i valori Null senza un file di formato

L'interruttore -k.

Al prompt dei comandi immettere il comando seguente:

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;"

Usare bcp e mantenere i valori Null con un file di formato non XML

Gli -k e -f interruttori.

Al prompt dei comandi immettere il comando seguente:

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;"

Usare bcp e i valori predefiniti senza un file di formato

Al prompt dei comandi immettere il comando seguente:

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;"

Usare bcp e i valori predefiniti con un file di formato non XML

L'interruttore -f.

Al prompt dei comandi immettere il comando seguente:

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;"

Usare BULK INSERT e mantenere i valori Null senza un file di formato

Argomento KEEPNULLS.

Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL 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;

Usare BULK INSERT e mantenere valori Null con un file di formato non XML

L'oggetto KEEPNULLS e l'argomento FORMATFILE.

Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL 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;

Usare BULK INSERT e usare i valori predefiniti senza un file di formato

Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL 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;

Usare BULK INSERT e i valori predefiniti con un file di formato non XML

Argomento FORMATFILE.

Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL 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;

Usare OPENROWSET BULK e mantenere valori Null con un file di formato non XML

Argomento FORMATFILE.

Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL 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;

Usare OPENROWSET BULK e mantenere i valori predefiniti con un file di formato non XML

Il suggerimento della tabella KEEPDEFAULTS e l'argomento FORMATFILE.

Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL 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;

Attività correlate

• Mantenere i valori Identity durante l'importazione in blocco dei dati (SQL Server)

• Preparare i dati per l'importazione o l'esportazione in blocco

Per utilizzare un file di formato

• Creazione di un file di formato con bcp (SQL Server)

• Usare un file di formato per l'importazione in blocco dei 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)

• Usare un file di formato per escludere un campo dati (SQL Server)

• Usare un file di formato per ignorare una colonna di una tabella (SQL Server)

Per utilizzare formati dei dati per l'importazione o l'esportazione in blocco

• Importare dati in formato nativo e carattere da versioni precedenti di SQL Server

• Usare il formato carattere per importare o esportare dati (SQL Server)

• Usare il formato nativo per importare o esportare dati (SQL Server)

• Usare il formato carattere Unicode per importare o esportare dati (SQL Server)

• Uso del formato Unicode nativo per importare o esportare dati (SQL Server)

Per specificare i formati di dati per la compatibilità mediante bcp

• Specificare i caratteri di terminazione dei campi e delle righe (SQL Server)

• Specificare la lunghezza del prefisso nei file di dati usando bcp (SQL Server)

• Specificare il tipo di archiviazione file usando bcp (SQL Server)

Contenuti correlati

• BACKUP (Transact-SQL)
• OPENROWSET (Transact-SQL)
• OPENROWSET BULK (Transact-SQL)
• Utilità bcp
• BULK INSERT (Transact-SQL)
• Hint di tabella (Transact-SQL)