Usar um arquivo de formato para importar dados em massa (SQL Server)

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

Este artigo ilustra o uso de um arquivo de formato em operações de importação em massa. Um arquivo de formato mapeia os campos do arquivo de dados para as colunas da tabela. Consulte Criar um arquivo de formato com bcp (SQL Server) para obter informações adicionais.

Pré-requisitos

• Para executar comandos T-SQL em sua instância do SQL Server, use o SQL Server Management Studio (SSMS), a extensão MSSQL para Visual Studio Code, sqlcmd ou sua ferramenta de consulta T-SQL favorita.

• Para que um arquivo de formato funcione com um arquivo de dados de caractere Unicode, todos os campos de entrada devem ser cadeias de texto Unicode (ou seja, cadeias de caracteres Unicode de tamanho fixo ou terminadas por caracteres).

• Para exportar ou importar em massa Exemplos de importação e exportação em massa de dados de documentos XML (SQL Server), use um dos seguintes tipos de dados em seu arquivo de formato:

  • SQLCHAR ou SQLVARCHAR (os dados são enviados na página de código do cliente ou na página de código implícita pelo agrupamento)
  • SQLNCHAR ou SQLNVARCHAR (os dados são enviados como Unicode)
  • SQLBINARY ou SQLVARBIN (os dados são enviados sem qualquer conversão).

• A Base de Dados SQL do Azure e o Azure Synapse Analytics suportam apenas bcp. Para obter informações adicionais, veja:

  • Carregar dados no Azure Synapse Analytics
  • Carregar dados do SQL Server no Azure Synapse Analytics (arquivos simples)
  • Migre os dados

• 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 neste tópico são baseados 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 myFirstImport. Execute o seguinte Transact-SQL no Microsoft SQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.MyFirstImport (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30),
   BirthDate Date
   );

Arquivo de dados de exemplo

Usando o bloco de notas, crie um arquivo D:\BCP\myFirstImport.bcp vazio e insira os seguintes dados:

1,Anthony,Grosse,1980-02-23
2,Alica,Fatnowna,1963-11-14
3,Stella,Rosenhain,1992-03-02

Como alternativa, você pode executar o seguinte script do PowerShell para criar e preencher o arquivo de dados:

Clear-Host
# revise directory as desired

$dir = 'D:\BCP\';

$bcpFile = Join-Path -Path $dir -ChildPath 'MyFirstImport.bcp';

# Confirm directory exists

IF ((Test-Path -Path $dir) -eq 0)
{
    Write-Host "The path $dir does not exist; please create or modify the directory.";
    RETURN;
};

# Clear content, will error if file does not exist, can be ignored

Clear-Content -Path $bcpFile -ErrorAction SilentlyContinue;

# Add data

Add-Content -Path $bcpFile -Value '1,Anthony,Grosse,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,1963-11-14';
Add-Content -Path $bcpFile -Value '3,Stella,Rosenhain,1992-03-02';

# Review content

Get-Content -Path $bcpFile;
Notepad.exe $bcpfile;

Criar os arquivos de formato

O SQL Server oferece suporte a dois tipos de arquivo de formato: formato não XML e formato XML. O formato não XML é o formato original suportado por versões anteriores do SQL Server.

Altere os locais de arquivo local do exemplo de código para um local de arquivo em sua máquina.

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, myFirstImport.fmt, com base no esquema de myFirstImport.

• Para usar um comando bcp para criar um arquivo de formato, especifique o format argumento e use nul em vez de um caminho de arquivo de dados.
• A opção de formato também requer a -f opção.
• 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.

Em um prompt de comando, digite o seguinte comando:

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

REM Review file
Notepad D:\BCP\myFirstImport.fmt

Seu arquivo D:\BCP\myFirstImport.fmt de formato não-XML deve ter a seguinte aparência:

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

Importante

Verifique se o arquivo de formato não XML termina com um retorno de carro\feed de linha. Caso contrário, você provavelmente receberá a seguinte mensagem de erro:

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format file

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, myFirstImport.xml, com base no esquema de myFirstImport.

• Para usar um comando bcp para criar um arquivo de formato, especifique o format argumento e use nul em vez de um caminho de arquivo de dados.
• A opção de formato requer sempre a -f opção.
• Para criar um arquivo de formato XML, você também deve especificar a -x opção.
• 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.

Em um prompt de comando, digite o seguinte comando:

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

REM Review file
Notepad D:\BCP\myFirstImport.xml

Seu arquivo de formato XML, D:\BCP\myFirstImport.xml deve ter a seguinte aparência:

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

Usar um arquivo de formato para importar dados em massa

Os exemplos abaixo usam o banco de dados, o arquivo de dados e os arquivos de formato criados acima.

Usar bcp e usar arquivos de formato não XML (SQL Server)

Em um prompt de comando, digite o seguinte comando:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.MyFirstImport;"

REM Import data
bcp TestDatabase.dbo.myFirstImport IN D:\BCP\myFirstImport.bcp -f D:\BCP\myFirstImport.fmt -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.MyFirstImport"

Usar arquivos de formato bcp e XML (SQL Server)

Em um prompt de comando, digite o seguinte comando:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.MyFirstImport;"

REM Import data
bcp TestDatabase.dbo.myFirstImport IN D:\BCP\myFirstImport.bcp -f D:\BCP\myFirstImport.xml -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.MyFirstImport;"

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

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

USE TestDatabase;  
GO

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

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

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 myFirstImport; -- (for testing)
BULK INSERT dbo.myFirstImport  
   FROM 'D:\BCP\myFirstImport.bcp'  
   WITH (FORMATFILE = 'D:\BCP\myFirstImport.xml');  
GO

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

Usar OPENROWSET BULK (Transact-SQL) e Usar arquivos de formato não XML (SQL Server)

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

USE TestDatabase;
GO

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

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

Utilize OPENROWSET BULK (Transact-SQL) e arquivos de formato XML (SQL Server)

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

USE TestDatabase;  
GO

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

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

Mais exemplos

• Criar um arquivo de formato com bcp (SQL Server)
• Usar um arquivo de formato para ignorar uma coluna de tabela (SQL Server)
• Usar um arquivo de formato para ignorar um campo de dados (SQL Server)
• Usar um arquivo de formato para mapear colunas de tabela para campos de arquivo de dados (SQL Server)

Conteúdo relacionado

• Utilitário bcp
• INSERÇÃO A GRANEL (Transact-SQL)
• OPENROWSET BULK (Transact-SQL)
• Usar arquivos de formato não XML (SQL Server)
• Arquivos de formato XML (SQL Server)
• Formatar arquivos para importar ou exportar dados (SQL Server)