Mantener valores NULL o usar valores predeterminados durante la importación masiva (SQL Server)Keep Nulls or Use Default Values During Bulk Import (SQL Server)

En este tema se aplica a: SíSQL ServerSíbase de datos de SQL AzureSíalmacenamiento de datos de SQL Azure Sí Almacenamiento de datos paralelosTHIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

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.By default, when data is imported into a table, the bcp command and BULK INSERT statement observe any defaults that are defined for the columns in the table. Por ejemplo, si un archivo de datos contiene un campo NULL, en su lugar, se cargará el valor predeterminado para la columna.For example, if there is a null field in a data file, the default value for the column is loaded instead. El comando BCP y la instrucción BULK INSERT permiten especificar que se mantengan los valores NULL.The bcp command and BULK INSERT statement both allow you to specify that nulls values be retained.

Por el contrario, una instrucción INSERT normal mantiene el valor NULL en lugar de insertar un valor predeterminado.In contrast, a regular INSERT statement retains the null value instead of inserting a default value. 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.The INSERT ... SELECT * FROM OPENROWSET(BULK...) statement provides the same basic behavior as regular INSERT but additionally supports a table hint for inserting the default values.

EsquemaOutline
Mantener valores NULLKeeping Null Values
Usar valores predeterminados con INSERT... SELECT * FROM OPENROWSET(BULK...)Using Default Values with INSERT ... SELECT * FROM OPENROWSET(BULK...)
Condiciones de prueba de ejemploExample Test Conditions
 ● Tabla de ejemplo ● Sample Table
 ● Archivo de datos de ejemplo ● Sample Data File
 ● Archivo de formato no XML de ejemplo ● Sample Non-XML Format File
Mantener valores NULL o usar valores predeterminados durante la importación masivaKeep Nulls or Use Default Values During Bulk Import
 ● Uso de BCP y mantenimiento de valores NULL sin un archivo de formato ● Using bcp and Keeping Null Values without a Format File
 ● Uso de BCP y mantenimiento de valores NULL con un archivo de formato no XML ● Using bcp and Keeping Null Values with a Non-XML Format File
 ● Uso de BCP y uso de valores predeterminados sin un archivo de formato ● Using bcp and Using Default Values without a Format File
 ● Uso de BCP y uso de valores predeterminados con un archivo de formato no XML ● Using bcp and Using Default Values with a Non-XML Format File
 ● Uso de BULK INSERT y mantenimiento de valores NULL sin un archivo de formato ● Using BULK INSERT and Keeping Null Values without a Format File
 ● Uso de BULK INSERT y mantenimiento de valores NULL con un archivo de formato no XML ● Using BULK INSERT and Keeping Null Values with a Non-XML Format File
 ● Uso de BULK INSERT y uso de valores predeterminados sin un archivo de formato ● Using BULK INSERT and Using Default Values without a Format File
 ● Uso de BULK INSERT y uso de valores predeterminados con un archivo de formato no XML ● Using BULK INSERT and Using Default Values with a Non-XML Format File
 ● Uso de OPENROWSET(BULK...) y mantenimiento de valores NULL con un archivo de formato no XML ● Using OPENROWSET(BULK...) and Keeping Null Values with a Non-XML Format File
 ● Uso de OPENROWSET(BULK...) y uso de valores predeterminados con un archivo de formato no XML ● Using OPENROWSET(BULK...) and Using Default Values with a Non-XML Format File

Mantener valores NULLKeeping Null Values

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.The following qualifiers specify that an empty field in the data file retains its null value during the bulk-import operation, rather than inheriting a default value (if any) for the table columns. De manera predeterminada, para OPENROWSET, cualquier columna no especificada en la operación de carga masiva se establece como NULL.For OPENROWSET, by default, any columns that are not specified in the bulk-load operation are set to NULL.

ComandoCommand QualifierQualifier Tipo de calificadorQualifier type
BCPbcp -k-k SwitchSwitch
BULK INSERTBULK INSERT KEEPNULLS\KEEPNULLS**** ArgumentoArgument
INSERT ... SELECT * FROM OPENROWSET(BULK...)INSERT ... SELECT * FROM OPENROWSET(BULK...) N/DN/A N/DN/A

\* Para BULK INSERT, si no hay valores predeterminados disponibles, se debe definir la columna de la tabla para permitir valores NULL.\* For BULK INSERT, if default values are not available, the table column must be defined to allow null values.

Nota

Estos calificadores deshabilitan la comprobación de definiciones DEFAULT en una tabla mediante los comandos de importación masiva.These qualifiers disable checking of DEFAULT definitions on a table by these bulk-import commands. Sin embargo, para cualquier instrucción INSERT simultánea, se esperan definiciones DEFAULT.However, for any concurrent INSERT statements, DEFAULT definitions are expected.

Usar valores predeterminados con INSERT... SELECT * FROM OPENROWSET(BULK...)Using Default Values with 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).You can specify that for an empty field in the data file, the corresponding table column uses its default value (if any). Para usar valores predeterminados, use la sugerencia de tabla KEEPDEFAULTS.To use default values, use the table hint KEEPDEFAULTS.

Condiciones de prueba de ejemploExample Test Conditions

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.The examples in this topic are based on the table, data file, and format file defined below.

Tabla de ejemploSample Table

El script siguiente crea una base de datos de prueba y una tabla llamada myNulls.The script below creates a test database and a table named myNulls. Tenga en cuenta que la cuarta columna de la tabla, Kids, tiene un valor predeterminado.Notice that the fourth table column, Kids, has a default value. Ejecutar el siguiente Transact-SQL en Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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 ejemploSample Data File

Mediante el Bloc de notas, cree un archivo vacío D:\BCP\myNulls.bcp e inserte los datos siguientes.Using Notepad, create an empty file D:\BCP\myNulls.bcp and insert the data below. Tenga en cuenta que no hay ningún valor en el registro tercero, cuarta columna.Note that there is no value in the third record, fourth column.

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:Alternatively, you can execute the following PowerShell script to create and populate the data file:

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 ejemploSample Non-XML Format File

SQL Server admite dos tipos de archivos de formato: XML y no XML.SQL Server support two types of format file: non-XML format and XML format. El formato no XML es el formato original compatible con versiones anteriores de SQL Server.The non-XML format is the original format that is supported by earlier versions of SQL Server. Revise Archivos de formato no XML (SQL Server) para obtener información detallada.Please review Non-XML Format Files (SQL Server) for detailed information. 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.The following command will use the bcp utility to generate a non-xml format file, myNulls.fmt, based on the schema of 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.To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. La opción format también requiere la opción -f .The format option also requires the -f option. 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.In addition, for this example, the qualifier c is used to specify character data, t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. En el símbolo del sistema, escriba el siguiente comando:At a command prompt, enter the following command:

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.Ensure your non-XML format file ends with a carriage return\line feed. De lo contrario, es probable que reciba el mensaje de error siguiente:Otherwise you will likely receive the following error message:

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).For more information about creating format files, see Create a Format File (SQL Server).

Mantener valores NULL o usar valores predeterminados durante la importación masivaKeep Nulls or Use Default Values During Bulk Import

Los ejemplos siguientes usan la base de datos, el archivo de datos y los archivos de formato que se han creado anteriormente.The examples below use the database, datafile, and format files created above.

Usar BCP y mantener valores NULL sin un archivo de formatoUsing bcp and Keeping Null Values without a Format File

Modificador -k .-k switch. En el símbolo del sistema, escriba el siguiente comando:At a command prompt, enter the following command:

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 XMLUsing bcp and Keeping Null Values with a Non-XML Format File

Modificadores -k y -f .-k and -f switches. En el símbolo del sistema, escriba el siguiente comando:At a command prompt, enter the following command:

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 formatoUsing bcp and Using Default Values without a Format File

En el símbolo del sistema, escriba el siguiente comando:At a command prompt, enter the following command:

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 XMLUsing bcp and Using Default Values with a Non-XML Format File

Modificador -f .-f switch. En el símbolo del sistema, escriba el siguiente comando:At a command prompt, enter the following command:

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 formatoUsing BULK INSERT and Keeping Null Values without a Format File

ArgumentoKEEPNULLS .KEEPNULLS argument. Ejecutar el siguiente Transact-SQL en Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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 XMLUsing BULK INSERT and Keeping Null Values with a Non-XML Format File

El argumentoKEEPNULLS y FORMATFILE .KEEPNULLS and the FORMATFILE argument. Ejecutar el siguiente Transact-SQL en Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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 formatoUsing BULK INSERT and Using Default Values without a Format File

Ejecutar el siguiente Transact-SQL en Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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 XMLUsing BULK INSERT and Using Default Values with a Non-XML Format File

ArgumentoFORMATFILE .FORMATFILE argument. Ejecutar el siguiente Transact-SQL en Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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 XMLUsing OPENROWSET(BULK...) and Keeping Null Values with a Non-XML Format File

ArgumentoFORMATFILE .FORMATFILE argument. Ejecutar el siguiente Transact-SQL en Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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 XMLUsing OPENROWSET(BULK...) and Using Default Values with a Non-XML Format File

Sugerencia de tablaKEEPDEFAULTS y argumento FORMATFILE .KEEPDEFAULTS table hint and FORMATFILE argument. Ejecutar el siguiente Transact-SQL en Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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 relacionadasRelated Tasks

Ver tambiénSee Also

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