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

Artigo
06/25/2013

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. O SQL Server atribui automaticamente valores exclusivos para a coluna.

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 pode ser usado em várias tabelas ou ser chamado a partir de aplicativos, sem fazer referência a nenhuma tabela, consulte Números de sequência.

Exemplos

Os exemplos neste tópico importam em massa dados usando INSERT ... SELECT * FROM OPENROWSET(BULK...) e mantendo os valores padrão.

Tabela de exemplo

Os exemplos de importação em massa requerem que uma tabela denominada tabela myTestKeepNulls seja criada no banco de dados do exemplo AdventureWorks no esquema dbo. Para criar essa tabela. No Editor de Consultas do SQL Server Management Studio, execute:

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

A tabela Department na qual o myDepartment é baseado tem IDENTITY_INSERT definido como OFF. Portanto, para importar dados para uma coluna de identidade, você deve especificar KEEPIDENTITY ou o esquema -E.

Arquivo de dados de exemplo

O arquivo de dados usado nos exemplos da importação em massa contém massa de dados exportada da tabela HumanResources.Department em formato nativo. Para criar esse arquivo de dados, no prompt de comando do Microsoft Windows, digite:

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

Arquivo de formato de exemplo

Estes exemplos de importação em massa usam um arquivo de formato XML, myDepartment-f-x-n.Xml, que usa formatos de dados nativos. Esse exemplo usa bcp para criar a geração desse arquivo de formato a partir da tabela HumanResources.Department do banco de dados AdventureWorks. No prompt de comando do Windows, digite:

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

Para obter mais informações sobre como criar um arquivo de formato, consulte Criar um formato de arquivo (SQL Server).

A.Usando bcp e mantendo valores de identidade

O exemplo a seguir demonstra como manter valores de identidade ao usar o bcp para realizar a importação em massa dos dados. O comando bcp usa o arquivo de formato myDepartment-f-n-x.Xml e contém as seguintes opções:

Qualificadores	Descrição
-E	Especifica que o valor ou valores de identidade no arquivo de dados serão usados para a coluna de identidade.
-T	Especifica que o utilitário bcp faz conexão com o SQL Server por meio de uma conexão confiável.

No prompt de comando do Windows, digite:

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

B.Usando BULK INSERT e mantendo valores de identidade

Os exemplos a seguir usam BULK INSERT para importar em massa dados do arquivo myDepartment-c.Dat na tabela AdventureWorks.HumanResources.myDepartment. A instrução usa o formato de arquivo myDepartment-f-n-x.Xml e inclui a opção de KEEPIDENTITY para assegurar que quaisquer valores de identidade no arquivo de dados são retidos.

No Editor de Consultas do SQL Server Management Studio, execute:

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.Usando OPENROWSET e mantendo valores de identidade

O exemplo abaixo usa o provedor de conjunto de linhas OPENROWSET para importar em massa dados do arquivo myDepartment-c.Dat na tabela AdventureWorks.HumanResources.myDepartment. A instrução usa o formato de arquivo myDepartment-f-n-x.Xml e inclui a dica de KEEPIDENTITY para assegurar que quaisquer valores de identidade no arquivo de dados são retidos.

No Editor de Consultas do SQL Server Management Studio, execute:

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