Usar um arquivo de formato para mapear colunas de tabela para campos de arquivo 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 Analytics Platform System (PDW)

Um arquivo de dados pode conter campos organizados em uma ordem diferente das colunas correspondentes na tabela. Este artigo apresenta arquivos de formato não XML e XML que são modificados para acomodar um arquivo de dados cujos campos são organizados em uma ordem diferente das colunas da tabela. O arquivo de formato modificado mapeia os campos de dados para as colunas da tabela correspondentes. Examine Criar um arquivo de formato com bcp (SQL Server) para obter informações adicionais.

Um arquivo de formato não XML ou XML pode ser usado para importar em massa um arquivo de dados para a tabela, usando um comando do utilitário bcp, instrução BULK INSERT ou INSERT ... SELECT * FROM OPENROWSET instrução. 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 e no arquivo de dados definidos posteriormente neste artigo.

Tabela de exemplo

O script Transact-SQL a seguir cria um banco de dados de teste e uma tabela chamada myRemap.

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;

CREATE TABLE myRemap
(
    PersonID SMALLINT,
    FirstName VARCHAR (25),
    LastName VARCHAR (30),
    Gender CHAR (1)
);

Arquivo de dados de exemplo

Os dados a seguir são apresentados FirstName e LastName na ordem inversa, conforme apresentado na tabela myRemap. Usando um editor de texto como o Bloco de Notas, crie um arquivo D:\BCP\myRemap.bcp vazio e insira os seguintes dados:

1,Grosse,Anthony,M
2,Fatnowna,Alica,F
3,Rosenhain,Stella,F

Criar os arquivos de formato

Para importar dados em massa da tabela, o arquivo de myRemap.bcp myRemap formato deve executar as seguintes ações:

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

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 usa o utilitário bcp para gerar um arquivo de formato não XML, myRemap.fmt, com base no esquema de myRemap. 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.myRemap format nul -c -f D:\BCP\myRemap.fmt -t, -T

Modificar o arquivo de formato não XML

Para obter informações sobre terminologia, consulte Estrutura de arquivos de formato não XML. Abra D:\BCP\myRemap.fmt no Bloco de Notas e realize as seguintes modificações:

  1. Reorganize a ordem das linhas do arquivo de formato para que as linhas fiquem na mesma ordem que os dados no myRemap.bcp.
  2. Verifique se os valores de ordem do campo do arquivo de host são sequenciais.
  3. Verifique se há um retorno de carro após a última linha do arquivo de formato.

Compare as alterações:

Antes

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

Depois

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

O arquivo de formato modificado agora reflete:

  • O primeiro campo de dados do myRemap.bcp é mapeado para a primeira coluna, myRemap.. PersonID
  • O segundo campo de dados do myRemap.bcp é mapeado para a terceira coluna, myRemap.. LastName
  • O terceiro campo de dados do myRemap.bcp é mapeado para a segunda coluna, myRemap.. FirstName
  • O quarto campo de dados do myRemap.bcp é mapeado para a quarta coluna, myRemap.. Gender

Criar um arquivo de formato XML

Examine Arquivos de formato XML (SQL Server) para obter informações detalhadas. O comando a seguir usa o utilitário bcp para criar um arquivo de formato xml, myRemap.xml, com base no esquema de myRemap. 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.myRemap format nul -c -x -f D:\BCP\myRemap.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\myRemap.xml no Bloco de Notas e realize as seguintes modificações:

  1. A ordem em que os elementos são declarados <FIELD> no arquivo de formato é a ordem em que esses campos aparecem no arquivo de dados. Assim, inverta <FIELD> a ordem dos elementos com atributos de ID 2 e 3.

  2. Verifique se os valores do atributo de ID <FIELD> são sequenciais.

  3. A ordem dos <COLUMN> elementos no <elemento ROW> define a ordem na qual a operação em massa os envia para o destino. O arquivo de formato XML atribui a cada elemento <COLUMN> um nome local que não tem nenhuma relação com a coluna na tabela de destino de uma operação de importação em massa. A ordem dos <COLUMN> elementos é independente da ordem dos <FIELD> elementos em uma <definição de RECORD> . Cada elemento <COLUMN> corresponde a um elemento <FIELD> (cuja ID é especificada no atributo SOURCE do elemento <COLUMN>). Portanto, os valores de <COLUMN> SOURCE são os únicos atributos que exigem revisão. Inverta a ordem dos atributos 2 e 3 de <COLUMN> SOURCE.

Compare as alterações:

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="," MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="1" 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"/>
  <COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
</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="30" 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="1" 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="2" NAME="LastName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
</ROW>
</BCPFORMAT>

O arquivo de formato modificado agora reflete:

  • FIELD 1, que corresponde à COLUMN 1, é mapeado para a primeira coluna da tabela, myRemap.. PersonID
  • O CAMPO 2, que corresponde à COLUNA 2, é remapeado para a terceira coluna do quadro, myRemap.. LastName
  • O CAMPO 3, que corresponde à COLUNA 3, é remapeado para a segunda coluna do quadro, myRemap.. FirstName
  • FIELD 4, que corresponde à COLUMN 4, é mapeado para a quarta coluna da tabela, myRemap.. Gender

Importar dados com um arquivo de formato para mapear colunas de uma tabela para campos de arquivo de dados

Os exemplos a seguir usam o banco de dados, o arquivo de dados e os arquivos de formato criados anteriormente.

Usar bcp e arquivo de formato não XML

Em um prompt de comando, insira o comando a seguir.

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

Usar bcp e arquivo de formato XML

Em um prompt de comando, insira o comando a seguir.

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

Usar BULK INSERT e arquivo de formato não XML

Execute o script Transact-SQL a seguir.

USE TestDatabase;
GO

TRUNCATE TABLE myRemap;

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

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

Usar BULK INSERT e arquivo de formato XML

Execute o script Transact-SQL a seguir.

USE TestDatabase;
GO

TRUNCATE TABLE myRemap;

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

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

Usar OPENROWSET e arquivo de formato não XML

Execute o script Transact-SQL a seguir.

USE TestDatabase;
GO

TRUNCATE TABLE myRemap;

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

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

Usar OPENROWSET e arquivo de formato XML

Execute o script Transact-SQL a seguir.

USE TestDatabase;
GO

TRUNCATE TABLE myRemap;

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

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

Conteúdo relacionado