Mantener valores de identidad al importar datos de forma masiva (SQL Server)

Artículo
06/25/2013

Los archivos de datos contienen valores de identidad que pueden importarse de forma masiva en una instancia de Microsoft SQL Server. De manera predeterminada, los valores de la columna de identidad del archivo de datos que se importa se omiten y SQL Server asigna automáticamente valores únicos. Los valores únicos se basan en los valores de inicialización y de incremento especificados durante la creación de la tabla.

Si el archivo de datos no contiene valores para la columna de identificadores de la tabla, use el archivo de formato para especificar que se debe omitir esta columna al importar los datos. SQL Server asignará automáticamente valores exclusivos para la columna.

Para impedir que SQL Server asigne valores de identidad al importar de forma masiva filas de datos en una tabla, utilice el calificador de comando adecuado para mantener la identidad. Al especificar un calificador para mantener la identidad, SQL Server utiliza los valores de identidad del archivo de datos. Estos calificadores son:

Comando	Calificador para mantener la identidad	Tipo de calificador
bcp	-E	Modificador
BULK INSERT	KEEPIDENTITY	Argumento
INSERT ... SELECT * FROM OPENROWSET(BULK...)	KEEPIDENTITY	Sugerencia de tabla

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

[!NOTA]

Para crear un número que se incremente automáticamente y que se pueda utilizar en varias tablas, o que se pueda llamar desde las aplicaciones sin hacer referencia a ninguna tabla, vea Números de secuencia.

Ejemplos

En los ejemplos de este tema se realiza una importación masiva de datos utilizando INSERT ... SELECT * FROM OPENROWSET(BULK...) y se mantienen los valores predeterminados.

Tabla de ejemplo

En ejemplos de importación masiva, es necesario crear una tabla denominada myTestKeepNulls en la base de datos de ejemplo AdventureWorks en el esquema dbo. Para crear esta tabla en el Editor de consultas de SQL Server Management Studio, ejecute:

USE AdventureWorks;
GO
SELECT * INTO HumanResources.myDepartment 
   FROM HumanResources.Department
      WHERE 1=0;
GO
SELECT * FROM HumanResources.myDepartment;

La tabla Department en la que se basa myDepartment tiene IDENTITY_INSERT establecido en OFF. Por tanto, para importar datos en una columna de identidad, debe especificar KEEPIDENTITY o -E.

Archivo de datos de ejemplo

El archivo de datos usado en los ejemplos de importación masiva contiene datos exportados de forma masiva desde la tabla HumanResources.Department en formato nativo. Para crear el archivo de datos, en el símbolo del sistema de Microsoft Windows, escriba:

bcp AdventureWorks.HumanResources.Department out myDepartment-n.Dat -n -T

Ejemplo de archivo de formato

En este ejemplo de importación masiva se usa un archivo de formato XML, myDepartment-f-x-n.Xml, que usa formatos de datos nativos. En este ejemplo se usa bcp para generar este archivo de formato desde la tabla HumanResources.Department de la base de datos AdventureWorks. En el símbolo del sistema de Windows, escriba:

bcp AdventureWorks.HumanResources.Department format nul -n -x -f myDepartment-f-n-x.Xml -T

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

A.Usar bcp y mantener valores de identidad

En el siguiente ejemplo se muestra cómo mantener valores de identidad al usar bcp para importar datos de forma masiva. El comando bcp usa el archivo de formato, myDepartment-f-n-x.Xml, y contiene los siguientes modificadores:

Calificadores	Descripción
-E	Especifica el valor o valores de identidad del archivo de datos que se van a usar en la columna de identidad.
-T	Especifica que la utilidad bcp se conecta a SQL Server mediante una conexión de confianza.

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

bcp AdventureWorks.HumanResources.myDepartment in C:\myDepartment-n.Dat -f C:\myDepartment-f-n-x.Xml -E -T

B.Usar BULK INSERT y mantener valores de identidad

En el siguiente ejemplo se utiliza BULK INSERT para realizar una importación masiva de datos desde el archivo myDepartment-c.Dat en la tabla AdventureWorks.HumanResources.myDepartment. La instrucción utiliza el archivo de formato myDepartment-f-n-x.Xml e incluye la opción KEEPIDENTITY para que se mantengan todos los valores de identidad del archivo de datos.

En el Editor de consultas de SQL Server Management Studio, ejecute:

USE AdventureWorks;
GO
DELETE HumanResources.myDepartment;
GO
BULK INSERT HumanResources.myDepartment
   FROM 'C:\myDepartment-n.Dat'
   WITH (
      KEEPIDENTITY,
      FORMATFILE='C:\myDepartment-f-n-x.Xml'
   );
GO
SELECT * FROM HumanResources.myDepartment;

C.Usar OPENROWSET y mantener valores de identidad

En el siguiente ejemplo se utiliza el proveedor de conjuntos de filas BULK, OPENROWSET, para realizar una importación masiva de datos desde el archivo myDepartment-c.Dat en la tabla AdventureWorks.HumanResources.myDepartment. La instrucción utiliza el archivo de formato myDepartment-f-n-x.Xml e incluye la sugerencia KEEPIDENTITY para que se mantengan todos los valores de identidad del archivo de datos.

En el Editor de consultas de SQL Server Management Studio, ejecute:

USE AdventureWorks;
GO
DELETE HumanResources.myDepartment;
GO

INSERT INTO HumanResources.myDepartment
   with (KEEPIDENTITY)
   (DepartmentID, Name, GroupName, ModifiedDate)
   SELECT *
      FROM  OPENROWSET(BULK 'C:\myDepartment-n.Dat',
      FORMATFILE='C:\myDepartment-f-n-x.Xml') as t1;
GO