Partilhar via

  • Artigo

Usando um arquivo de formato para importar dados em massa

Este tópico ilustra o uso de um arquivo de formato operações de importação em massa. O arquivo de formato mapeia os campos do arquivo de dados para as colunas da tabela. No SQL Server 2005 e em versões posteriores, você pode usar um arquivo de formato não XML ou XML para importar dados em massa usando um comando bcp ou BULK INSERT ou INSERT... SELECT * FROM OPENROWSET(BULK...) Comando Transact-SQL.

Observação importanteImportante

Para um arquivo de formato para trabalhar com um arquivo de dados de caractere Unicode, todos os campos de entrada devem ser cadeias de caracteres de texto Unicode (isto é, devem ser cadeias de caracteres Unicode de tamanho fixo ou terminadas por caractere).
ObservaçãoObservação

Se não estiver familiarizado com arquivos de formato, consulte Compreendendo arquivos de formato não XML e Compreendendo arquivos no formato XML.

Opções arquivo de formato de dados para comandos de importação em massa

A tabela a seguir resume a opção de arquivo de formato para cada um dos comandos de importação em massa.

Comando de carregamento em massa

Usando a opção de Arquivo de Formato

BULK INSERT

FORMATFILE = 'format_file_path'

INSERT ... SELECT * FROM OPENROWSET(BULK...)

FORMATFILE = 'format_file_path'

bcp. in

-fformat_file

Para obter mais informações, consulte Utilitário bcp, BULK INSERT (Transact-SQL) ou OPENROWSET (Transact-SQL).

ObservaçãoObservação

Para importar ou exportar dados SQLXML em massa, use um dos seguintes tipos de dados em seu arquivo de formato: SQLCHAR ou SQLVARYCHAR (os dados são enviados na página de código de cliente ou na página de código indicada pelo agrupamento), SQLNCHAR ou SQLNVARCHAR (os dados são enviados como Unicode) ou SQLBINARY ou SQLVARYBIN (os dados são enviados sem nenhuma conversão).

Exemplos

Os exemplos nesta seção ilustram como usar arquivos de formato para importar dados em massa usando o comando bcp e o BULK INSERT e INSERT... SELECT * FROM OPENROWSET(BULK...) instruções. Antes de poder executar um dos exemplos de importação em massa, você precisa criar uma amostra de tabela, arquivo de dados e arquivo de formato.

Tabela de exemplo

Os exemplos de importação exigem que uma tabela denominada myTestFormatFiles seja criada no banco de dados do exemplo AdventureWorks2008R2 no esquema dbo. Para criar esta tabela, no Editor de Consultas do SQL Server Management Studio, execute:

USE AdventureWorks2008R2;
GO
CREATE TABLE myTestFormatFiles (
   Col1 smallint,
   Col2 nvarchar(50),
   Col3 nvarchar(50),
   Col4 nvarchar(50)
   );
GO

Arquivo de dados de exemplo

Os exemplos usam um arquivo de dados de amostra, myTestFormatFiles-c.Datque contém os seguintes registros. Para criar esse arquivo de dados, no prompt de comando do Microsoft Windows, digite:

10,Field2,Field3,Field4
15,Field2,Field3,Field4
46,Field2,Field3,Field4
58,Field2,Field3,Field4

Arquivo de formato de exemplo

Alguns dos exemplos nessa seção usam um arquivo de formato XML, myTestFormatFiles-f-x-c.Xmle, outros exemplos usam um arquivo de formato não XML. Ambos os arquivos de formato usam formatos de dados de caracteres e um terminador de campo não padrão (,).

O exemplo de arquivo de formato não XML

O exemplo a seguir usa bcp para gerar um arquivo de formato XML a partir da tabela myTestFormatFiles. O arquivo de formato myTestFormatFiles.Fmtcontém as seguintes informações:

9.0
4
1       SQLCHAR       0       7       ","      1     Col1         ""
2       SQLCHAR       0       100     ","      2     Col2         SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       100     ","      3     Col3         SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       100     "\r\n"   4     Col4         SQL_Latin1_General_CP1_CI_AS

Para usar bcp com a opção format para criar esse arquivo de formato, no prompt de comando do Windows, digite:

bcp AdventureWorks2008R2..MyTestFormatFiles format nul -c -t, -f myTestFormatFiles.Fmt -T

Para obter mais informações sobre como criar um arquivo de formato, consulte Criando um arquivo de formato.

O arquivo de formato XML de exemplo

O exemplo a seguir usa bcp para criar a geração de um arquivo de formato XML a partir da tabela myTestFormatFiles. O arquivo de formato myTestFormatFiles.Xmlcontém as seguintes informações:

<?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="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="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"/>
  <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

Para usar bcp com a opção format para criar este arquivo de formato, no prompt de comando do Windows, digite:

bcp AdventureWorks2008R2..MyTestFormatFiles format nul -c -t, -x -f myTestFormatFiles.Xml -T

Usando bcp

O exemplo a seguir usa bcp para importar em massa dados do arquivo de dados myTestFormatFiles-c.Dat na tabela HumanResources.myTestFormatFiles no banco de dados de exemplo AdventureWorks2008R2. Este exemplo usa um arquivo de formato XML, MyTestFormatFiles.Xml. O exemplo exclui as linhas existentes na tabela antes de importar o arquivo de dados.

No prompt de comando do Windows, digite:

bcp AdventureWorks2008R2..myTestFormatFiles in C:\myTestFormatFiles-c.Dat -f C:\myTestFormatFiles.Xml -T
ObservaçãoObservação

Para obter mais informações sobre esse comando, consulte Utilitário bcp.

Usando BULK INSERT

O exemplo a seguir usa BULK INSERT para importar em massa dados do arquivo de dados myTestFormatFiles-c.Dat na tabela HumanResources.myTestFormatFiles no banco de dados de exemplo AdventureWorks2008R2. Este exemplo usa um arquivo de formato não XML, MyTestFormatFiles.Fmt. O exemplo exclui as linhas existentes na tabela antes de importar o arquivo de dados.

No Editor de Consultas do SQL Server Management Studio, execute:

USE AdventureWorks2008R2;
GO
DELETE myTestFormatFiles;
GO
BULK INSERT myTestFormatFiles 
   FROM 'C:\myTestFormatFiles-c.Dat' 
   WITH (FORMATFILE = 'C:\myTestFormatFiles.Fmt');
GO
SELECT * FROM myTestFormatFiles;
GO
ObservaçãoObservação

Para obter mais informações sobre esta instrução, consulte BULK INSERT (Transact-SQL).

Usando o Provedor de Conjuntos de Linhas em Massa OPENROWSET

O exemplo a seguir usa INSERT ... SELECT * FROM OPENROWSET(BULK...)para importar em massa dados do arquivo de dados myTestFormatFiles-c.Dat na tabela HumanResources.myTestFormatFiles no banco de dados de exemplo AdventureWorks2008R2. Este exemplo usa um arquivo de formato XML, MyTestFormatFiles.Xml. O exemplo exclui as linhas existentes na tabela antes de importar o arquivo de dados.

No Editor de Consultas do SQL Server Management Studio, execute:

USE AdventureWorks2008R2;
DELETE myTestFormatFiles;
GO
INSERT INTO myTestFormatFiles
    SELECT *
      FROM  OPENROWSET(BULK  'C:\myTestFormatFiles-c.Dat',
      FORMATFILE='C:\myTestFormatFiles.Xml'     
      ) as t1 ;
GO
SELECT * FROM myTestFormatFiles;
GO

Quando você terminar de usar a tabela de amostra, pode descartá-la usando a instrução:

DROP TABLE myTestFormatFiles
ObservaçãoObservação

Para obter mais informações sobre a cláusula OPENROWSET BULK, consulte OPENROWSET (Transact-SQL).