Partilhar via

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

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

Os arquivos de dados 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 no arquivo de dados importado são ignorados e o SQL Server atribui valores exclusivos automaticamente. Os valores exclusivos são baseados nos valores de semente e incremento especificados durante a criação da tabela.

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

Manter os valores de identidade

Para impedir que o SQL Server atribua valores de identidade durante a importação em massa de linhas de dados para uma tabela, use o qualificador de comando keep-identity apropriado. Quando você especifica um qualificador keep-identity, o SQL Server usa os valores de identidade no arquivo de dados.

Estes qualificativos são os seguintes:

Comando Qualificador de preservação de identidade Tipo de qualificador
bcp -E Alternar
BULK INSERT KEEPIDENTITY Argumento
INSERT ... SELECT * FROM OPENROWSET(BULK...) MANTERIDENTIDADE 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 de incremento automático que pode ser usado em várias tabelas ou que pode ser chamado de aplicativos sem fazer referência a nenhuma tabela, consulte Números de sequência.

Exemplo de condições de teste

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 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 D:\BCP\myIdentity.bcp vazio 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 oferece suporte a dois tipos de arquivo de formato: formato não XML e formato XML. O formato não XML é o formato original suportado por 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 format argumento e use nul em vez de um caminho de arquivo de dados.
  • A opção de formato também requer a -f opção.
  • c é usado para especificar dados de caracteres
  • t, é usado para especificar uma vírgula como um terminador de campo
  • T é usado para especificar uma conexão confiável usando segurança integrada.

Em um 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\feed 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.

Use bcp e mantenha valores de identidade sem um arquivo de formato

O -E interruptor.

Em um 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;"

Use bcp e mantenha valores de identidade com um arquivo de formato não XML

Os -E e -f interruptores.

Em um 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 gerar valores de identidade sem um arquivo de formato

Usando predefinições.

Em um 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 ficheiro de formato não XML

Use padrões e -f alterne.

Em um 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;"

Use BULK INSERT e mantenha os valores Identity sem um arquivo de formato

O KEEPIDENTITY argumento.

Execute o seguinte 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;

Utilize o comando BULK INSERT e mantenha os valores de identidade com arquivos de formato não XML.

O KEEPIDENTITY e os FORMATFILE argumentos.

Execute o seguinte 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;

Usar BULK INSERT e gerar valores de Identity sem um arquivo de formato

Usando predefinições.

Execute o seguinte 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;

Utilizar BULK INSERT e valores de Identity gerados com um ficheiro de formato não XML

Usando padrões e FORMATFILE argumento.

Execute o seguinte 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;

Use OPENROWSET BULK e mantenha os valores de Identity com um arquivo de formato não XML

A dica da tabela KEEPIDENTITY e o argumento FORMATFILE.

Execute o seguinte 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;

Utilize OPENROWSET BULK e valores de identidade gerados com um ficheiro de formato não XML

Usando valores predefinidos e o argumento FORMATFILE.

Execute o seguinte 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;

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 ao usar bcp

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

  2. Especificar o comprimento 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

  • Last updated on