Mantendo valores de identidade ao importar em massa dados
Os dados de arquivo que contêm valores de identidade podem ser importados em massa em uma instância de 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 deveria ser ignorada ao importar dados. O 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 |
Alternar |
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).
Exemplos
Os exemplos neste tópico importam em massa dados usando INSERT ... SELECT * FROM OPENROWSET(BULK...) e mantendo 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 AdventureWorks2008R2 no esquema dbo. Para criar essa tabela, no Editor de Consultas do SQL Server Management Studio, execute:
USE AdventureWorks2008R2;
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. Então, para importar dados em uma coluna de identidade você deve especificar KEEPIDENTITY ou o -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 AdventureWorks2008R2.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. Este exemplo usa bcp para criar a geração deste arquivo de formato da tabela HumanResources.Department do banco de dados AdventureWorks2008R2. No prompt de comando do Windows, digite:
bcp AdventureWorks2008R2.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 Criando um arquivo de formato.
A. Usando bcp e mantendo valores de identidade
O exemplo seguinte demonstra como manter valores de identidade ao usar bcp para importar em massa 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 AdventureWorks2008R2.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
O exemplo a seguir usa BULK INSERT para importar em massa dados do arquivo myDepartment-c.Dat na tabela AdventureWorks2008R2.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 AdventureWorks2008R2;
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 AdventureWorks2008R2.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 AdventureWorks2008R2;
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