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

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics 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 regular INSERT conserva el valor NULL en lugar de insertar un valor predeterminado. Insert ... LA instrucción SELECT * FROM OPENROWSET BULK proporciona el mismo comportamiento básico que INSERT normal, pero además admite una sugerencia de tabla para insertar los valores predeterminados.

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. Para OPENROWSET BULK, de forma predeterminada, las columnas que no se especifican en la operación de carga masiva se establecen NULLen .

Comando	Calificador:	Tipo de calificador
bcp	-k	Conmutador
BULK INSERT	KEEPNULLS*	Argumento
INSERT ... SELECT * FROM OPENROWSET(BULK...)	N/D	N/D

* Para BULK INSERT (Transact-SQL), si los valores predeterminados no están disponibles, la columna de tabla debe definirse para permitir valores NULL.

Nota:

Estos calificadores deshabilitan la comprobación de DEFAULT definiciones en una tabla mediante estos comandos de importación masiva. Sin embargo, para las instrucciones simultáneas INSERT , DEFAULT se esperan definiciones.

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 sugerencias de tabla.

Para obtener más información, consulte OPENROWSET BULK.

Condiciones de prueba de ejemplo

En los ejemplos se usan la base de datos y los archivos de formato creados en este artículo.

Cambie la ubicación del archivo local del ejemplo de código a una ubicación de archivo en el equipo.

Tabla de ejemplo

El script crea una base de datos de prueba y una tabla denominada myNulls. La cuarta columna de tabla, Kids, tiene un valor predeterminado. Ejecute 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

Con el Bloc de notas, cree un archivo D:\BCP\myNulls.bcp vacío e inserte los siguientes datos de ejemplo. No hay ningún valor en el tercer registro, 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. Para obtener más información, vea Usar archivos de formato no XML (SQL Server).

El siguiente comando usará la utilidad bcp para generar un archivo de formato no XML, , myNulls.fmtbasado en el esquema de myNulls.

• Para usar un comando bcp para crear un archivo de formato, especifique el argumento format y use nul en lugar de una ruta de acceso de archivo de datos.
• La opción Format también requiere la opción -f.
• c se usa para especificar datos de caracteres
• t, se usa para especificar una coma como terminador de campo
• T se usa para especificar una conexión de confianza mediante la 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 con bcp (SQL Server).

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

En los ejemplos se usan la base de datos, el archivo de datos y los archivos de formato creados en este artículo.

Usar bcp y mantener valores NULL sin un archivo de formato

El -k modificador.

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

Los -k modificadores 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 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 valores predeterminados con un archivo de formato no XML

El -f modificador.

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

Argumento KEEPNULLS.

Ejecute 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

y KEEPNULLS el FORMATFILE argumento .

Ejecute 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

Ejecute 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 valores predeterminados con un archivo de formato no XML

Argumento FORMATFILE.

Ejecute 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

Argumento FORMATFILE.

Ejecute 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 mantener los valores predeterminados con un archivo de formato no XML

Sugerencia KEEPDEFAULTS de tabla y FORMATFILE argumento.

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

Tareas relacionadas

• Mantenimiento de valores de identidad al importar datos en bloque (SQL Server)

• Preparación de los datos para la exportación o importación en bloque

Para usar un archivo de formato

• Creación de un archivo de formato con bcp (SQL Server)

• Uso de un archivo de formato para la importación en bloque de datos (SQL Server)

• Uso de un archivo de formato para asignar columnas de tabla a campos de un archivo de datos (SQL Server)

• Uso de un archivo de formato para omitir un campo de datos (SQL Server)

• Usar un archivo de formato para omitir una columna de tabla (SQL Server)

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

• Importación de datos de formato nativo y de caracteres de versiones anteriores de SQL Server

• Uso del formato de caracteres para importar o exportar datos (SQL Server)

• Uso del formato nativo para importar o exportar datos (SQL Server)

• Uso del formato de caracteres Unicode para importar o exportar datos (SQL Server)

• Usar el formato nativo Unicode para importar o exportar datos (SQL Server)

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

• Especificar terminadores de campo y fila (SQL Server)

• Especificar la longitud del prefijo en los archivos de datos mediante bcp (SQL Server)

• Especificar el tipo de almacenamiento de archivos mediante bcp (SQL Server)

Contenido relacionado

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