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

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

Um arquivo de dados pode conter mais campos do que o número de colunas na tabela. Este artigo descreve a modificação de arquivos de formato não XML e XML para acomodar um arquivo de dados com mais campos, mapeando as colunas da tabela para os campos de dados correspondentes e ignorando os campos extras.

Para obter mais informações, consulte Criar um arquivo de formato com bcp (SQL Server).

Observação

Um arquivo de formato não XML ou XML pode ser usado para importar em massa um arquivo de dados para a tabela usando um comando utilitário bcp, instrução BULK INSERT (Transact-SQL) ou instrução INSERT ... SELECT * FROM OPENROWSET BULK (Transact-SQL). Para obter mais informações, consulte Usar um arquivo de formato para importar dados em massa (SQL Server).

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

Exemplo de condições de ensaio

Os exemplos de arquivos de formato modificado neste artigo são baseados na tabela myTestSkipField de exemplo e no arquivo D:\BCP\myTestSkipField.bcpde dados . Altere o local do arquivo local no exemplo de código para um local de arquivo em sua máquina.

Tabela de exemplo

O script cria um banco de dados de teste e uma tabela chamada myTestSkipField. Execute o seguinte Transact-SQL no Microsoft SQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE myTestSkipField
   (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30)
   );

Arquivo de dados de exemplo

Crie um arquivo D:\BCP\myTestSkipField.bcp vazio e insira os seguintes dados:

1,SkipMe,Anthony,Grosse
2,SkipMe,Alica,Fatnowna
3,SkipMe,Stella,Rosenhain

Criar os arquivos de formato

Para importar dados em grandes quantidades de myTestSkipField.bcp para a tabela de myTestSkipField, o ficheiro de formato myTestSkipField.bcp deve fazer o seguinte:

• Mapeie o primeiro campo de dados para a primeira coluna, PersonID.
• Ignore o segundo campo de dados.
• Mapeie o terceiro campo de dados para a segunda coluna, FirstName.
• Mapeie o quarto campo de dados para a terceira coluna, LastName.

O método mais simples para criar o arquivo de formato é usando o utilitário bcp. Primeiro, crie um arquivo de formato base a partir da tabela existente. Em segundo lugar, modifique o arquivo de formato base para refletir o arquivo de dados real.

Criar um arquivo de formato não XML

Consulte Usar arquivos de formato não XML (SQL Server) para obter informações detalhadas. O comando a seguir usará o utilitário bcp para gerar um arquivo de formato não-xml, myTestSkipField.fmt, com base no esquema de myTestSkipField. Além disso, o qualificador c é usado para especificar dados de caracteres, t, é usado para especificar uma vírgula como terminador de campo e T é usado para especificar uma conexão confiável usando segurança integrada. Em um prompt de comando, digite o seguinte comando:

bcp TestDatabase.dbo.myTestSkipField format nul -c -f D:\BCP\myTestSkipField.fmt -t, -T

Modificar o arquivo de formato não-XML

Analise a estrutura de arquivos de formato não-XML para obter terminologia. Abra D:\BCP\myTestSkipField.fmt no Bloco de Notas e execute as seguintes modificações:

1. Copie toda a linha do arquivo de formato FirstName e cole-a imediatamente após FirstName na linha seguinte.
2. Aumente o valor da ordem de campo do arquivo host em um para a nova linha e todas as linhas subsequentes.
3. Aumente o valor do número de colunas para refletir o número real de campos no arquivo de dados.
4. Modifique a ordem das colunas do servidor de 2 para 0 na segunda linha do arquivo de formato.

Compare as alterações feitas:

Antes

13.0
3
1       SQLCHAR    0       7       ","      1     PersonID        ""
2       SQLCHAR    0       25      ","      2     FirstName    SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR    0       30      "\r\n"   3     LastName     SQL_Latin1_General_CP1_CI_AS

Depois

13.0
4
1       SQLCHAR    0       7       ","      1     PersonID     ""
2       SQLCHAR    0       25      ","      0     FirstName    SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR    0       25      ","      2     FirstName    SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR    0       50      "\r\n"   3     LastName     SQL_Latin1_General_CP1_CI_AS

O arquivo de formato modificado agora reflete:

• 4 campos de dados
• O primeiro campo de dados em myTestSkipField.bcp é mapeado para a primeira coluna, myTestSkipField.. PersonID.
• O segundo campo de dados em myTestSkipField.bcp não está mapeado para nenhuma coluna.
• O terceiro campo de dados em myTestSkipField.bcp é mapeado para a segunda coluna, myTestSkipField.. FirstName
• O quarto campo de dados em myTestSkipField.bcp é mapeado para a terceira coluna, myTestSkipField.. LastName

Criar um arquivo de formato XML

Analise os arquivos de formato XML (SQL Server) para obter informações detalhadas. O comando a seguir usará o utilitário bcp para criar um arquivo de formato xml, myTestSkipField.xml, com base no esquema de myTestSkipField.

• O qualificador c é usado para especificar dados de caracteres
• t, é usado para especificar uma vírgula como um terminador de campo
• T é usado para especificar uma conexão confiável usando segurança integrada.
• O x qualificador deve ser usado para gerar um arquivo de formato baseado em XML.

Em um prompt de comando, digite o seguinte comando:

bcp TestDatabase.dbo.myTestSkipField format nul -c -x -f D:\BCP\myTestSkipField.xml -t, -T

Modificar o arquivo de formato XML

Revise a sintaxe do esquema para arquivos de formato XML para obter terminologia. Abra D:\BCP\myTestSkipField.xml no Bloco de Notas e execute as seguintes modificações:

1. Copie todo o segundo campo e cole-o diretamente após o segundo campo na próxima linha.
2. Aumente o valor do FIELD ID em 1 para o novo FIELD e para cada FIELD subsequente.
3. Aumente o COLUMN SOURCE valor em 1 para FirstNamee LastName reflita o mapeamento revisado.

Compare as alterações feitas:

Antes

<?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="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

Depois

<?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="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="3" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="LastName" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

O arquivo de formato modificado agora reflete:

• 4 campos de dados
• FIELD 1 que corresponde a COLUMN 1 é mapeado para a primeira coluna da tabela, myTestSkipField.. PersonID
• FIELD 2 não corresponde a nenhuma COLUMN e, portanto, não está mapeado para nenhuma coluna da tabela.
• FIELD 3 que corresponde a COLUMN 3 está associado à segunda coluna da tabela, myTestSkipField.. FirstName
• FIELD 4 que corresponde a COLUMN 4 está associado à terceira coluna da tabela, myTestSkipField.. LastName

Importar dados com um arquivo de formato para ignorar um campo de dados

Os exemplos usam o banco de dados de exemplo, arquivo de dados e arquivos de formato criados neste artigo.

Usar bcp e arquivo em formato não XML

Em um prompt de comando, digite o seguinte comando:

bcp TestDatabase.dbo.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.fmt -T

Usar arquivos de formato bcp e XML (SQL Server)

Em um prompt de comando, digite o seguinte comando:

bcp TestDatabase.dbo.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.xml -T

Use BULK INSERT (Transact-SQL) e ficheiro em formato não XML

Execute o seguinte Transact-SQL no Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myTestSkipField;
BULK INSERT dbo.myTestSkipField  
   FROM 'D:\BCP\myTestSkipField.bcp'  
   WITH (FORMATFILE = 'D:\BCP\myTestSkipField.fmt');  
GO

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

Usar arquivos de formato BULK INSERT (Transact-SQL) e XML (SQL Server)

Execute o seguinte Transact-SQL no Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myTestSkipField;
BULK INSERT dbo.myTestSkipField  
   FROM 'D:\BCP\myTestSkipField.bcp'  
   WITH (FORMATFILE = 'D:\BCP\myTestSkipField.xml');  
GO

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

Utilize OPENROWSET BULK (Transact-SQL) e arquivo de formato não-XML

Execute o seguinte Transact-SQL no Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE myTestSkipField;
INSERT INTO dbo.myTestSkipField
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myTestSkipField.bcp',
        FORMATFILE = 'D:\BCP\myTestSkipField.fmt'
        ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

Usar OPENROWSET BULK (Transact-SQL) e ficheiros de formato XML (SQL Server)

Execute o seguinte Transact-SQL no Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myTestSkipField;
INSERT INTO dbo.myTestSkipField  
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myTestSkipField.bcp',
        FORMATFILE = 'D:\BCP\myTestSkipField.xml'  
       ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

Conteúdo relacionado

• Utilitário bcp
• INSERÇÃO A GRANEL (Transact-SQL)
• OPENROWSET (Transact-SQL)
• Usar um arquivo de formato para ignorar uma coluna de tabela (SQL Server)
• Usar um arquivo de formato para mapear colunas de tabela para campos de arquivo de dados (SQL Server)