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

Artigo
06/13/2017

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

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.

Exemplos

Os exemplos neste tópico importam dados em massa 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. em SQL Server Management Studio Editor de Consultas, execute:

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

A tabela DepartamentomyDepartment na qual o é baseado tem IDENTITY_INSERT definido como OFF. Portanto, para importar dados para uma coluna de identidade, você deve especificar KEEPIDENTITY ou -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 o arquivo de dados, no prompt de comando do Microsoft Windows, insira:

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 arquivo de formato (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 `bcp` utilitário se conecta a SQL Server com 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 SQL Server Management Studio Editor de Consultas, 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 SQL Server Management Studio Editor de Consultas, 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

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

Consulte Também

BACKUP (Transact-SQL)
Utilitário bcp
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
Dicas de tabela (Transact-SQL)

Compartilhar via