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

  • Artículo

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.

Esquema
Mantener valores de identidad
Condiciones de prueba de ejemplo
 ● Tabla de ejemplo
 ● Archivo de datos de ejemplo
 ● Archivo de formato no XML de ejemplo
Ejemplos
 ● Usar BCP y mantener valores de identidad sin un archivo de formato
 ● Usar BCP y mantener valores de identidad con un archivo de formato no XML
 ● Usar BCP y valores de identidad generados sin un archivo de formato
 ● Usar BCP y valores de identidad generados con un archivo de formato no XML
 ● Usar BULK INSERT y mantener valores de identidad sin un archivo de formato
 ● Usar BULK INSERT y mantener valores de identidad con un archivo de formato no XML
 ● Usar BULK INSERT y valores de identidad generados sin un archivo de formato
 ● Usar BULK INSERT y valores de identidad generados con un archivo de formato no XML
 ● Usar OPENROWSET y mantener valores de identidad con un archivo de formato no XML
 ● Usar OPENROWSET y valores de identidad generados con un archivo de formato no XML

Mantener valores de identidad

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:

Get-Help Calificador para mantener la identidad Tipo de calificador
BCP -E Switch
BULK INSERT KEEPIDENTITY Argumento
INSERT ... SELECT * FROM OPENROWSET(BULK...) KEEPIDENTITY Sugerencia de tabla

Para obtener más información, consulte Utilidad bcp, 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 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. Ejecutar 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. Revise Archivos de formato no XML (SQL Server) para obtener información detallada. 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. 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. La opción format también requiere la opción -f . 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. 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

Los ejemplos siguientes usan la base de datos, el archivo de datos y los archivos de formato que se han creado anteriormente.

Usar BCP y mantener valores de identidad sin un archivo de formato

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

Modificadores -E 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;"

Usar 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

Usar valores predeterminados y el modificador -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

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

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

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

El argumentoKEEPIDENTITY y FORMATFILE . Ejecutar 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;

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

Usar valores predeterminados. Ejecutar 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

Usar valores predeterminados y el argumento FORMATFILE . Ejecutar 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;

Usar OPENROWSET(BULK...) y mantener valores de identidad con un archivo de formato no XML

Sugerencia de tablaKEEPIDENTITY y argumento FORMATFILE . Ejecutar 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;

Usar OPENROWSET(BULK...) y valores de identidad generados con un archivo de formato no XML

Usar valores predeterminados y el argumento FORMATFILE . Ejecutar 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;

Related Tasks

Para usar un archivo de formato

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

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

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

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

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

Consulte también

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