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

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada SQL do Azure do Azure Synapse AnalyticsAnalytics Platform System (PDW)

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 ignorada tem um valor padrão.

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, realize a movimentação de dados por meio da instrução COPY no Azure Data Factory ou usando instruções T-SQL, como COPY INTO e PolyBase.

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:

Use o utilitário de linha de comando bcp para criar um arquivo de formato padrão.
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 para a myTestSkipCol tabela, o arquivo de formato deve mapear o primeiro campo de myTestSkipCol2.dat dados para Col1, ignorar Col2e 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

Importante

Talvez seja necessário especificar o nome da instância do servidor à qual você está se conectando com o -S argumento. Além disso, talvez seja necessário especificar o nome de usuário e a senha com os -U argumentos e -P . Para obter mais informações, consulte bcp Utility.

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.

Diagrama detalhando o arquivo de formato não-XML padrão para mytestskipcol.

Observação

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

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:

Primeiro, exclua qualquer linha de arquivo de formato que descreva um campo ausente do arquivo de dados de origem.
Em seguida, reduza o valor "Ordem de campo do arquivo host" de cada linha de arquivo de formato que segue uma linha excluída. 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.
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 "agrupamento de colunas" devem ser definidos como "" (ou seja, para um valor ou NULL vazio). 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 a myTestSkipCol seguir são baseados na tabela de exemplo e no myTestSkipCol2.dat arquivo de dados de exemplo descritos anteriormente neste artigo.

Usar 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

Importante

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>

Observação

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

Etapa #2 - Modificar um arquivo de formato XML

Aqui está o arquivo de formato XML modificado, myTestSkipCol2.xml, que ignora Col2. As FIELD entradas e ROW para 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 a myTestSkipCol seguir são baseados na tabela de exemplo e no myTestSkipCol2.dat arquivo de dados de exemplo descritos anteriormente neste artigo.

Para importar os dados da tabela, os myTestSkipCol exemplos usam o arquivo de myTestSkipCol2.dat formato XML modificado, myTestSkipCol2.xml.

Usar BULK INSERT com um modo de exibição

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, você pode importar para todas, exceto a última coluna de uma tabela. 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 a myTestSkipCol seguir cria o v_myTestSkipCol modo de exibição na tabela. Esta vista ignora a segunda coluna da tabela, Col2. O exemplo é usado BULK INSERT para importar o myTestSkipCol2.dat arquivo de dados para essa exibição.

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

Use 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 a seguir usa o provedor de OPENROWSET conjunto de linhas em massa e o arquivo de myTestSkipCol2.xml formato. 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

Comentários

Esta página foi útil?

Last updated on 2025-09-09

Partilhar via

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

Tabela de exemplo e arquivo de dados

Passos básicos

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

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

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

Opção #1 - Remover a linha

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

Exemplos com um arquivo de formato não XML

Usar BULK INSERT

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

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

Etapa #2 - Modificar um arquivo de formato XML

Exemplos com um arquivo de formato XML

Usar BULK INSERT com um modo de exibição

Use OPENROWSET(BULK...)

Conteúdo relacionado

Comentários

Recursos adicionais