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

Artigo
06/25/2013

Um arquivo de dados pode conter mais campos do que o número de colunas na tabela. Este tópico descreve como modificar arquivos de formato XML e não XML para acomodar um arquivo de dados com mais campos, mapeando as colunas de tabela para os campos de dados correspondentes e ignorando os campos extras.

Observação
Um arquivo no formato XML ou diferente de 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 uma instrução INSERT ... SELECT * FROM OPENROWSET(BULK...). Para obter mais informações, consulte Usar um arquivo de formato para importação em massa de dados (SQL Server).

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 requerem que uma tabela denominada myTestSkipField seja criada no banco de dados de exemplo AdventureWorks2012 no esquema dbo. Para criar essa tabela, no Editor de Consultas do Microsoft SQL Server Management Studio execute o seguinte código:

USE AdventureWorks2012;
GO
CREATE TABLE myTestSkipField 
   (
   PersonID smallint,
   FirstName nvarchar(50) ,
   LastName nvarchar(50) 
   );
GO

Arquivo de dados de exemplo

O arquivo de dados myTestSkipField-c.dat contém os seguintes registros:

1,Skipme,DataField3,DataField4
1,Skipme,DataField3,DataField4
1,Skipme,DataField3,DataField4

Para importar em massa dados de myTestSkipField-c.dat para a tabela myTestSkipField, o arquivo de formato deve 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.

Arquivo de formato não XML para mais campos de dados

O arquivo de formato a seguir, myTestSkipField.fmt, mapeia os campos em myTestSkipField-c.dat para as colunas da tabela myTestSkipField. O arquivo de formato usa o formato de dados de caracteres. Ignorar um mapeamento de coluna requer a alteração do valor da ordem da coluna para 0, como mostrado para a coluna ExtraField no arquivo de formato.

O arquivo de formato myTestSkipField.fmt contém as seguintes informações:

9.0
4
1       SQLCHAR       0       7       ","      1     PersonID               ""
2       SQLCHAR       0       100       ","    0     ExtraField             SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       100     ","      2     FirstName              SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       100     "\r\n"   3     LastName               SQL_Latin1_General_CP1_CI_AS

Observação
Para obter mais informações sobre a sintaxe de arquivos de formato não XML, consulte Arquivos de formato não XML (SQL Server).

Exemplos

O exemplo a seguir usa INSERT ... SELECT * FROM OPENROWSET(BULK...) utilizando o arquivo de formato myTestSkipField.fmt. O exemplo importa em massa o arquivo de dados myTestSkipField-c.dat para a tabela myTestSkipField. Para criar a tabela e o arquivo de dados de exemplo, consulte "Tabela e arquivo de dados de exemplo", anteriormente neste tópico.

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

USE AdventureWorks2012;
GO
INSERT INTO myTestSkipField 
   SELECT *
      FROM  OPENROWSET(BULK  'C:\myTestSkipField-c.dat',
      FORMATFILE='C:\myTestSkipField.fmt'  
       ) AS t1;
GO

Arquivo de formato XML para mais campos de dados

O arquivo de formato apresentado neste exemplo tem base em outro arquivo de formato, myTestSkipField.xml, que usa formato de dados de caracteres sempre e cujos campos correspondem exatamente em número e ordem às colunas na tabela myTestSkipField. Para exibir o conteúdo desse arquivo de formato, consulte Criar um formato de arquivo (SQL Server).

O arquivo de formato a seguir, myTestSkipField.xml, mapeia os campos em myTestSkipField-c.dat para as colunas da tabela myTestSkipField. O arquivo de formato usa o formato de dados de caracteres.

O arquivo de formato myTestSkipField.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="7"/>
  <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="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="3" NAME="FirstName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="LastName" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

Exemplos

O exemplo a seguir usa INSERT ... SELECT * FROM OPENROWSET(BULK...) utilizando o arquivo de formato myTestSkipField.Xml. O exemplo importa em massa o arquivo de dados myTestSkipField-c.dat para a tabela myTestSkipField. Para criar a tabela e o arquivo de dados de exemplo, consulte "Tabela e arquivo de dados de exemplo", anteriormente neste tópico.

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

USE AdventureWorks2012;
GO
INSERT INTO myTestSkipField 
  SELECT *
      FROM  OPENROWSET(BULK  'C:\myTestSkipField-c.dat',
      FORMATFILE='C:\myTestSkipField.xml'  
       ) AS t1;
GO

Observação
Para obter informações sobre a sintaxe do esquema XML e exemplos adicionais de arquivos de formato XML, consulte Arquivos de formato XML (SQL Server).