Criar um arquivo de formato com bcp (SQL Server)

Artigo
12/06/2023

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics PDW (Analytics Platform System)

Este artigo descreve como usar o utilitário bcp para criar um arquivo de formato para uma tabela específica. O arquivo de formato se baseia na opção do tipo de dados especificada (-n, -c, -w ou -N) e nos delimitadores de exibição ou tabela.

Ao importar em massa para uma tabela do SQL Server ou exportar em massa dados de uma tabela, você pode usar um arquivo de formato como um sistema flexível para gravar arquivos de dados. Arquivos de formato exigem pouca ou nenhuma edição para estar em conformidade com outros formatos de dados ou para ler arquivos de dados de outros programas de software.

Limitações

A versão do utilitário bcp (bcp.exe) usada para ler um arquivo de formato deve ser igual ou posterior à versão usada para criar o arquivo de formato. Por exemplo, o SQL Server 2016 (13.x) bcp pode ler um arquivo no formato da versão 12.0, que é gerado pelo SQL Server 2014 (12.x) bcp, mas o SQL Server 2014 (12.x) bcp não pode ler um arquivo de formato da versão 13.0, que é gerado pelo SQL Server 2016 (13.x) bcp.

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.

Crie arquivos de formato

O SQL Server dá suporte a dois tipos de arquivo de formato: XML e não XML. O formato não XML é o formato original com suporte em versões anteriores do SQL Server.

Geralmente, arquivos de formato XML e não XML são intercambiáveis. Entretanto, recomendamos que você use a sintaxe XML para arquivos de formato porque eles oferecem diversas vantagens em relação aos arquivos de formato não XML.

Arquivo de formato XML
Arquivo de formato não XML

Os exemplos de código do Transact-SQL deste artigo usa o banco de dados de exemplo AdventureWorks2022 ou AdventureWorksDW2022, que pode ser baixado da home page Microsoft SQL Server Samples and Community Projects. A Adventure Works Cycles é uma empresa de fabricação fictícia utilizada para demonstrar conceitos e cenários de banco de dados.

Criar um arquivo de formato XML

Para usar um comando bcp para criar um arquivo de formato, especifique o argumento format e use nul em vez de um caminho de arquivo de dados. A opção format sempre exige a opção -f e, para criar um arquivo de formato XML, é necessário especificar também a opção -x, como bcp <table_or_view> format nul -f <format_file_name> -x.

Para diferenciar um arquivo de formato XML recomendamos que você use .xml como a extensão do nome de arquivo, por exemplo, MyTable.xml.

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

Exemplos

Esta seção contém os seguintes exemplos que mostram como usar comandos bcp para criar um arquivo de formato XML. A tabela HumanResources.Department contém quatro colunas: DepartmentID, Name, GroupNamee ModifiedDate.

R. Criar um arquivo de formato XML para dados de caracteres

O exemplo a seguir cria um arquivo de formato XML, Department.xml, para a tabela HumanResources.Department . O arquivo de formato usa formatos de dados de caracteres e um terminador de campo não padrão (,). O conteúdo do arquivo de formato gerado é apresentado após o comando.

O comando bcp contém os qualificadores a seguir.

Qualificadores	Descrição
`format nul -x -f <format_file>`	Especifica o arquivo de formato XML.
`-c`	Especifica dados de caracteres.
`-t,`	Especifica uma vírgula (`,`) como terminador de campo. Observação: Se o arquivo de dados usar o terminador de campo padrão (`\t`), o parâmetro `-t` será desnecessário.
`-T`	Especifica que o utilitário bcp se conecta ao SQL Server com uma conexão confiável usando segurança integrada. Se `-T` não for especificado, especifique `-U` e `-P` para iniciar a sessão com êxito.

No prompt de comando do Windows, digite o seguinte comando bcp :

bcp AdventureWorks2022.HumanResources.Department format nul -c -x -f Department-c.xml -t, -T

O arquivo de formato gerado, Department-c.xml, contém os seguintes elementos XML:

<?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="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="24"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>
</ROW>
</BCPFORMAT>

Para obter informações sobre a sintaxe deste arquivo de formato XML, veja Arquivos de formato XML (SQL Server). Para obter informações sobre dados de caractere, veja Usar a formatação de caracteres para importar ou exportar dados (SQL Server).

B. Criar um arquivo de formato XML para dados nativos

O exemplo a seguir cria um arquivo de formato XML, Department-n.xml, para a tabela HumanResources.Department . O arquivo de formato usa tipos de dados nativos. O conteúdo do arquivo de formato gerado é apresentado após o comando.

O comando bcp contém os qualificadores a seguir.

Qualificadores	Descrição
`format nul -x -f <format_file>`	Especifica o arquivo de formato XML.
`-n`	Especifica tipos de dados nativos.
`-T`	Especifica que o utilitário bcp se conecta ao SQL Server com uma conexão confiável usando segurança integrada. Se `-T` não for especificado, especifique `-U` e `-P` para iniciar a sessão com êxito.

No prompt de comando do Windows, digite o seguinte comando bcp :

bcp AdventureWorks2022.HumanResources.Department format nul -x -f Department-n.xml -n -T

O arquivo de formato gerado, Department-n.xml, contém os seguintes elementos XML:

<?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="NativeFixed" LENGTH="2"/>
  <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="NativeFixed" LENGTH="8"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>
</ROW>
</BCPFORMAT>

Para obter informações sobre a sintaxe deste arquivo de formato XML, veja Arquivos de formato XML (SQL Server). Para obter informações sobre como usar dados nativos, veja Usar o formato nativo para importar ou exportar dados (SQL Server).

Criar um arquivo de formato não XML

Para usar um comando bcp para criar um arquivo de formato, especifique o argumento format e use nul em vez de um caminho de arquivo de dados. A opção format também exige a opção -f, como: bcp <table_or_view> format nul -f <format_file_name>.

Para diferenciar um arquivo de formato não XML recomendamos que você use .fmt como a extensão do nome de arquivo, por exemplo, MyTable.fmt.

Para obter informações sobre a estrutura e os campos dos arquivos de formato não XML, veja Usar arquivos de formato não XML (SQL Server).

Exemplos

Esta seção contém os seguintes exemplos que mostram como usar comandos bcp para criar um arquivo de formato não XML. A tabela HumanResources.Department contém quatro colunas: DepartmentID, Name, GroupNamee ModifiedDate.

R. Criar um arquivo de formato não XML para dados nativos

O comando bcp contém os qualificadores a seguir.

Qualificadores	Descrição
`format nul -f <format_file>`	Especifica o arquivo de formato não XML.
`-n`	Especifica tipos de dados nativos.
`-T`	Especifica que o utilitário bcp se conecta ao SQL Server com uma conexão confiável usando segurança integrada. Se `-T` não for especificado, especifique `-U` e `-P` para iniciar a sessão com êxito.

No prompt de comando do Windows, digite o seguinte comando bcp :

bcp AdventureWorks2022.HumanResources.Department format nul -T -n -f Department-n.fmt

O arquivo de formato gerado, Department-n.fmt, contém as seguintes informações:

12.0
4
1  SQLSMALLINT   0       2       ""   1     DepartmentID         ""
2  SQLNCHAR      2       100     ""   2     Name                 SQL_Latin1_General_CP1_CI_AS
3  SQLNCHAR      2       100     ""   3     GroupName            SQL_Latin1_General_CP1_CI_AS
4  SQLDATETIME   0       8       ""   4     ModifiedDate         ""

Para obter mais informações, veja Usar arquivos de formato não XML (SQL Server).

B. Criar um arquivo de formato não XML para dados de caracteres

O exemplo a seguir cria um arquivo de formato XML, Department.fmt, para a tabela HumanResources.Department . O arquivo de formato usa formatos de dados de caracteres e um terminador de campo não padrão (,). O conteúdo do arquivo de formato gerado é apresentado após o comando.

O comando bcp contém os qualificadores a seguir.

Qualificadores	Descrição
`format nul -f <format_file>`	Especifica um arquivo de formato não XML.
`-c`	Especifica dados de caracteres.
`-T`	Especifica que o utilitário bcp se conecta ao SQL Server com uma conexão confiável usando segurança integrada. Se `-T` não for especificado, especifique `-U` e `-P` para iniciar a sessão com êxito.

No prompt de comando do Windows, digite o seguinte comando bcp :

bcp AdventureWorks2022.HumanResources.Department format nul -c -f Department-c.fmt -T

O arquivo de formato gerado, Department-c.fmt, contém as seguintes informações:

12.0
4
1  SQLCHAR       0       7       "\t"     1     DepartmentID            ""
2  SQLCHAR       0       100     "\t"     2     Name                    SQL_Latin1_General_CP1_CI_AS
3  SQLCHAR       0       100     "\t"     3     GroupName               SQL_Latin1_General_CP1_CI_AS
4  SQLCHAR       0       24      "\r\n"   4     ModifiedDate            ""

Para obter mais informações, veja Usar arquivos de formato não XML (SQL Server).

C. Criar um arquivo de formato não XML para dados nativos Unicode

Para criar um arquivo de formato não XML para dados nativos Unicode para a tabela HumanResources.Department, use o seguinte comando:

bcp AdventureWorks2022.HumanResources.Department format nul -T -N -f Department-n.fmt

Para obter mais informações sobre como usar dados nativos Unicode, veja Usar o formato nativo Unicode para importar ou exportar dados (SQL Server).

D. Criar um arquivo de formato não XML para dados de caracteres Unicode

Para criar um arquivo de formato não XML para dados de caracteres Unicode para a tabela HumanResources.Department que usa terminadores padrão, utilize o seguinte comando:

bcp AdventureWorks2022.HumanResources.Department format nul -T -w -f Department-w.fmt

Para obter mais informações sobre como usar dados de caractere Unicode, veja Usar a formatação de caracteres Unicode para importar ou exportar dados (SQL Server).

F. Usar um formato de arquivo com a opção de página de código

Se você criar um arquivo de formato usando bcp (isto é, usando bcp format), as informações sobre a ordenação/página de código serão gravadas no arquivo de formato.

O arquivo de formato de exemplo a seguir, para uma tabela com cinco colunas, inclui a ordenação.

13.0
5
1  SQLCHAR         0       0       "\t"             1     c_0          Cyrillic_General_CS_AS
2  SQLCHAR         0       0       "\t"             2     c_1          Cyrillic_General_CS_AS
3  SQLCHAR         0       3000    "\t"             3     c_2          Cyrillic_General_CS_AS
4  SQLCHAR         0       5       "\t"             4     c_3          ""
5  SQLCHAR         0       41      "!!!\r\r\n"      5     c_4          ""

Se você tentar importar dados para o SQL Server usando bcp in -c -C65001 -f format_file ..." ou "BULK INSERT/OPENROWSET ... FORMATFILE='format_file' CODEPAGE=65001 ...", as informações sobre a ordenação/página de código terão prioridade sobre a opção 65001.

Portanto, se você gerar um arquivo de formato, você deverá excluir manualmente as informações de ordenação do arquivo de formato gerado antes de iniciar a importação de dados de volta para o SQL Server.

O exemplo a seguir mostra o arquivo de formato sem as informações de ordenação.

13.0
5
1  SQLCHAR         0       0       "\t"             1     c_0              ""
2  SQLCHAR         0       0       "\t"             2     c_1              ""
3  SQLCHAR         0       3000    "\t"             3     c_2              ""
4  SQLCHAR         0       5       "\t"             4     c_3              ""
5  SQLCHAR         0       41      "!!!\r\r\n"      5     c_4              ""

Mapear campos de dados para colunas de tabelas

Como criado pelo bcp, um arquivo de formato descreve todas as colunas da tabela em ordem. Você pode modificar um arquivo de formato para reorganizar ou omitir linhas de tabela. Você pode personalizar um arquivo de formato em um arquivo de dados cujos campos não são mapeados diretamente para as colunas da tabela. Para obter mais informações, consulte os seguintes artigos:

Compartilhar via

Criar um arquivo de formato com bcp (SQL Server)

Limitações

Crie arquivos de formato

Criar um arquivo de formato XML

Exemplos

R. Criar um arquivo de formato XML para dados de caracteres

B. Criar um arquivo de formato XML para dados nativos

Criar um arquivo de formato não XML

Exemplos

R. Criar um arquivo de formato não XML para dados nativos

B. Criar um arquivo de formato não XML para dados de caracteres

C. Criar um arquivo de formato não XML para dados nativos Unicode

D. Criar um arquivo de formato não XML para dados de caracteres Unicode

F. Usar um formato de arquivo com a opção de página de código

Mapear campos de dados para colunas de tabelas

Comentários

Recursos adicionais

Compartilhar via

Criar um arquivo de formato com bcp (SQL Server)

Limitações

Crie arquivos de formato

Criar um arquivo de formato XML

Exemplos

R. Criar um arquivo de formato XML para dados de caracteres

B. Criar um arquivo de formato XML para dados nativos

Mapear campos de dados para colunas de tabelas

Conteúdo relacionado

Comentários

Recursos adicionais