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

Aplica-se a:SQL ServerBase de Dados SQL do AzureAzure SQL Managed InstanceAzure Synapse AnalyticsSistema de Plataforma de Análise (PDW)Base de dados SQL no Microsoft Fabric

Este artigo descreve como usar um arquivo de formato para ignorar a importação de uma coluna de tabela quando os dados para a coluna ignorada não existem no arquivo de dados de origem. Um arquivo de dados pode conter menos campos do que o número de colunas na tabela de destino - ou seja, você pode ignorar a importação de uma coluna - somente se pelo menos uma das duas condições a seguir for verdadeira na tabela de destino:

• A coluna ignorada é anulável.
• A coluna omitida tem um valor padrão.

Tabela de exemplo e arquivo de dados

Os exemplos neste artigo esperam uma tabela nomeada myTestSkipCol sob o dbo esquema. Você pode criar essa tabela em um banco de dados de exemplo, como WideWorldImporters ou AdventureWorks em qualquer outro banco de dados. Crie esta tabela da seguinte forma:

USE WideWorldImporters;
GO

CREATE TABLE myTestSkipCol
(
    Col1 SMALLINT,
    Col2 NVARCHAR (50) NULL,
    Col3 NVARCHAR (50) NOT NULL
);
GO

Os exemplos neste artigo também usam um arquivo de dados de exemplo, myTestSkipCol2.dat. Esse arquivo de dados contém apenas dois campos, embora a tabela de destino contenha três colunas.

1,DataForColumn3
1,DataForColumn3
1,DataForColumn3

Passos básicos

Você pode usar um arquivo de formato não XML ou um arquivo de formato XML para ignorar uma coluna de tabela. Em ambos os casos, existem dois passos:

1. Use o utilitário de linha de comando bcp para criar um arquivo de formato padrão.
2. Modifique o arquivo de formato padrão em um editor de texto.

O arquivo de formato modificado deve mapear cada campo existente para sua coluna correspondente na tabela de destino. Ele também deve indicar qual coluna ou colunas da tabela devem ser ignoradas.

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

Opção #1 - Usar um arquivo de formato não-XML

Etapa #1 - Criar um arquivo de formato não-XML padrão

Crie um arquivo de formato não XML padrão para a myTestSkipCol tabela de exemplo executando o seguinte comando bcp no prompt de comando:

bcp WideWorldImporters..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 é chamado de arquivo de formato padrão porque é o formulário gerado pelo bcp. Um arquivo de formato padrão descreve uma correspondência um-para-um entre campos de arquivo de dados e colunas de tabela.

A captura de tela a seguir mostra os valores neste arquivo de formato padrão de exemplo.

Etapa #2 - Modificar um arquivo de formato não-XML

Para modificar um arquivo de formato não-XML padrão, há duas alternativas. Qualquer uma das alternativas indica que o campo de dados não existe no arquivo de dados e que nenhum dado deve ser inserido na coluna da tabela correspondente.

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

Opção #1 - Remover a linha

O método preferido para pular uma coluna envolve as três etapas a seguir:

1. Primeiro, exclua qualquer linha de arquivo de formato que descreva um campo ausente do arquivo de dados de origem.
2. Em seguida, reduza o valor de "Ordem dos campos do ficheiro anfitrião" de cada linha do ficheiro de formato que se segue a uma linha eliminada. O objetivo é valores sequenciais de "ordem de campo do arquivo host", de 1 a n, que refletem a posição real de cada campo de dados no arquivo de dados.
3. 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 é baseado no arquivo de formato padrão para a myTestSkipCol tabela. Esse arquivo de formato modificado mapeia o primeiro campo de dados para Col1, ignora Col2e mapeia o segundo campo de dados para Col3. A linha para Col2 foi excluída. O delimitador após o primeiro campo também foi alterado de \t para ,.

14.0
2
1       SQLCHAR       0       7       ","      1     Col1         ""
2       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

Opção #2 - Modificar a definição da linha

Como alternativa, para ignorar uma coluna de tabela, você pode modificar a definição da linha de arquivo de formato que corresponde à coluna da tabela. Nesta linha de 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, os campos "terminador" e "ordenação da coluna" devem ser definidos como "" (ou seja, para um valor vazio ou NULL). O valor "nome da coluna do servidor" requer uma cadeia de caracteres não em branco, embora o nome da coluna real não seja necessário. Os campos de formato restantes exigem seus valores padrão.

O exemplo a seguir também é derivado do arquivo de formato padrão para a myTestSkipCol tabela.

14.0
3
1       SQLCHAR       0       7       ","      1     Col1         ""
2       SQLCHAR       0       0       ""       0     Col2         ""
3       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

Exemplos com um arquivo de formato não XML

Os exemplos seguintes baseiam-se na tabela de exemplo myTestSkipCol e no ficheiro de dados de exemplo myTestSkipCol2.dat, que foram descritos anteriormente neste artigo.

Utilize BULK INSERT

Este exemplo funciona usando qualquer um dos arquivos de formato não-XML modificados criados conforme descrito na seção anterior. Neste exemplo, o arquivo de formato modificado é chamado myTestSkipCol2.fmt. Para usar BULK INSERT para importar em massa o myTestSkipCol2.dat arquivo de dados, no SQL Server Management Studio (SSMS), execute o código a seguir. Atualize os caminhos do sistema de arquivos para o local dos arquivos de exemplo no seu computador.

USE WideWorldImporters;
GO

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

SELECT *
FROM myTestSkipCol;
GO

Opção #2 - Usar um arquivo de formato XML

Etapa #1 - Criar um arquivo de formato XML padrão

Crie um arquivo de formato XML padrão para a myTestSkipCol tabela de exemplo executando o seguinte comando bcp no prompt de comando:

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

O comando anterior cria um arquivo de formato XML, myTestSkipCol_Default.xml. Esse arquivo de formato é chamado de arquivo de formato padrão porque é o formulário gerado pelo bcp. Um arquivo de formato padrão descreve uma correspondência um-para-um entre campos de arquivo de dados e colunas de tabela.

<?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="\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>

Etapa #2 - Modificar um arquivo de formato XML

Aqui está o arquivo de formato XML modificado, myTestSkipCol2.xml, que ignora Col2. As entradas FIELD e ROW de Col2 foram removidas e as entradas foram renumeradas. O delimitador após o primeiro campo também foi alterado de \t para ,.

<?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="\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>

Exemplos com um arquivo de formato XML

Os exemplos seguintes baseiam-se na tabela de exemplo myTestSkipCol e no ficheiro de dados de exemplo myTestSkipCol2.dat, que foram descritos anteriormente neste artigo.

Para importar os dados de myTestSkipCol2.dat para a tabela myTestSkipCol, os exemplos utilizam o ficheiro de formato XML modificado, myTestSkipCol2.xml.

Utilize BULK INSERT com uma vista

Com um arquivo de formato XML, você não pode pular uma coluna ao importar diretamente para uma tabela usando um comando bcp ou uma BULK INSERT instrução. No entanto, pode importar para todas as colunas de uma tabela, exceto a última. Se você tiver que ignorar qualquer coluna diferente da última coluna, deverá criar uma exibição da tabela de destino que contenha apenas as colunas contidas no arquivo de dados. Em seguida, você pode importar dados em massa desse arquivo para a exibição.

O exemplo seguinte cria o modo de exibição v_myTestSkipCol na tabela myTestSkipCol. Esta vista ignora a segunda coluna da tabela, Col2. O exemplo utiliza então BULK INSERT para importar o ficheiro de dados myTestSkipCol2.dat para esta vista.

No SSMS, execute o código a seguir. Atualize os caminhos do sistema de arquivos para o local dos arquivos de exemplo no seu computador.

USE WideWorldImporters;
GO

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

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

Utilize OPENROWSET(BULK...)

Para usar um arquivo de formato XML para ignorar uma coluna de tabela usando OPENROWSET(BULK...)o , você precisa fornecer uma lista explícita de colunas na lista de seleção e também na tabela de destino, da seguinte maneira:

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

O exemplo seguinte utiliza o fornecedor de conjuntos de linhas em massa OPENROWSET e o ficheiro de formato myTestSkipCol2.xml. O exemplo importa em massa o myTestSkipCol2.dat arquivo de dados para a myTestSkipCol tabela. A instrução contém uma lista explícita de colunas na lista de seleção e também na tabela de destino, conforme necessário.

No SSMS, execute o código a seguir. Atualize os caminhos do sistema de arquivos para o local dos arquivos de exemplo no seu computador.

USE WideWorldImporters;
GO

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

Conteúdo relacionado

• Utilitário bcp
• BULK INSERT (Transact-SQL)
• OPENROWSET (Transact-SQL)
• Usar um arquivo de formato para ignorar um campo de dados (SQL Server)
• Usar um arquivo de formato para mapear colunas de tabela para campos de arquivo de dados (SQL Server)
• Usar um arquivo de formato para importar dados em massa (SQL Server)