Usar um arquivo de formato para mapear colunas de tabela para campos de arquivo de dados (SQL Server)

Artigo
04/04/2023

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 campos organizados em uma ordem diferente das colunas correspondentes na tabela. Este tópico apresenta arquivos de formato não XML e XML que foram 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 (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 myRemap. Execute o seguinte comando Transact-SQL no Microsoft SQL Server Management Studio (SSMS):

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 abaixo apresentam FirstName e LastName na ordem inversa como apresentados na tabela myRemap. Usando o Bloco de Notas, crie um arquivo vazio D:\BCP\myRemap.bcp e insira os seguintes dados:

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

Criar os arquivos de formato

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

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 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, 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

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

Reorganize a ordem das linhas do arquivo de formato para que as linhas estejam na mesma ordem que os dados do myRemap.bcp.
Verifique se os valores de ordem do campo do arquivo de host são sequenciais.
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 usará 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:

A ordem na qual os elementos <FIELD> são declarados no formato de arquivo é a ordem na qual os campos são exibidos no arquivo de dados; portanto, inverta a ordem dos elementos <FIELD> com os atributos de ID 2 e 3.
Verifique se os valores do atributo de ID <FIELD> são sequenciais.
A ordem dos elementos <COLUMN> no elemento <ROW> define a ordem em que 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 da tabela de destino de uma operação de importação em massa. A ordem dos elementos <COLUMN> não depende da ordem dos elementos <FIELD> 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
FIELD 2, que corresponde à COLUMN 2, é mapeado novamente para a terceira coluna da tabela, myRemap.. LastName
FIELD 3, que corresponde à COLUMN 3, é mapeado novamente para a segunda coluna da tabela, 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 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.myRemap IN D:\BCP\myRemap.bcp -f D:\BCP\myRemap.fmt -T

Usar bcp e arquivo de formato XML

No prompt de comando, digite o seguinte comando:

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 seguinte comando Transact-SQL no Microsoft SQL Server Management Studio (SSMS):

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 seguinte comando Transact-SQL no Microsoft SQL Server Management Studio (SSMS):

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(BULK...) e arquivo de formato não XML

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

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(BULK...) e arquivo de formato XML

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

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;

Usar um arquivo de formato para mapear colunas de tabela para campos de arquivo de dados (SQL Server)

Condições de teste de exemplo

Tabela de exemplo

Arquivo de dados de exemplo

Criar os arquivos de formato

Criar um arquivo de formato não XML

Modificar o arquivo de formato não XML

Criar um arquivo de formato XML

Modificar o arquivo de formato XML

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

Usar bcp e arquivo de formato não XML

Usar bcp e arquivo de formato XML

Usar BULK INSERT e arquivo de formato não XML

Usar BULK INSERT e arquivo de formato XML

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

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

Próximas etapas

Comentários

Comentários

Recursos adicionais