Partilhar via

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

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada SQL do Azure do Azure Synapse AnalyticsAnalytics 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. Consulte 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 utilitário bcp, instrução BULK INSERT ou instrução OPENROWSET BUL. Para obter mais informações, consulte Usar um arquivo de formato para importar dados em massa (SQL Server).

Observação

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

Exemplo de condições de ensaio

Os exemplos de arquivos de formato modificado 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 apresentados a seguir FirstName e LastName na ordem inversa, como 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 para a tabela myRemap.bcp, o arquivo de formato myRemap deve executar as seguintes ações:

  • Mapeie o primeiro campo de dados para a primeira coluna, PersonID.
  • Mapeie o segundo campo de dados para a terceira coluna, LastName.
  • Mapeie o terceiro campo de dados para a segunda coluna, FirstName.
  • Mapeie 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 a partir 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

Consulte Usar arquivos de 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 caracteres, t, é usado para especificar uma vírgula como um terminador de campo e 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.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 execute as seguintes modificações:

  1. Reorganize a ordem das linhas do arquivo de formato para que as linhas estejam na mesma ordem dos dados no myRemap.bcp.
  2. Verifique se os valores da ordem de campo do arquivo host são sequenciais.
  3. Verifique se há um retorno de carro após a última linha de 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 em myRemap.bcp é mapeado para a primeira coluna, myRemap.. PersonID
  • O segundo campo de dados em myRemap.bcp é mapeado para a terceira coluna, myRemap.. LastName
  • O terceiro campo de dados em myRemap.bcp é mapeado para a segunda coluna, myRemap.. FirstName
  • O quarto campo de dados é myRemap.bcp mapeado para a quarta coluna, myRemap.. Gender

Criar um arquivo de formato XML

Analise os 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 caracteres, t, é usado para especificar uma vírgula como um terminador de campo e T é usado para especificar uma conexão confiável usando segurança integrada. O x qualificador deve ser usado para gerar um arquivo de formato baseado em XML. Em um 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

Revise a sintaxe do esquema para arquivos de formato XML para obter terminologia. Abra D:\BCP\myRemap.xml no Bloco de Notas e execute as seguintes modificações:

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

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

  3. A ordem dos <COLUMN> elementos no <ROW> elemento define a ordem na qual a operação em massa os envia para o destino. O arquivo de formato XML atribui a cada <COLUMN> elemento um nome local que não tem 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 de uma <RECORD> definição. Cada <COLUMN> elemento corresponde a um <FIELD> elemento (cuja ID é especificada no atributo SOURCE do <COLUMN> elemento). Assim, os valores para <COLUMN> SOURCE são os únicos atributos que requerem revisão. Inverta a ordem dos <COLUMN> atributos SOURCE 2 e 3.

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:

  • O CAMPO 1, que corresponde à COLUNA 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 da tabela, myRemap.. FirstName
  • O CAMPO 4, que corresponde à COLUNA 4, é mapeado para a quarta coluna da tabela, myRemap.. Gender

Importar dados com um arquivo de formato para mapear colunas de tabela para o campo 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 arquivo de formato bcp e não XML

Em um prompt de comando, digite o seguinte comando.

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

Usar arquivo de formato bcp e XML

Em um 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 script Transact-SQL.

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 script Transact-SQL.

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 arquivo de formato OPENROWSET e não XML

Execute o seguinte script Transact-SQL.

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 arquivo de formato OPENROWSET e XML

Execute o seguinte script Transact-SQL.

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

  • Last updated on