Mantenimiento de valores NULL o predeterminados durante la importación en bloque (SQL Server)

Artículo
04/04/2023

Se aplica a:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

De manera predeterminada, cuando se importan datos en una tabla, el comando BCP y la instrucción BULK INSERT aplican los valores predeterminados definidos para las columnas de la tabla. Por ejemplo, si un archivo de datos contiene un campo NULL, en su lugar, se cargará el valor predeterminado para la columna. El comando BCP y la instrucción BULK INSERT permiten especificar que se mantengan los valores NULL.

Por el contrario, una instrucción INSERT normal mantiene el valor NULL en lugar de insertar un valor predeterminado. La instrucción INSERT ... La instrucción SELECT * FROM OPENROWSET(BULK...) proporciona el mismo comportamiento básico que la instrucción INSERT regular, pero además admite una sugerencia de tabla para insertar los valores predeterminados.

Esquema
Mantener valores NULL Usar valores predeterminados con INSERT... SELECT * FROM OPENROWSET(BULK...) Condiciones de prueba de ejemplo ● Tabla de ejemplo ● Archivo de datos de ejemplo ● Archivo de formato no XML de ejemplo Mantener valores NULL o usar valores predeterminados durante la importación masiva ● Usar bcp y mantener valores Null sin un archivo de formato ● Usar BCP y mantener valores NULL con un archivo de formato no XML ● Usar BCP y usar valores predeterminados sin un archivo de formato ● Usar BCP y usar valores predeterminados con un archivo de formato no XML ● Usar BULK INSERT y mantener valores NULL sin un archivo de formato ● Usar BULK INSERT y mantener valores NULL con un archivo de formato no XML ● Usar BULK INSERT y usar valores predeterminados sin un archivo de formato ● Usar BULK INSERT y usar valores predeterminados con un archivo de formato no XML ● Usar OPENROWSET(BULK...) y mantener valores NULL con un archivo de formato no XML ● Usar OPENROWSET(BULK...) y usar valores predeterminados con un archivo de formato no XML

Esquema

Mantener valores NULL

Los siguientes calificadores especifican que un campo vacío del archivo de datos mantiene su valor NULL durante la operación de importación masiva, en lugar de heredar un valor predeterminado (si existe) para las columnas de la tabla. De manera predeterminada, para OPENROWSET, cualquier columna no especificada en la operación de carga masiva se establece como NULL.

Get-Help	Qualifier	Tipo de calificador
BCP	-k	Switch
BULK INSERT	KEEPNULLS*	Argumento
INSERT ... SELECT * FROM OPENROWSET(BULK...)	N/D	N/D

*Para BULK INSERT, si no hay valores predeterminados disponibles, se debe definir la columna de la tabla para permitir valores null.

Nota

Estos calificadores deshabilitan la comprobación de definiciones DEFAULT en una tabla mediante los comandos de importación masiva. Sin embargo, para cualquier instrucción INSERT simultánea, se esperan definiciones DEFAULT.

Usar valores predeterminados con INSERT... SELECT * FROM OPENROWSET(BULK...)

Puede especificar que para un campo vacío del archivo de datos, la columna de tabla correspondiente use su valor predeterminado (si existe). Para usar valores predeterminados, use la sugerencia de tabla KEEPDEFAULTS.

Nota

Para obtener más información, vea INSERT (Transact-SQL),SELECT (Transact-SQL), OPENROWSET (Transact-SQL) y Sugerencias de tabla (Transact-SQL)

Condiciones de prueba de ejemplo

Los ejemplos de este tema se basan en la tabla, en el archivo de datos y en el archivo de formato definidos a continuación.

Tabla de ejemplo

El script siguiente crea una base de datos de prueba y una tabla llamada myNulls. Tenga en cuenta que la cuarta columna de la tabla, Kids, tiene un valor predeterminado. Ejecutar el siguiente Transact-SQL en 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
   );

Archivo de datos de ejemplo

Mediante el Bloc de notas, cree un archivo vacío D:\BCP\myNulls.bcp e inserte los datos siguientes. Tenga en cuenta que no hay ningún valor en el registro tercero, cuarta columna.

1,Anthony,Grosse,Yes,1980-02-23
2,Alica,Fatnowna,No,1963-11-14
3,Stella,Rosenhain,,1992-03-02

Como alternativa, puede ejecutar el siguiente script de PowerShell para crear y rellenar el archivo de datos:

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;

Archivo de formato no XML de ejemplo

SQL Server admite dos tipos de archivos de formato: XML y no XML. El formato no XML es el formato original compatible con versiones anteriores de SQL Server. Revise Archivos de formato no XML (SQL Server) para obtener información detallada. El siguiente comando hará uso de la utilidad BCP para generar un archivo de formato no XML, myNulls.fmt, basado en el esquema de myNulls. Si quiere usar un comando BCP para crear un archivo de formato, especifique el argumento format y use null en lugar de una ruta de acceso de archivo de datos. La opción format también requiere la opción -f . Además, en este ejemplo, el calificador c se usa para especificar los datos de caracteres, t, se usa para especificar una coma como terminador de campoy T se usa para especificar una conexión de confianza que usa seguridad integrada. En el símbolo del sistema, escriba el siguiente comando:

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

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

Importante

Asegúrese de que el archivo de formato no XML termina con un retorno de carro o avance de línea. De lo contrario, es probable que reciba el mensaje de error siguiente:

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

Para obtener más información sobre cómo crear archivos de formato, vea Crear un archivo de formato (SQL Server).

Mantener valores NULL o usar valores predeterminados durante la importación masiva

Los ejemplos siguientes usan la base de datos, el archivo de datos y los archivos de formato que se han creado anteriormente.

Usar BCP y mantener valores NULL sin un archivo de formato

Modificador -k . En el símbolo del sistema, escriba el siguiente comando:

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

Usar BCP y mantener valores NULL con un archivo de formato no XML

Modificadores -k y -f . En el símbolo del sistema, escriba el siguiente comando:

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

Usar BCP y usar valores predeterminados sin un archivo de formato

En el símbolo del sistema, escriba el siguiente comando:

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

Usar BCP y usar valores predeterminados con un archivo de formato no XML

Modificador -f . En el símbolo del sistema, escriba el siguiente comando:

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

Usar BULK INSERT y mantener valores NULL sin un archivo de formato

ArgumentoKEEPNULLS . Ejecutar el siguiente Transact-SQL en 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;

Usar BULK INSERT y mantener valores NULL con un archivo de formato no XML

El argumentoKEEPNULLS y FORMATFILE . Ejecutar el siguiente Transact-SQL en 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;

Usar BULK INSERT y usar valores predeterminados sin un archivo de formato

Ejecutar el siguiente Transact-SQL en 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;

Usar BULK INSERT y usar valores predeterminados con un archivo de formato no XML

ArgumentoFORMATFILE . Ejecutar el siguiente Transact-SQL en 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;

Usar OPENROWSET(BULK...) y mantener valores NULL con un archivo de formato no XML

ArgumentoFORMATFILE . Ejecutar el siguiente Transact-SQL en 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;

Usar OPENROWSET(BULK...) y usar valores predeterminados con un archivo de formato no XML

Sugerencia de tablaKEEPDEFAULTS y argumento FORMATFILE . Ejecutar el siguiente Transact-SQL en 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;

Related Tasks

Para usar un archivo de formato

Para usar formatos de datos para la importación o exportación masivas

Para especificar formatos de datos por razones de compatibilidad cuando se usa bcp

Consulte también

BACKUP (Transact-SQL)
OPENROWSET (Transact-SQL)
bcp (utilidad)
BULK INSERT (Transact-SQL)
Sugerencias de tabla (Transact-SQL)