Manter valores de identidade ao importar dados em massa (SQL Server)

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsAnalytics Platform System (PDW)

Os dados de arquivo que contêm valores de identidade podem ser importados em massa para uma instância do Microsoft SQL Server.

Por padrão, os valores da coluna de identidade do arquivo de dados que é importado são ignorados e o SQL Server atribui valores exclusivos automaticamente. Os valores exclusivos são baseados nos valores de semente e incremento que são especificados durante a criação da tabela.

Se o arquivo de dados não contiver valores para a coluna de identificador na tabela, use um arquivo de formato para especificar que a coluna de identificador na tabela deve ser ignorada durante a importação dos dados. Consulte Usar um arquivo de formato para ignorar uma coluna de tabela (SQL Server) para obter mais informações.

Manter valores de identidade

Para impedir que o SQL Server atribua valores de identidade durante a importação em massa de linhas de dados, use o qualificador de comando manter identidade apropriado. Quando você especificar um qualificador de manter identidade, o SQL Server usa os valores de identidade no arquivo de dados.

Estes qualificadores são os seguintes:

Comando	Qualificador manter identidade	Tipo de qualificador
bcp	-E	Comutador
BULK INSERT	KEEPIDENTITY	Argumento
INSERT ... SELECT * FROM OPENROWSET(BULK...)	KEEPIDENTITY	Dica de tabela

Para obter mais informações, consulte bcp Utility, BULK INSERT (Transact-SQL), OPENROWSET BULK (Transact-SQL), INSERT (Transact-SQL), SELECT (Transact-SQL), e Dicas de tabela (Transact-SQL).

Observação

Para criar um número incrementado automaticamente, que possa ser usado em várias tabelas ou ser chamado de aplicativos, sem referenciar tabelas, consulte Números de Sequência.

Condições de teste de exemplo

Os exemplos neste tópico baseiam-se na tabela, no arquivo de dados e no arquivo de formato definidos abaixo.

Tabela de exemplo

O script abaixo cria um banco de dados de teste e uma tabela chamada myIdentity. Execute o seguinte script Transact-SQL no Microsoft SSMS (SQL Server Management Studio):

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
   );

Arquivo de dados de exemplo

Usando o Bloco de Notas, crie um arquivo vazio D:\BCP\myIdentity.bcp e insira os dados abaixo.

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, você pode executar o seguinte script do PowerShell para criar e preencher o arquivo de dados:

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;

Arquivo de formato não XML de exemplo

O SQL Server dá suporte a dois tipos de arquivo de formato: XML e não XML. O formato não XML é o formato original com suporte em versões anteriores do SQL Server. Para obter mais informações, consulte Usar arquivos de formato não XML (SQL Server).

O comando a seguir usará o utilitário bcp para gerar um arquivo de formato não XML, myIdentity.fmt, com base no esquema de myIdentity.

• Para usar um comando bcp para criar um arquivo de formato, especifique o argumento format e use nul em vez de um caminho de arquivo de dados.
• A opção de formato também requer a opção -f.
• c é usado para especificar dados de caractere
• t, é usado para especificar uma vírgula como um terminador de campo
• T é usado para especificar uma conexão confiável usando a segurança integrada.

No prompt de comando, digite o seguinte comando:

bcp TestDatabase.dbo.myIdentity format nul -c -f D:\BCP\myIdentity.fmt -t, -T

REM Review file
Notepad D:\BCP\myIdentity.fmt

Importante

Verifique se o arquivo de formato não XML termina com um retorno de carro/alimentação de linha. Caso contrário, você provavelmente receberá a seguinte mensagem de erro:

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format file

Exemplos

Os exemplos usam o banco de dados, o arquivo de dados e os arquivos de formato criados neste artigo.

Usar bcp e manter valores de identidade sem um arquivo de formato

A -E opção.

No prompt de comando, digite o seguinte 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 e manter valores de identidade com um arquivo de formato não XML

O -E e -f alterna.

No prompt de comando, digite o seguinte 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 e valores de identidade gerados sem um arquivo de formato

Usando padrões.

No prompt de comando, digite o seguinte 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 e valores de identidade gerados com um arquivo de formato não XML

Use padrões e -f alterne.

No prompt de comando, digite o seguinte 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 e manter valores de identidade sem um arquivo de formato

O argumento KEEPIDENTITY.

Execute o seguinte script Transact-SQL no Microsoft SSMS (SQL Server Management Studio):

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 e manter valores de identidade com um arquivo de formato não XML

O KEEPIDENTITY e os FORMATFILE argumentos.

Execute o seguinte script Transact-SQL no Microsoft SSMS (SQL Server Management Studio):

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 e valores de identidade gerados sem um arquivo de formato

Usando padrões.

Execute o seguinte script Transact-SQL no Microsoft SSMS (SQL Server Management Studio):

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 e valores de identidade gerados com um arquivo de formato não XML

Usando padrões e FORMATFILE argumentos.

Execute o seguinte script Transact-SQL no Microsoft SSMS (SQL Server Management Studio):

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 e manter valores de identidade com um arquivo de formato não XML

A dica e KEEPIDENTITY o argumento da FORMATFILE tabela.

Execute o seguinte script Transact-SQL no Microsoft SSMS (SQL Server Management Studio):

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 e valores de identidade gerados com um arquivo de formato não XML

Usando padrões e o FORMATFILE argumento.

Execute o seguinte script Transact-SQL no Microsoft SSMS (SQL Server Management Studio):

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;

Tarefas Relacionadas

• Manter valores nulos ou padrão durante a importação em massa (SQL Server)

• Preparar dados para exportação ou importação em massa

Para usar um arquivo de formato

• Criar um arquivo de formato com bcp (SQL Server)

• Usar um arquivo de formato para importação em massa de dados (SQL Server)

• Usar um arquivo de formato para mapear colunas de tabela para campos de arquivo de dados (SQL Server)

• Usar um arquivo de formato para ignorar um campo de dados (SQL Server)

• Usar um arquivo de formato para ignorar uma coluna de tabela (SQL Server)

Para usar formatos de dados para importação ou exportação em massa

• Importar dados no formato nativo e de caractere de versões anteriores do SQL Server

• Usar o formato de caractere para importar ou exportar dados (SQL Server)

• Usar o formato nativo para importar ou exportar dados (SQL Server)

• Usar o formato de caractere Unicode para importar ou exportar dados (SQL Server)

• Usar o formato nativo Unicode para importar ou exportar dados (SQL Server)

Para especificar formatos de dados para compatibilidade usando bcp

1. Especificar terminadores de linha e de campo (SQL Server)

2. Especificar o tamanho do prefixo em arquivos de dados usando bcp (SQL Server)

3. Especificar o tipo de armazenamento de arquivos usando bcp (SQL Server)

Conteúdo relacionado

• BACKUP (Transact-SQL)
• Utilitário bcp
• BULK INSERT (Transact-SQL)
• OPENROWSET (Transact-SQL)
• Dicas de tabela (Transact-SQL)
• Arquivos de formato para importação ou exportação de dados (SQL Server)