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 de SQL do AzureAzure Synapse AnalyticsPDW (Analytics Platform System)
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. Examine Criar um arquivo de formato (SQL Server) para obter mais informações.
Observação
Um arquivo de formato XML ou não XML pode ser usado para a importação em massa de um arquivo de dados para a tabela usando um comando do utilitário bcp, uma instrução BULK INSERT ou INSERT... Instrução SELECT * FROM OPENROWSET(BULK...). Para obter mais informações, confira Usar um arquivo de formato para importação de dados em massa (SQL Server).
Observação
Essa sintaxe, incluindo inserção em massa, não tem suporte no Azure Synapse Analytics. No Azure Synapse Analytics e em outras integrações de plataforma de banco de dados de nuvem, efetue 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.
Condições de teste de exemplo
Os exemplos de arquivos de formato modificados neste tópico baseiam-se na tabela e no arquivo de dados definidos abaixo.
Tabela de exemplo
O script abaixo cria um banco de dados de teste e uma tabela chamada myTestSkipField. Execute o seguinte comando 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 vazio D:\BCP\myTestSkipField.bcp e insira os seguintes dados:
1,SkipMe,Anthony,Grosse
2,SkipMe,Alica,Fatnowna
3,SkipMe,Stella,Rosenhain
Criar os arquivos de formato
Para importar em massa dados de myTestSkipField.bcp para a tabela myTestSkipField , o arquivo de formato deverá 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.
O método mais simples para criar o arquivo de formato é usando o utilitário bcp. Primeiro, crie um arquivo de formato base 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
Examine 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 caractere, t, é usado para especificar uma vírgula como um terminador de campo e T é usado para especificar uma conexão confiável usando a segurança integrada. No 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
Examine a estrutura de arquivos de formato não XML para obter a terminologia. Abra D:\BCP\myTestSkipField.fmt no Bloco de Notas e realize as seguintes modificações:
- Copie a linha inteira de arquivo de formato em
FirstNamee cole-a diretamente apósFirstNamena próxima linha. - Aumente o valor de ordem do campo de arquivo de host em um para a nova linha e todas as próximas linhas.
- Aumente o valor do número de colunas para que ele reflita o número real de campos no arquivo de dados.
- Modifique a ordem das colunas do servidor de
2para0na 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 do
myTestSkipField.bcpé mapeado para a primeira coluna,myTestSkipField.. PersonID - O segundo campo de dados do
myTestSkipField.bcpnão é mapeado para nenhuma coluna. - O terceiro campo de dados do
myTestSkipField.bcpé mapeado para a segunda coluna,myTestSkipField.. FirstName - O quarto campo de dados do
myTestSkipField.bcpé mapeado para a terceira coluna,myTestSkipField.. LastName
Criar um arquivo de formato XML
Examine 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. Além disso, o qualificador c é usado para especificar dados de caractere, t, é usado para especificar uma vírgula como um terminador de campo e T é usado para especificar uma conexão confiável usando a segurança integrada. O qualificador x deve ser usado para gerar um arquivo de formato baseado em XML. No 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
Examine Sintaxe de esquema para arquivos de formato XML para obter a terminologia. Abra D:\BCP\myTestSkipField.xml no Bloco de Notas e realize as seguintes modificações:
- Copie todo o segundo campo e cole-o diretamente após o segundo campo na próxima linha.
- Aumente o valor de “FIELD ID” em 1 para o novo FIELD e para cada campo posterior.
- Aumente o valor de “COLUMN SOURCE” em 1 para
FirstNameeLastNamepara que eles reflitam 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 à 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 à COLUMN 3 é mapeado para a segunda coluna da tabela,
myTestSkipField.. FirstName - FIELD 4 que corresponde à COLUMN 4 é mapeado para a terceira coluna da tabela,
myTestSkipField.. LastName
Importar dados com um arquivo de formato para ignorar um campo de dados
Os exemplos abaixo usam o banco de dados, o arquivo de dados e os arquivos de formato criados acima.
Usar bcp e arquivo de formato não XML
No prompt de comando, digite o seguinte comando:
bcp TestDatabase.dbo.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.fmt -T
Usar bcp e arquivo de formato XML
No prompt de comando, digite o seguinte comando:
bcp TestDatabase.dbo.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.xml -T
Usar BULK INSERT e arquivo de formato não XML
Execute o seguinte comando 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 BULK INSERT e arquivo de formato XML
Execute o seguinte comando 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;
Usar OPENROWSET(BULK...) e arquivo de formato não XML
Execute o seguinte comando 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...) e arquivo de formato XML
Execute o seguinte comando 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;
Próximas etapas
Comentários
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulte https://aka.ms/ContentUserFeedback.
Enviar e exibir comentários de