Introdução aos arquivos de formato
Um arquivo que é usado para armazenar informações de formato para cada campo em um arquivo de dados relativo a uma tabela específica é chamado de arquivo de formato. Um arquivo de formato fornece todas as informações de formato necessárias para importar ou exportar dados em massa. Um arquivo de formato fornece um sistema flexível para gravar arquivos de dados que exigem pouca ou nenhuma edição para estar em conformidade com outros formatos de dados ou para ler arquivos de dados de outro software.
No Microsoft SQL Server 2000 e versões anteriores, a importação e exportação em massa trabalham com um único tipo de arquivo de formato. Isso ainda tem suporte. Entretanto, o SQL Server 2005 e versões posteriores também oferecem suporte a arquivos de formato XML como alternativa. Os arquivos de formato do tipo original são chamados de arquivos de formato não XML.
Todos os arquivos de formato contêm descrições de cada campo em um arquivo de dados. Os arquivos de formato XML também contêm descrições das colunas de tabela correspondentes. Geralmente, arquivos de formato XML e não XML são intercambiáveis. No entanto, recomendamos usar a sintaxe XML para novos arquivos de formato porque eles apresentam várias vantagens em relação aos arquivos de formato não XML. Um arquivo de formato XML possui as seguintes características:
Autoexplicativo e fácil de ler, criar e estender.
Contém os tipos de dados das colunas de destino.
Isso permite a separação entre como os dados são representados no arquivo de dados e quais tipos de dados são associados a cada campo no arquivo. Por exemplo, se um arquivo de dados contiver uma representação de caracteres dos dados, o tipo de coluna SQL correspondente será perdido.
O comando bcp e a instrução BULK INSERT usam as colunas da tabela de destino para fazer a conversão de tipo. Isso torna necessária uma tabela de destino. Ao contrário, a função OPENROWSET(BULK...) depende de um arquivo de formato XML para ler dados de um arquivo de dados. Isso torna a tabela de destino opcional.
Permite carregar um campo que contenha um único tipo de dado de objeto (LOB) grande de um arquivo de dados.
A sintaxe de um arquivo de formato é independente da direção da operação; ou seja, a sintaxe é a mesma para exportação ou importação em massa.
Para obter informações sobre o layout de cada tipo de arquivo de formato, consulte "Exemplos", mais abaixo neste tópico.
Quando um arquivo de formato é necessário?
Uma instrução INSERT... SELECT * FROM OPENROWSET(BULK...) sempre exige um arquivo de formato.
- Para bcp ou BULK INSERT, em situações simples, usar um arquivo de formato é opcional e raramente necessário. No entanto, em situações complexas de importação em massa, um arquivo de formato é frequentemente necessário.
Os arquivos de formato serão necessários se:
O mesmo arquivo de dados for usado como origem para diversas tabelas que têm esquemas diferentes.
O arquivo de dados tiver um número diferente de campos do número de colunas da tabela de destino; por exemplo:
A tabela de destino contém pelo menos uma coluna para a qual há um valor padrão definido ou é permitido NULL.
Os usuários não têm permissões SELECT/INSERT em uma ou mais colunas na tabela.
Um único arquivo de dados é usado com duas ou mais tabelas que têm esquemas diferentes.
A ordem de colunas é diferente para o arquivo de dados e a tabela.
Os caracteres de terminação ou o comprimento dos prefixos diferem entre as colunas do arquivo de dados.
.gif "Observação") Observação |
|---|
Na ausência de um arquivo de formato, se um comando bcp especificar uma opção de formato de dados (- n, - c, - w ou - N) ou uma operação BULK INSERT especificar a opção DATAFILETYPE, o formato de dados especificado será usado como o método padrão de interpretação dos campos do arquivo de dados. |
Exemplos
Os exemplos abaixo mostram o layout de um arquivo de formato não XML e de um arquivo de formato XML. Esses arquivos de formato correspondem à tabela HumanResources.myTeam no banco de dados de exemplo AdventureWorks2008R2. Essa tabela contém quatro colunas: EmployeeID, Name, Title e ModifiedDate.
| Observação |
|---|
Para obter informações sobre essa tabela e sobre como criá-la, consulte Criando a tabela HumanResources.myTeam. |
A. Usando um arquivo de formato não XML
O arquivo de formato não XML seguinte usa o formato de dados nativo do SQL Server para a tabela HumanResources.myTeam. Esse arquivo de formato foi criado usando o comando bcp a seguir.
bcp AdventureWorks2008R2.HumanResources.myTeam format nul -f myTeam.Fmt -n -T
The contents of this format file are as follows: 9.0
4
1 SQLSMALLINT 0 2 "" 1 EmployeeID ""
2 SQLNCHAR 2 100 "" 2 Name SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 2 100 "" 3 Title SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 2 100 "" 4 Background SQL_Latin1_General_CP1_CI_AS
Para obter mais informações, consulte Compreendendo arquivos de formato não XML.
B. Usando um arquivo de formato XML
O seguinte arquivo de formato XML usa o formato de dados nativo do SQL Server para a tabela HumanResources.myTeam. Esse arquivo de formato foi criado usando o comando bcp a seguir.
bcp AdventureWorks2008R2.HumanResources.myTeam format nul -f myTeam.Xml -x -n -T
O arquivo de formato contém:
<?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="NativePrefix" LENGTH="1"/>
<FIELD ID="2" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="4" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="EmployeeID" xsi:type="SQLSMALLINT"/>
<COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="3" NAME="Title" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="4" NAME="Background" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>
Para obter mais informações, consulte Compreendendo arquivos no formato XML.