Usar um arquivo de formato para importação em massa de dados (SQL Server)
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 ilustra o uso de um arquivo de formato operações de importação em massa. Um arquivo de formato mapeia os campos do arquivo de dados para as colunas da tabela. Examine Criar um arquivo de formato (SQL Server) para obter mais informações.
Antes de começar
- Para um arquivo de formato funcionar com um arquivo de dados de caractere Unicode, todos os campos de entrada devem ser cadeias de caracteres de texto Unicode (isto é, cadeias de caracteres Unicode de tamanho fixo ou terminadas por caractere).
- Para exportar ou importar dados SQLXML em massa, use um dos tipos de dados a seguir 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 na ordenação)
- SQLNCHAR ou SQLNVARCHAR (os dados são enviados como Unicode)
- SQLBINARY ou SQLVARBIN (os dados são enviados sem nenhuma conversão).
- O Banco de Dados SQL do Azure e o Azure Synapse Analytics dão suporte somente a bcp. Para obter informações adicionais, consulte:
- 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 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 myFirstImport
. Execute o seguinte script Transact-SQL no Microsoft SSMS (SQL Server Management Studio):
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 vazio D:\BCP\myFirstImport.bcp
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 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.
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, myFirstImport.fmt
, com base no esquema de myFirstImport
. 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 . Além disso, neste exemplo, o qualificador c é usado para especificar dados de caractere, t é usado para especificar uma vírgula como um terminador de campoe 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.myFirstImport format nul -c -f D:\BCP\myFirstImport.fmt -t, -T
REM Review file
Notepad D:\BCP\myFirstImport.fmt
Seu arquivo de formato não XML, D:\BCP\myFirstImport.fmt
, deve se assemelhar ao seguinte:
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/alimentação 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
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, myFirstImport.xml
, com base no esquema de myFirstImport
. 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 . Além disso, neste exemplo, o qualificador c é usado para especificar dados de caractere, t é usado para especificar uma vírgula como um terminador de campoe 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.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 se assemelhar ao seguinte:
<?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 importação em massa 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:
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 bcp e arquivo de formato XML
No 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 e arquivo de formato não XML
Execute o seguinte script Transact-SQL no Microsoft SSMS (SQL Server Management Studio):
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 BULK INSERT e arquivo de formato XML
Execute o seguinte script Transact-SQL no Microsoft SSMS (SQL Server Management Studio):
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...) e arquivo de formato não XML
Execute o seguinte script Transact-SQL no Microsoft SSMS (SQL Server Management Studio):
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;
Usar OPENROWSET(BULK...) e arquivo de formato XML
Execute o seguinte script Transact-SQL no Microsoft SSMS (SQL Server Management Studio):
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 formato de arquivo (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)