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

  • Artigo

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

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.

Contorno
Manter valores de identidade
Condições de teste de exemplo
 ● Tabela de amostra
 ● Arquivo de dados de amostra
 ● Arquivo em formato não XML de amostra
Exemplos
 ● Como usar bcp e manter valores de identidade sem um arquivo de formato
 ● Como usar bcp e manter valores de identidade com um arquivo de formato não XML
 ● Como usar bcp e valores de identidade gerados sem um arquivo de formato
 ● Como usar bcp e valores de identidade gerados com um arquivo de formato não XML
 ● Como usar BULK INSERT e manter valores de identidade sem um arquivo de formato
 ● Como usar BULK INSERT e manter valores de identidade com um arquivo de formato não XML
 ● Como usar BULK INSERT e valores de identidade gerados sem um arquivo de formato
 ● Como usar BULK INSERT e valores de identidade gerados com um arquivo de formato não XML
 ● Usando OPENROWSET e mantendo valores de identidade com um arquivo de formato não XML
 ● Usando OPENROWSET e valores de identidade gerados com um arquivo de formato não XML

Manter valores de identidade

Para impedir SQL Server a atribuição de 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 Opção
BULK INSERT KEEPIDENTITY Argumento
INSERT ... SELECT * FROM OPENROWSET(BULK...) KEEPIDENTITY Dica de tabela

Para obter mais informações, consulte Utilitário bcp, BULK INSERT (Transact-SQL), OPENROWSET (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 comando Transact-SQL no 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
   );

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;

Exemplo de arquivo de formato não XML

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. Examine Arquivos de formato não XML (SQL Server) para obter informações detalhadas. 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 format também exige a opção -f . Além disso, neste exemplo, o qualificador c é usado para especificar dados de caractere, t é usado para especificar uma vírgula como um terminador de campoe 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 abaixo usam o banco de dados, o arquivo de dados e os arquivos de formato criados acima.

Como usar bcp e manter valores de identidade sem um arquivo de formato

Opção -E . 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;"

Como usar bcp e manter valores de identidade com um arquivo de formato não XML

Opções -E e -f . 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;"

Como 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;"

Como usar bcp e valores de identidade gerados com um arquivo de formato não XML

Usando padrões e a opção -f . 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;"

Como usar BULK INSERT e manter valores de identidade sem um arquivo de formato

ArgumentoKEEPIDENTITY . Execute o seguinte comando Transact-SQL no 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;

Como usar BULK INSERT e manter valores de identidade com um arquivo de formato não XML

KEEPIDENTITY e o argumento FORMATFILE . Execute o seguinte comando Transact-SQL no 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;

Como usar BULK INSERT e valores de identidade gerados sem um arquivo de formato

Usando padrões. Execute o seguinte comando Transact-SQL no 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;

Como usar BULK INSERT e valores de identidade gerados com um arquivo de formato não XML

Usando os padrões e o argumento FORMATFILE . Execute o seguinte comando Transact-SQL no 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;

Como usar OPENROWSET(BULK...) e manter valores de identidade com um arquivo de formato não XML

Dica de tabelaKEEPIDENTITY e argumento FORMATFILE . Execute o seguinte comando Transact-SQL no 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;

Como usar OPENROWSET(BULK...) e valores de identidade gerados com um arquivo de formato não XML

Usando os padrões e o argumento FORMATFILE . Execute o seguinte comando Transact-SQL no 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 um arquivo de formato

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

Para especificar formatos de dados para compatibilidade usando bcp

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

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

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

Consulte Também

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)