Condividi tramite


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

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics Analytics 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.

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

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

Per utilizzare un file di formato

Per utilizzare formati di dati per l'importazione o l'esportazione bulk

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

Vedi anche

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