Mantener valores NULL o usar valores predeterminados durante la importación masiva (SQL Server)

Artículo
06/25/2013

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

[!NOTA]

Para obtener ejemplos de archivos de formato que omiten una columna de la tabla, vea Usar un archivo de formato para omitir una columna de tabla (SQL Server).

Tabla y archivo de datos de ejemplo

Para ejecutar los ejemplos de este tema, es necesario crear una tabla y un archivo de datos de ejemplo.

Tabla de ejemplo

Los ejemplos requieren la creación de una tabla denominada MyTestDefaultCol2 en la base de datos de ejemplo AdventureWorks, bajo el esquema dbo. Para crear esta tabla, en el Editor de consultas de Microsoft SQL Server Management Studio, ejecute:

USE AdventureWorks;
GO
CREATE TABLE MyTestDefaultCol2 
(Col1 smallint,
Col2 nvarchar(50) DEFAULT 'Default value of Col2',
Col3 nvarchar(50) 
);
GO

Tenga en cuenta que la segunda columna de la tabla, Col2, tiene un valor predeterminado.

Ejemplo de archivo de formato

Algunos de los ejemplos de importación masiva utilizan un archivo de formato no XML, MyTestDefaultCol2-f-c.Fmt, que corresponde exactamente a la tabla MyTestDefaultCol2. Para crear este archivo de formato, en el símbolo del sistema de Microsoft Windows, especifique:

bcp AdventureWorks..MyTestDefaultCol2 format nul -c -f C:\MyTestDefaultCol2-f-c.Fmt -t, -r\n -T

Para obtener más información acerca de la creación de archivos de formato, vea Crear un archivo de formato (SQL Server).

Archivo de datos de ejemplo

El ejemplo utiliza un archivo de datos de ejemplo, MyTestEmptyField2-c.Dat, que no contiene valores en el segundo campo. El archivo de datos MyTestEmptyField2-c.Dat contiene los siguientes registros.

1,,DataField3
2,,DataField3

Mantener valores NULL con bcp o BULK INSERT

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.

Comando	Calificador	Tipo de calificador
bcp	-k	Modificador
BULK INSERT	KEEPNULLS1	Argumento

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

Para obtener más información, vea bcp (utilidad) y BULK INSERT (Transact-SQL).

Ejemplos

Los ejemplos de esta sección realizan importaciones masivas mediante bcp o BULK INSERT y mantienen los valores NULL.

La segunda columna de la tabla, Col2, tiene un valor predeterminado. El campo correspondiente del archivo de datos contiene una cadena vacía. De forma predeterminada, cuando se usa bcp o BULK INSERT para importar datos de este archivo de datos en la tabla MyTestDefaultCol2, se inserta el valor predeterminado de Col2, obteniéndose el siguiente resultado:

1	Default value of Col2	DataField3
2	Default value of Col2	DataField3

Para insertar "NULL" en lugar de "Default value of Col2", es necesario usar el modificador -k o la opción KEEPNULL, como se muestra en los siguientes ejemplos de bcp y BULK INSERT.

Usar bcp y mantener valores NULL

En el siguiente ejemplo se muestra cómo mantener valores NULL en un comando bcp. El comando bcp contiene los siguientes modificadores:

Modificador	Descripción
-f	Especifica que el comando utiliza un archivo de formato.
-k	Especifica que las columnas vacías deben conservar un valor NULL durante la operación, en vez de tener valores predeterminados para las columnas insertadas.
-T	Especifica que la utilidad bcp se conecte a SQL Server mediante una conexión de confianza.

En el símbolo del sistema de Windows, escriba:

bcp AdventureWorks..MyTestDefaultCol2 in C:\MyTestEmptyField2-c.Dat -f C:\MyTestDefaultCol2-f-c.Fmt -k -T

Usar BULK INSERT y mantener valores NULL

En el siguiente ejemplo se muestra el uso de la opción KEEPNULLS en una instrucción BULK INSERT. En una herramienta de consulta, como el Editor de consultas de SQL Server Management Studio, ejecute:

USE AdventureWorks;
GO
BULK INSERT MyTestDefaultCol2
   FROM 'C:\MyTestEmptyField2-c.Dat'
   WITH (
      DATAFILETYPE = 'char',
      FIELDTERMINATOR = ',',
      KEEPNULLS
   );
GO

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

De forma predeterminada, cualquier columna no especificada en la operación de carga masiva se establece como NULL mediante INSERT ... SELECT * FROM OPENROWSET(BULK...). Sin embargo, puede especificar que para un campo vacío del archivo de datos, la columna de tabla correspondiente utilice su valor predeterminado (si existe). Para usar los valores predeterminados, especifique la siguiente sugerencia de tabla:

Comando	Calificador	Tipo de calificador
INSERT ... SELECT * FROM OPENROWSET(BULK...)	WITH(KEEPDEFAULTS)	Sugerencia de tabla

[!NOTA]

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

Ejemplos

El siguiente ejemplo de INSERT ... El ejemplo SELECT * FROM OPENROWSET(BULK...) realiza importaciones masivas de datos y mantiene los valores predeterminados.

Para ejecutar los ejemplos es necesario crear la tabla de ejemplo MyTestDefaultCol2 y el archivo de datos MyTestEmptyField2-c.Dat y usar un archivo de formato, MyTestDefaultCol2-f-c.Fmt. Para obtener más información acerca de la creación de estos ejemplos, vea "Tabla y archivo de datos de ejemplo", más arriba en este tema.

La segunda columna de la tabla, Col2, tiene un valor predeterminado. El campo correspondiente del archivo de datos contiene una cadena vacía. Cuando INSERT ... SELECT * FROM OPENROWSET(BULK...) importa los campos de este archivo de datos a la tabla MyTestDefaultCol2; de modo predeterminado, en Col2 se inserta NULL en lugar del valor predeterminado. Este comportamiento predeterminado genera el siguiente resultado:

1	NULL	DataField3
2	NULL	DataField3

Para insertar el valor predeterminado, "Default value of Col2", en lugar de "NULL", es necesario usar la sugerencia de tabla KEEPDEFAULTS, como se muestra en el siguiente ejemplo. En una herramienta de consulta, como el Editor de consultas de SQL Server Management Studio, ejecute:

USE AdventureWorks;
GO
INSERT INTO MyTestDefaultCol2
    WITH (KEEPDEFAULTS)
    SELECT *
      FROM OPENROWSET(BULK  'C:\MyTestEmptyField2-c.Dat',
      FORMATFILE='C:\MyTestDefaultCol2-f-c.Fmt'     
      ) as t1 ;
GO