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

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse AnalyticsPiattaforma di sistemi analitici (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.

Un'istruzione INSERT regolare mantiene invece il valore Null anziché inserire un valore predefinito. 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.

Struttura

Mantenimento dei valori Null Uso dei valori predefiniti con INSERT ... SELECT * FROM OPENROWSET(BULK...). Condizioni di test di esempio  ● Tabella di esempio  ● File di dati di esempio  ● File di formato non XML di esempio Mantenere i valori Null o usare i valori predefiniti durante un'importazione bulk  ● Uso di bcp e mantenimento dei valori Null senza un file di formato  ● Uso di bcp e mantenimento dei valori Null con un file di formato non XML  ● Uso di bcp e dei valori predefiniti senza un file di formato  ● Uso di bcp e dei valori predefiniti con un file di formato non XML  ● Uso di BULK INSERT e mantenimento dei valori Null senza un file di formato  ● Uso di BULK INSERT e mantenimento dei valori Null con un file di formato non XML  ● Uso di BULK INSERT e dei valori predefiniti senza un file di formato  ● Uso di BULK INSERT e dei valori predefiniti con un file di formato non XML  ● Uso di OPENROWSET(BULK...) e mantenimento dei valori Null con un file di formato non XML  ● Uso di OPENROWSET(BULK...) e dei valori predefiniti con un file di formato non XML

Mantenimento dei 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 impostazione predefinita, per OPENROWSETle colonne non specificate nell'operazione di importazione vengono impostate su NULL.

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

● Per BULK INSERT, se i valori predefiniti non sono disponibili, è necessario impostare la colonna della tabella in modo da consentire valori Null.

Nota

Questi qualificatori disabilitano il controllo delle definizioni DEFAULT di una tabella mediante i comandi per l'importazione bulk. Per le istruzioni INSERT simultanee, le definizioni DEFAULT sono tuttavia previste.

Uso dei 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, specificare l'hint di tabella KEEPDEFAULTS.

Nota

Per ulteriori informazioni, vedere INSERT (Transact-SQL), SELECT (Transact-SQL), OPENROWSET (Transact-SQL) e Hint di tabella (Transact-SQL)

Condizioni di test di esempio

Gli esempi riportati in questo argomento sono basati sulla tabella, il file di dati e il file di formato definiti di seguito.

Tabella di esempio

Lo script seguente crea un database di prova e una tabella denominata myNulls. Si noti che 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 il Blocco note, creare un file D:\BCP\myNulls.bcp vuoto e inserire i dati seguenti. Si noti che nel terzo record della quarta colonna non è presente alcun valore.

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 informazioni dettagliate, vedere File in formato non XML (SQL Server) . Il comando seguente userà l' utility bcp per generare un formato di file non XML, myNulls.fmt, sulla base dello 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 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.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 informazioni sulla creazione di file di formato, vedere Creare un file di formato (SQL Server).

Mantenimento dei valori Null o utilizzo dei valori predefiniti durante un'importazione bulk

Gli esempi seguenti usano il database, il file di dati e i file di formato creati in precedenza.

Uso di bcp e mantenimento dei valori Null senza un file di formato

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

Uso di bcp e mantenimento dei valori Null con un file di formato non XML

Opzioni-k e -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 -k

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

Uso di bcp e dei 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;"

Uso di bcp e dei valori predefiniti con un file di formato non XML

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

Uso di BULK INSERT e mantenimento dei valori Null senza un file di formato

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

Uso di BULK INSERT e mantenimento dei valori Null con un file di formato non XML

ArgomentiKEEPNULLS e 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;

Uso di BULK INSERT e dei 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;

Uso di BULK INSERT e dei valori predefiniti con un file di formato non XML

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

Uso di OPENROWSET(BULK...) e mantenimento dei valori Null con un file di formato non XML

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

Uso di OPENROWSET(BULK...) e dei valori predefiniti con un file di formato non XML

Hint di tabellaKEEPDEFAULTS e 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 bulk dei dati (SQL Server)

• Preparazione dei dati per l'importazione o l'esportazione bulk (SQL Server)

Per utilizzare un file di formato

• Creazione di un file di formato (SQL Server)

• Usare un file di formato per l'importazione bulk 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 di dati per l'importazione o l'esportazione bulk

• 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

• Impostazione dei caratteri di terminazione del campo e della riga (SQL Server)

• Specifica della lunghezza del prefisso nei file di dati tramite bcp (SQL Server)

• Specifica del tipo di archiviazione di file tramite bcp (SQL Server)

Vedi anche

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