Usar um arquivo de formato para ignorar um campo de dados (SQL Server)

  • Artigo

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics PDW (Analytics Platform System)

Um arquivo de dados pode conter mais campos do que o número de colunas na tabela. Este tópico descreve como modificar arquivos de formato XML e não XML para acomodar um arquivo de dados com mais campos, mapeando as colunas de tabela para os campos de dados correspondentes e ignorando os campos extras. Examine Criar um arquivo de formato (SQL Server) para obter mais informações.

Observação

Um arquivo de formato XML ou não XML pode ser usado para a importação em massa de um arquivo de dados para a tabela usando um comando do utilitário bcp , uma instrução BULK INSERT ou INSERT... Instrução SELECT * FROM OPENROWSET(BULK...) . Para obter mais informações, confira Usar um arquivo de formato para importação de dados em massa (SQL Server).

Observação

Essa sintaxe, incluindo inserção em massa, não tem suporte no Azure Synapse Analytics. No Azure Synapse Analytics e em outras integrações de plataforma de banco de dados de nuvem, efetue a movimentação de dados por meio da instrução COPY no Azure Data Factory ou usando instruções T-SQL, como COPY INTO e PolyBase.

Condições de teste de exemplo

Os exemplos de arquivos de formato modificados neste tópico baseiam-se na tabela e no arquivo de dados definidos abaixo.

Tabela de exemplo

O script abaixo cria um banco de dados de teste e uma tabela chamada myTestSkipField. Execute o seguinte script Transact-SQL no Microsoft SSMS (SQL Server Management Studio):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE myTestSkipField
   (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30)
   );

Arquivo de dados de exemplo

Crie um arquivo vazio D:\BCP\myTestSkipField.bcp e insira os seguintes dados:

1,SkipMe,Anthony,Grosse
2,SkipMe,Alica,Fatnowna
3,SkipMe,Stella,Rosenhain

Criar os arquivos de formato

Para importar em massa dados de myTestSkipField.bcp para a tabela myTestSkipField , o arquivo de formato deverá fazer o seguinte:

  • Mapear o primeiro campo de dados para a primeira coluna, PersonID.
  • Ignorar o segundo campo de dados.
  • Mapear o terceiro campo de dados para a segunda coluna, FirstName.
  • Mapear o quarto campo de dados para a terceira coluna, LastName.

O método mais simples para criar o arquivo de formato é usando o utilitário bcp. Primeiro, crie um arquivo de formato base da tabela existente. Em segundo lugar, modifique o arquivo de formato base para refletir o arquivo de dados real.

Criar um arquivo de formato não XML

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, myTestSkipField.fmt, com base no esquema de myTestSkipField. Além disso, o qualificador c é usado para especificar dados de caractere, t, é usado para especificar uma vírgula como um terminador de campo e 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.myTestSkipField format nul -c -f D:\BCP\myTestSkipField.fmt -t, -T

Modificar o arquivo de formato não XML

Examine a estrutura de arquivos de formato não XML para obter a terminologia. Abra D:\BCP\myTestSkipField.fmt no Bloco de Notas e realize as seguintes modificações:

  1. Copie a linha inteira de arquivo de formato em FirstName e cole-a diretamente após FirstName na próxima linha.
  2. Aumente o valor de ordem do campo de arquivo de host em um para a nova linha e todas as próximas linhas.
  3. Aumente o valor do número de colunas para que ele reflita o número real de campos no arquivo de dados.
  4. Modifique a ordem das colunas do servidor de 2 para 0 na segunda linha do arquivo de formato.

Compare as alterações feitas:

Antes

13.0
3
1       SQLCHAR    0       7       ","      1     PersonID        ""
2       SQLCHAR    0       25      ","      2     FirstName    SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR    0       30      "\r\n"   3     LastName     SQL_Latin1_General_CP1_CI_AS

Depois

13.0
4
1       SQLCHAR    0       7       ","      1     PersonID     ""
2       SQLCHAR    0       25      ","      0     FirstName    SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR    0       25      ","      2     FirstName    SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR    0       50      "\r\n"   3     LastName     SQL_Latin1_General_CP1_CI_AS

O arquivo de formato modificado agora reflete:

  • 4 campos de dados
  • O primeiro campo de dados do myTestSkipField.bcp é mapeado para a primeira coluna, myTestSkipField.. PersonID
  • O segundo campo de dados do myTestSkipField.bcp não é mapeado para nenhuma coluna.
  • O terceiro campo de dados do myTestSkipField.bcp é mapeado para a segunda coluna, myTestSkipField.. FirstName
  • O quarto campo de dados do myTestSkipField.bcp é mapeado para a terceira coluna, myTestSkipField.. LastName

Criar um arquivo de formato XML

Examine Arquivos de formato XML (SQL Server) para obter informações detalhadas. O comando a seguir usará o utilitário bcp para criar um arquivo de formato XML, myTestSkipField.xml, com base no esquema de myTestSkipField. Além disso, o qualificador c é usado para especificar dados de caractere, t, é usado para especificar uma vírgula como um terminador de campo e T é usado para especificar uma conexão confiável usando a segurança integrada. O qualificador x deve ser usado para gerar um arquivo de formato baseado em XML. No prompt de comando, digite o seguinte comando:

bcp TestDatabase.dbo.myTestSkipField format nul -c -x -f D:\BCP\myTestSkipField.xml -t, -T

Modificar o arquivo de formato XML

Examine Sintaxe de esquema para arquivos de formato XML para obter a terminologia. Abra D:\BCP\myTestSkipField.xml no Bloco de Notas e realize as seguintes modificações:

  1. Copie todo o segundo campo e cole-o diretamente após o segundo campo na próxima linha.
  2. Aumente o valor de “FIELD ID” em 1 para o novo FIELD e para cada campo posterior.
  3. Aumente o valor de “COLUMN SOURCE” em 1 para FirstNamee LastName para que eles reflitam o mapeamento revisado.

Compare as alterações feitas:

Antes

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

Depois

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="3" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="LastName" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

O arquivo de formato modificado agora reflete:

  • 4 campos de dados
  • FIELD 1, que corresponde à COLUMN 1, é mapeado para a primeira coluna da tabela, myTestSkipField.. PersonID
  • FIELD 2 não corresponde a nenhuma COLUMN e, portanto, não está mapeado para nenhuma coluna da tabela.
  • FIELD 3 que corresponde à COLUMN 3 é mapeado para a segunda coluna da tabela, myTestSkipField.. FirstName
  • FIELD 4 que corresponde à COLUMN 4 é mapeado para a terceira coluna da tabela, myTestSkipField.. LastName

Importar dados com um arquivo de formato para ignorar um campo de dados

Os exemplos abaixo usam o banco de dados, o arquivo de dados e os arquivos de formato criados acima.

Usar bcp e arquivo de formato não XML

No prompt de comando, digite o seguinte comando:

bcp TestDatabase.dbo.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.fmt -T

Usar bcp e arquivo de formato XML

No prompt de comando, digite o seguinte comando:

bcp TestDatabase.dbo.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.xml -T

Usar BULK INSERT e arquivo de formato não XML

Execute o seguinte script Transact-SQL no Microsoft SSMS (SQL Server Management Studio):

USE TestDatabase;  
GO

TRUNCATE TABLE myTestSkipField;
BULK INSERT dbo.myTestSkipField  
   FROM 'D:\BCP\myTestSkipField.bcp'  
   WITH (FORMATFILE = 'D:\BCP\myTestSkipField.fmt');  
GO

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

Usar BULK INSERT e arquivo de formato XML

Execute o seguinte script Transact-SQL no Microsoft SSMS (SQL Server Management Studio):

USE TestDatabase;  
GO

TRUNCATE TABLE myTestSkipField;
BULK INSERT dbo.myTestSkipField  
   FROM 'D:\BCP\myTestSkipField.bcp'  
   WITH (FORMATFILE = 'D:\BCP\myTestSkipField.xml');  
GO

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

Usar OPENROWSET(BULK...) e arquivo de formato não XML

Execute o seguinte script Transact-SQL no Microsoft SSMS (SQL Server Management Studio):

USE TestDatabase;
GO

TRUNCATE TABLE myTestSkipField;
INSERT INTO dbo.myTestSkipField
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myTestSkipField.bcp',
        FORMATFILE = 'D:\BCP\myTestSkipField.fmt'
        ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

Usar OPENROWSET(BULK...) e arquivo de formato XML

Execute o seguinte script Transact-SQL no Microsoft SSMS (SQL Server Management Studio):

USE TestDatabase;  
GO

TRUNCATE TABLE myTestSkipField;
INSERT INTO dbo.myTestSkipField  
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myTestSkipField.bcp',
        FORMATFILE = 'D:\BCP\myTestSkipField.xml'  
       ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

Próximas etapas