Usar um arquivo de formato para ignorar uma coluna de tabela (SQL Server)

Artigo
06/25/2013

Este tópico descreve arquivos de formato. Você pode usar um arquivo de formato para ignorar a importação de uma coluna de tabela, quando o campo não existir no arquivo de dados. Um arquivo de dados só poderá conter um número menor de campos que o número de colunas na tabela, se as colunas ignoradas forem anuláveis e/ou possuírem valor padrão.

Tabela e arquivo de dados de exemplo

Os exemplos a seguir solicitam uma tabela chamada myTestSkipCol, no banco de dados de exemplo AdventureWorks2012, no esquema dbo. Crie essa tabela como se segue:

USE AdventureWorks2012;
GO
CREATE TABLE myTestSkipCol 
   (
   Col1 smallint,
   Col2 nvarchar(50) NULL,
   Col3 nvarchar(50) not NULL
   );
GO

Os exemplos seguintes usam um arquivo de dados de exemplo myTestSkipCol2.dat com somente dois campos, ainda que a tabela correspondente tenha três colunas:

1,DataForColumn3
1,DataForColumn3
1,DataForColumn3

Para importar dados em massa do myTestSkipCol2.dat para a tabela myTestSkipCol, o arquivo de formato deve mapear o primeiro campo de dados para Col1, o segundo campo para Col3 e ignorar a Col2.

Usando um arquivo de formato não XML

É possível modificar um arquivo de formato não XML para ignorar uma coluna de tabela. Em geral, isso envolve o uso do utilitário bcp para criar um arquivo de formato não XML padrão e a modificação do arquivo padrão em um editor de texto. O arquivo de formato modificado deve mapear cada campo existente para sua coluna de tabela correspondente e indicar qual, ou quais, coluna de tabela deve ser ignorada. Há duas alternativas para se modificar um arquivo de dados não XML padrão. Ambas indicam que o campo de dados não existe no arquivo de dados e que nenhum dado será inserido na coluna de tabela correspondente.

Criando um arquivo de formato não XML padrão

Este tópico usa o arquivo de formato não XML padrão criado para a tabela de exemplo myTestSkipCol, usando o seguinte comando bcp:

bcp AdventureWorks2012..myTestSkipCol format nul -f myTestSkipCol_Default.fmt -c -T

O comando anterior cria um arquivo de formato não XML, myTestSkipCol_Default.fmt. Esse arquivo de formato é denominado arquivo de formato padrão, pois esse é o formato gerado pelo bcp. Geralmente, um arquivo de formato padrão descreve uma correspondência um-para-um entre campos de arquivo de dados e colunas de tabela.

Observação sobre segurança
Talvez seja preciso especificar o nome da instância do servidor ao qual você está conectando. Talvez também seja preciso especificar o nome de usuário e a senha. Para obter mais informações, consulte Utilitário bcp.

A ilustração a seguir exibe os valores nesses exemplos de arquivos de formato padrão. A ilustração também mostra o nome de cada campo do arquivo de formato.

arquivo de formato não XML padrão para myTestSkipCol

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

Métodos para modificar um arquivo de formato não XML

Para ignorar uma coluna de tabela, edite o arquivo de formato não XML padrão e modifique o arquivo, recorrendo a um dos seguintes métodos alternativos:

O método preferencial compreende três etapas básicas. Primeiro, exclua qualquer linha do arquivo de formato que descreva um campo ausente do arquivo de dados. Então, reduza o valor da "Ordem do campo de arquivo host" de cada linha do arquivo de formato que segue uma linha excluída. A meta são valores sequenciais "Ordem do campo de arquivo host", de 1 a n, que reflitam a posição atual de cada campo de dados no arquivo de dados. Finalmente, reduza o valor no campo "Número de colunas" para refletir o número real de campos no arquivo de dados.

O exemplo a seguir foi baseado no arquivo de formato padrão para a tabela myTestSkipCol, criada anteriormente neste tópico, em "Criando um arquivo de formato não XML padrão". Este arquivo de formato modificado mapeia o primeiro campo de dados para a Col1, ignora a Col2 e mapeia o segundo campo de dados para a Col3. A linha da Col2 foi excluída. Outras modificações estão indicadas em negrito:
```
9.0
2
1       SQLCHAR       0       7       "\t"     1     Col1         ""
2       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS
```
Como alternativa para ignorar uma coluna de tabela, é possível modificar a definição da linha do arquivo de formato que corresponde à coluna de tabela. Nessa linha do arquivo de formato, os valores "comprimento do prefixo", "comprimento dos dados do arquivo host" e "ordem da coluna do servidor" devem ser definidos como 0. Além disso, o campos "terminador" e "agrupamento de colunas" devem ser definidos como "" (NULO).

O valor "nome da coluna de servidor" requer uma cadeia de caracteres não vazios, ainda que o nome de coluna em si não seja necessário. Os campos de formato restantes requerem seus valores padrão.

O exemplo a seguir também é derivado do arquivo de formato padrão da tabela myTestSkipCol. Os valores que devem ser 0 ou NULL estão indicados em negrito.
```
9.0
3
1       SQLCHAR       0       7       "\t"     1     Col1         ""
2       SQLCHAR       0        0       ""      0     Col2         ""
3       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS
```

Exemplos

Os exemplos a seguir também são baseados na tabela de exemplo myTestSkipCol e no arquivo de dados de exemplo myTestSkipCol2.dat criados anteriormente neste tópico, em "Tabela de exemplo e arquivo de dados".

Usando BULK INSERT

Esse exemplo funciona usando qualquer um dos arquivos de formato não XML modificados criados anteriormente neste tópico, em "Métodos para modificar um arquivo de formato não XML". Nesse exemplo, o nome do arquivo de formato modificado é C:\myTestSkipCol2.fmt. Para usar o BULK INSERT para importar em massa o arquivo de dados myTestSkipCol2.dat, no Editor de Consultas SQL Server Management Studio, execute o código a seguir:

USE AdventureWorks2012;
GO
BULK INSERT myTestSkipCol 
   FROM 'C:\myTestSkipCol2.dat' 
   WITH (FORMATFILE = 'C:\myTestSkipCol2.fmt');
GO
SELECT * FROM myTestSkipCol;
GO

Usando um arquivo de formato XML

Com um arquivo de formato XML, não é possível ignorar uma coluna durante a importação direta para uma tabela usando um comando bcp ou uma instrução BULK INSERT. No entanto, é possível realizar a importação para todas as colunas de uma tabela, exceto para a última. Se for preciso ignorar alguma que não a última coluna, crie uma exibição da tabela de destino que contenha apenas as colunas contidas no arquivo de dados. Depois, importe em massa os dados desse arquivo para a exibição.

Para usar um arquivo de formato XML para ignorar uma coluna de tabela usando OPENROWSET(BULK...), é necessário fornecer uma lista explícita de colunas na lista de seleção e também na tabela de destino, como se segue:

INSERT ...<column_list> SELECT <column_list> FROM OPENROWSET(BULK...)

Criando um arquivo de formato XML padrão

Os exemplos dos arquivos de formato modificados se baseiam na tabela de exemplo myTestSkipCol e no arquivo de dados criado anteriormente neste tópico, em "Tabela de exemplo e arquivo de dados". O comando bcp a seguir cria um arquivo de formato XML padrão para a tabela myTestSkipCol:

bcp AdventureWorks2012..myTestSkipCol format nul -f myTestSkipCol_Default.xml -c -x -T

O arquivo de formato não XML padrão resultante descreve uma correspondência um-para-um entre campos de arquivo de dados e colunas de tabela, da seguinte maneira:

<?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="\t" MAX_LENGTH="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

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

Exemplos

Os exemplos desta seção usam a tabela de exemplo myTestSkipCol e o arquivo de dados de exemplo myTestSkipCol2.dat criados anteriormente neste tópico, em "Tabela de exemplo e arquivo de dados". Para importar os dados de myTestSkipCol2.dat para a tabela myTestSkipCol, os exemplos usam o seguinte arquivo de formato XML modificado, myTestSkipCol2-x.xml. A base para isso é o arquivo de formato criado anteriormente neste tópico, em "Criando um arquivo de formato XML padrão".

<?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="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Col3" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

Usando OPENROWSET(BULK...)

O exemplo a seguir usa o provedor de conjunto de linhas em massa OPENROWSET e o arquivo de formato myTestSkipCol2.xml. O exemplo importa em massa o arquivo de dados myTestSkipCol2.dat para a tabela myTestSkipCol. A instrução contém uma lista explícita de colunas na lista de seleção e também na tabela de destino, como exigido.

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

USE AdventureWorks2012;
GO
INSERT INTO myTestSkipCol
  (Col1,Col3)
    SELECT Col1,Col3
      FROM  OPENROWSET(BULK  'C:\myTestSkipCol2.Dat',
      FORMATFILE='C:\myTestSkipCol2.Xml'  
       ) as t1 ;
GO

Usando o BULK IMPORT em uma exibição

O exemplo a seguir cria o v_myTestSkipCol na tabela myTestSkipCol. Essa exibição ignora a segunda coluna da tabela, Col2. O exemplo usa o BULK INSERT para importar o arquivo de dados myTestSkipCol2.dat para essa exibição.

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

CREATE VIEW v_myTestSkipCol AS
    SELECT Col1,Col3
    FROM myTestSkipCol;
GO

USE AdventureWorks2012;
GO
BULK INSERT v_myTestSkipCol
FROM 'C:\myTestSkipCol2.dat'
WITH (FORMATFILE='C:\myTestSkipCol2.xml');
GO