Compartilhar via

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

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

Um arquivo de dados pode conter mais campos do que o número de colunas na tabela. Este artigo descreve a modificação de arquivos de formato não XML e XML para acomodar um arquivo de dados com mais campos mapeando as colunas da tabela para os campos de dados correspondentes e ignorando os campos extras.

Para obter mais informações, consulte Criar um arquivo de formato com bcp (SQL Server).

Observação

Um arquivo de formato XML ou não XML pode ser usado para importar em massa um arquivo de dados para a tabela usando um comando utilitário bcp, uma instrução BULK INSERT (Transact-SQL) ou INSERT... Instrução SELECT * FROM OPENROWSET BULK (Transact-SQL). Para obter mais informações, consulte Usar um arquivo de formato para importar 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 artigo são baseados na tabela de exemplo myTestSkipField e no arquivo D:\BCP\myTestSkipField.bcpde dados. Altere o local do arquivo local no exemplo de código para um local de arquivo em seu computador.

Tabela de exemplo

O script 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 Usar arquivos no 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 os 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.

  • O qualificador c é usado para especificar dados de caractere
  • t, é usado para especificar uma vírgula como um terminador de campo
  • 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 FIELD ID valor em 1 para o novo FIELD e para cada subsequente FIELD.
  3. Aumente o COLUMN SOURCE valor em 1 para FirstName, e LastName para refletir 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 a COLUMN 1 é mapeado para a primeira coluna da tabela, myTestSkipField.. PersonID
  • FIELD 2 não corresponde a nenhum COLUMN e, portanto, não é mapeado para nenhuma coluna de tabela.
  • FIELD 3 que corresponde a COLUMN 3 é mapeado para a segunda coluna da tabela, myTestSkipField.. FirstName
  • FIELD 4 que corresponde a 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 usam o banco de dados de exemplo, o arquivo de dados e os arquivos de formato criados neste artigo.

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 arquivos de formato bcp e XML (SQL Server)

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 (Transact-SQL) 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 arquivos de formato BULK INSERT (Transact-SQL) e XML (SQL Server)

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 (Transact-SQL) 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 arquivos de formato OPENROWSET BULK (Transact-SQL) e XML (SQL Server)

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;

Conteúdo relacionado

  • Last updated on