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

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Archivos de datos que 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. Vea Usar un archivo de formato para omitir una columna de tabla (SQL Server) para obtener información adicional.

Mantener los valores de identidad

Para impedir que SQL Server asigne valores de identidad al importar de forma masiva filas de datos en una tabla, utiliza 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	Conmutador
BULK INSERT	KEEPIDENTITY	Argumento
INSERT ... SELECT * FROM OPENROWSET(BULK...)	KEEPIDENTITY	Sugerencia de tabla

Para obtener más información, vea utilidad bcp, BULK INSERT (Transact-SQL), OPENROWSET BULK (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 usar en varias tablas, o que se pueda llamar desde las aplicaciones sin hacer referencia a ninguna tabla, vea Números de secuencia.

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 myIdentity. Ejecute el siguiente Transact-SQL en Microsoft SQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.myIdentity ( 
   PersonID smallint IDENTITY(1,1) NOT NULL,
   FirstName varchar(25) NOT NULL,
   LastName varchar(30) NOT NULL,
   BirthDate date
   );

Archivo de datos de ejemplo

Mediante el Bloc de notas, cree un archivo vacío D:\BCP\myIdentity.bcp e inserte los datos siguientes.

3,Anthony,Grosse,1980-02-23
2,Alica,Fatnowna,1963-11-14
1,Stella,Rosenhain,1992-03-02
4,Miller,Dylan,1954-01-05

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 + 'myIdentity.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 '3,Anthony,Grosse,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,1963-11-14';
Add-Content -Path $bcpFile -Value '1,Stella,Rosenhain,1992-03-02';
Add-Content -Path $bcpFile -Value '4,Miller,Dylan,1954-01-05';

#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 hará uso de la utilidad BCP para generar un archivo de formato no XML, myIdentity.fmt, basado en el esquema de myIdentity.

• 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.myIdentity format nul -c -f D:\BCP\myIdentity.fmt -t, -T

REM Review file
Notepad D:\BCP\myIdentity.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

Ejemplos

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 de identidad sin un archivo de formato

El -E modificador.

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

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myIdentity;"

REM Import data
bcp TestDatabase.dbo.myIdentity IN D:\BCP\myIdentity.bcp -T -c -t, -E

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myIdentity;"

Usar bcp y mantener valores de identidad con un archivo de formato no XML

Los -E modificadores y -f .

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

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myIdentity;"

REM Import data
bcp TestDatabase.dbo.myIdentity IN D:\BCP\myIdentity.bcp -f D:\BCP\myIdentity.fmt -T -E

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myIdentity;"

Uso de bcp y valores de identidad generados sin un archivo de formato

Usar valores predeterminados.

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

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myIdentity;"

REM Import data
bcp TestDatabase.dbo.myIdentity IN D:\BCP\myIdentity.bcp -T -c -t,

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myIdentity;"

Usar bcp y valores de identidad generados con un archivo de formato no XML

Use los valores predeterminados y -f el modificador.

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

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myIdentity;"

REM Import data
bcp TestDatabase.dbo.myIdentity IN D:\BCP\myIdentity.bcp -f D:\BCP\myIdentity.fmt -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myIdentity;"

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

Argumento KEEPIDENTITY.

Ejecute el siguiente Transact-SQL en Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity; -- for testing
BULK INSERT dbo.myIdentity
    FROM 'D:\BCP\myIdentity.bcp'
    WITH (
        DATAFILETYPE = 'char',  
        FIELDTERMINATOR = ',',  
        KEEPIDENTITY
        );

-- review results
SELECT * FROM TestDatabase.dbo.myIdentity;

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

Los KEEPIDENTITY argumentos y FORMATFILE .

Ejecute el siguiente Transact-SQL en Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity; -- for testing
BULK INSERT dbo.myIdentity
   FROM 'D:\BCP\myIdentity.bcp'
   WITH (
        FORMATFILE = 'D:\BCP\myIdentity.fmt',
        KEEPIDENTITY
        );

-- review results
SELECT * FROM TestDatabase.dbo.myIdentity;

Uso de BULK INSERT y valores de identidad generados sin un archivo de formato

Usar valores predeterminados.

Ejecute el siguiente Transact-SQL en Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity;  -- for testing
BULK INSERT dbo.myIdentity
   FROM 'D:\BCP\myIdentity.bcp'
   WITH (
      DATAFILETYPE = 'char',  
      FIELDTERMINATOR = ','
      );

-- review results
SELECT * FROM TestDatabase.dbo.myIdentity;

Usar BULK INSERT y valores de identidad generados con un archivo de formato no XML

Uso de valores predeterminados y FORMATFILE argumentos.

Ejecute el siguiente Transact-SQL en Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity;  -- for testing
BULK INSERT dbo.myIdentity
   FROM 'D:\BCP\myIdentity.bcp'
   WITH (
        FORMATFILE = 'D:\BCP\myIdentity.fmt'
        );

-- review results
SELECT * FROM TestDatabase.dbo.myIdentity;

Uso de OPENROWSET BULK y mantenimiento de valores de identidad con un archivo de formato no XML

Sugerencia KEEPIDENTITY de tabla y FORMATFILE argumento.

Ejecute el siguiente Transact-SQL en Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity;  -- for testing
INSERT INTO dbo.myIdentity
WITH (KEEPIDENTITY) 
(PersonID, FirstName, LastName, BirthDate)
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myIdentity.bcp', 
        FORMATFILE = 'D:\BCP\myIdentity.fmt'  
        ) AS t1;

-- review results
SELECT * FROM TestDatabase.dbo.myIdentity;

Uso de OPENROWSET BULK y valores de identidad generados con un archivo de formato no XML

Uso de valores predeterminados y el FORMATFILE argumento .

Ejecute el siguiente Transact-SQL en Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity;  -- for testing
INSERT INTO dbo.myIdentity
(FirstName, LastName, BirthDate)
    SELECT FirstName, LastName, BirthDate
    FROM OPENROWSET (
        BULK 'D:\BCP\myIdentity.bcp', 
        FORMATFILE = 'D:\BCP\myIdentity.fmt'  
        ) AS t1;

-- review results
SELECT * FROM TestDatabase.dbo.myIdentity;

Tareas relacionadas

• Mantenimiento de valores NULL o predeterminados durante la importación 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

1. Especificar terminadores de campo y fila (SQL Server)

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

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

Contenido relacionado

• BACKUP (Transact-SQL)
• bcp (utilidad)
• BULK INSERT (Transact-SQL)
• OPENROWSET (Transact-SQL)
• Sugerencias de tabla (Transact-SQL)
• Archivos de formato para importar y exportar datos (SQL Server)