• Artigo

Usando um arquivo de formato para mapear colunas de uma tabela para campos de arquivo de dados

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.

ObservaçãoObservaçã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 bcp, uma instrução BULK INSERT ou INSERT... SELECT * FROM OPENROWSET(BULK...) . Para obter mais informações, consulte Usando um arquivo de formato para importar dados em massa.

Tabela e arquivo de dados de exemplo

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

Tabela de exemplo

Os exemplos neste tópico necessitam que seja criada uma tabela denominada myTestOrder no banco de dados de exemplo AdventureWorks no esquema dbo. Para criar essa tabela, no Editor de Consultas do SQL Server Management Studio, execute o seguinte código:

USE AdventureWorks2008R2;
GO
CREATE TABLE myTestOrder 
   (
   Col1 smallint,
   Col2 nvarchar(50) ,
   Col3 nvarchar(50) , 
   Col4 nvarchar(50) 
   );
GO

Arquivo de dados

O arquivo de dados myTestOrder-c.txt contém os seguintes registros:

DataField3,DataField2,1,DataField4
DataField3,DataField2,1,DataField4
DataField3,DataField2,1,DataField4

Para importar dados em massa do myTestSkipCol2-c.dat para a tabela myTestSkipCol, o arquivo de formato deve mapear o primeiro campo de dados para Col3, o segundo campo de dados para Col2, o terceiro campo de dados para Col1e o quarto campo de dados para Col4.

Usando um arquivo de formato não XML

Você pode alterar a ordem de um mapeamento de coluna alterando o valor da ordem da coluna para indicar a posição do campo de dados correspondente.

O exemplo de arquivo de formato não XML a seguir apresenta um arquivo de formato, myTestOrder.fmt, que mapeia os campos no myTestOrder-c.txt para as colunas da tabela myTestOrder. Para obter informações sobre como criar o arquivo de dados e a tabela, consulte "Tabela e arquivo de dados de exemplo" anteriormente neste tópico. O arquivo de formato usa o formato de dados de caracteres.

Ele contém as seguintes informações:

9.0
4
1       SQLCHAR       0       100     ","     3     Col3               SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       100     ","     2     Col2               SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       7       ","     1     Col1               ""
4       SQLCHAR       0       100     "\r\n"  4     Col4               SQL_Latin1_General_CP1_CI_AS
ObservaçãoObservação

Para obter mais informações sobre o layout de arquivos de formato não XML, consulte Compreendendo arquivos de formato não XML.

Exemplo

O exemplo abaixo usa uma instrução BULK INSERT para importar dados em massa do arquivo de dados myTestOrder-c.txt para a tabela de exemplo myTestOrder usando o arquivo de formato não XML myTestOrder.fmt.

No Editor de Consultas do SQL Server Management Studio, execute:

USE AdventureWorks2008R2;
GO
BULK INSERT myTestOrder
FROM 'C:\myTestOrder-c.txt' 
WITH (formatfile='C:\myTestOrder.fmt');
GO

Usando um arquivo de formato XML

O exemplo de arquivo de formato não XML a seguir apresenta um arquivo de formato, myTestOrder.xml, que mapeia os campos no myTestOrder-c.txt para as colunas da tabela myTestOrder. Para obter informações sobre como criar o arquivo de dados e a tabela, consulte "Tabela e arquivo de dados de exemplo" anteriormente neste tópico.

O arquivo de formato myTestOrder.xml contém as seguintes informações:

<?xml version="1.0"?>
<BCPFORMAT xmlns="https://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="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="7"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="3" NAME="Col1" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="1" NAME="Col3" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>
ObservaçãoObservação

Para obter informações sobre a sintaxe do esquema XML e exemplos adicionais de arquivos de formato XML, consulte Compreendendo arquivos no formato XML.

Exemplo

O exemplo abaixo usa o provedor de conjuntos de linhas em massa OPENROWSET para importar dados do arquivo de dados myTestOrder-c.txt para a tabela de exemplo myTestOrder usando o arquivo de formato XML myTestOrder.xml. A instrução INSERT… SELECT especifica a lista de colunas na lista de seleção.

No Editor de Consultas do SQL Server Management Studio, execute o seguinte código:

USE AdventureWorks2008R2;
GO
INSERT INTO myTestOrder 
  SELECT Col1, Col2, Col3, Col4
      FROM  OPENROWSET(BULK  'C:\myTestOrder-c.txt',
      FORMATFILE='C:\myTestOrder.Xml'  
       ) AS t1;
GO