Manter valores nulos ou padrão durante a importação em massa (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)
Por padrão, quando os dados são importados para uma tabela, o comando bcp e a instrução BULK INSERT observam os padrões definidos para as colunas na tabela. Por exemplo, se houver um campo nulo em um arquivo de dados, o valor padrão para a coluna será carregado no campo nulo. O comando bcp e a instrução BULK INSERT permitem que você especifique a retenção de campos nulos.
Em contraste, uma instrução INSERT regular retém o valor nulo em vez de inserir um valor padrão. A instrução INSERT ... SELECT * FROM OPENROWSET(BULK...) fornece o mesmo comportamento básico de INSERT regular, mas, além disso, dá suporte a uma dica de tabela para inserir os valores padrão.
Manter valores nulos
Os qualificadores a seguir especificam que um campo vazio no arquivo de dados retém seu valor nulo durante a operação de importação em massa, em vez de herdar um valor padrão (se houver) para as colunas de tabela. Para OPENROWSET, por padrão, toda coluna não especificada na operação de carregamento em massa é definida como NULL.
Comando | Qualificador | Tipo de qualificador |
---|---|---|
bcp | -k | Comutador |
BULK INSERT | KEEPNULLS* | Argumento |
INSERT ... SELECT * FROM OPENROWSET(BULK...) | N/D | N/D |
* Para BULK INSERT, se os valores padrão não estiverem disponíveis, a coluna de tabela deverá ser definida para permitir valores nulos.
Observação
Esses qualificadores desabilitam a verificação de definições DEFAULT em uma tabela por esses comandos de importação em massa. No entanto, para qualquer instrução INSERT simultânea, são previstas definições DEFAULT.
Usar valores padrão com INSERT... SELECT * FROM OPENROWSET(BULK...)
Você pode especificar que para um campo vazio no arquivo de dados, a coluna de tabela correspondente use seu valor padrão (se houver). Para usar valores padrão, use a dica de tabela KEEPDEFAULTS.
Observação
Para obter mais informações, consulte INSERT (Transact-SQL),SELECT (Transact-SQL), OPENROWSET (Transact-SQL) e Dicas de Tabela (Transact-SQL)
Condições de teste de exemplo
Os exemplos neste tópico baseiam-se na tabela, no arquivo de dados e no arquivo de formato definidos abaixo.
Tabela de exemplo
O script abaixo cria um banco de dados de teste e uma tabela chamada myNulls
. Observe que a quarta coluna de tabela, Kids
, tem um valor padrão. Execute o seguinte script Transact-SQL no Microsoft SSMS (SQL Server Management Studio):
CREATE DATABASE TestDatabase;
GO
USE TestDatabase;
CREATE TABLE dbo.myNulls (
PersonID smallint not null,
FirstName varchar(25),
LastName varchar(30),
Kids varchar(13) DEFAULT 'Default Value',
BirthDate date
);
Arquivo de dados de exemplo
Usando o Bloco de Notas, crie um arquivo vazio D:\BCP\myNulls.bcp
e insira os dados abaixo. Observe que não há nenhum valor no terceiro registro, na quarta coluna.
1,Anthony,Grosse,Yes,1980-02-23
2,Alica,Fatnowna,No,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:
cls
# revise directory as desired
$dir = 'D:\BCP\';
$bcpFile = $dir + 'MyNulls.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,Yes,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,No,1963-11-14';
Add-Content -Path $bcpFile -Value '3,Stella,Rosenhain,,1992-03-02';
#Review content
Get-Content -Path $bcpFile;
Invoke-Item $bcpFile;
Exemplo de arquivo de formato não XML
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. 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, myNulls.fmt
, com base no esquema de myNulls
. 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.myNulls format nul -c -f D:\BCP\myNulls.fmt -t, -T
REM Review file
Notepad D:\BCP\myNulls.fmt
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
Para obter mais informações sobre como criar arquivos de formato, veja Criar um arquivo de formato (SQL Server).
Manter valores nulos ou use os valores padrão durante a importação em massa
Os exemplos abaixo usam o banco de dados, o arquivo de dados e os arquivos de formato criados acima.
Como usar bcp e manter valores nulos sem um arquivo de formato
Opção-k . No prompt de comando, digite o seguinte comando:
REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"
REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -c -t, -T -k
REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"
Como usar bcp e manter valores nulos com um arquivo de formato não XML
Opções-k e -f . No prompt de comando, digite o seguinte comando:
REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"
REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -f D:\BCP\myNulls.fmt -T -k
REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"
Como usar bcp e valores padrão sem um arquivo de formato
No prompt de comando, digite o seguinte comando:
REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"
REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -c -t, -T
REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"
Como usar bcp e valores padrão com um arquivo de formato não XML
Opção-f . No prompt de comando, digite o seguinte comando:
REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"
REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -f D:\BCP\myNulls.fmt -T
REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"
Como usar BULK INSERT e manter valores nulos sem um arquivo de formato
ArgumentoKEEPNULLS . Execute o seguinte script Transact-SQL no Microsoft SSMS (SQL Server Management Studio):
USE TestDatabase;
GO
TRUNCATE TABLE dbo.myNulls; -- for testing
BULK INSERT dbo.myNulls
FROM 'D:\BCP\myNulls.bcp'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
KEEPNULLS
);
-- review results
SELECT * FROM TestDatabase.dbo.myNulls;
Como usar BULK INSERT e manter valores nulos com um arquivo de formato não XML
KEEPNULLS e o argumento FORMATFILE . Execute o seguinte script Transact-SQL no Microsoft SSMS (SQL Server Management Studio):
USE TestDatabase;
GO
TRUNCATE TABLE dbo.myNulls; -- for testing
BULK INSERT dbo.myNulls
FROM 'D:\BCP\myNulls.bcp'
WITH (
FORMATFILE = 'D:\BCP\myNulls.fmt',
KEEPNULLS
);
-- review results
SELECT * FROM TestDatabase.dbo.myNulls;
Como usar BULK INSERT e valores padrão sem um arquivo de formato
Execute o seguinte script Transact-SQL no Microsoft SSMS (SQL Server Management Studio):
USE TestDatabase;
GO
TRUNCATE TABLE dbo.myNulls; -- for testing
BULK INSERT dbo.myNulls
FROM 'D:\BCP\myNulls.bcp'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ','
);
-- review results
SELECT * FROM TestDatabase.dbo.myNulls;
Como usar BULK INSERT e valores padrão com um arquivo de formato não XML
ArgumentoFORMATFILE . Execute o seguinte script Transact-SQL no Microsoft SSMS (SQL Server Management Studio):
USE TestDatabase;
GO
TRUNCATE TABLE dbo.myNulls; -- for testing
BULK INSERT dbo.myNulls
FROM 'D:\BCP\myNulls.bcp'
WITH (
FORMATFILE = 'D:\BCP\myNulls.fmt'
);
-- review results
SELECT * FROM TestDatabase.dbo.myNulls;
Como usar OPENROWSET(BULK...) e manter valores nulos com um arquivo de formato não XML
ArgumentoFORMATFILE . Execute o seguinte script Transact-SQL no Microsoft SSMS (SQL Server Management Studio):
USE TestDatabase;
GO
TRUNCATE TABLE dbo.myNulls; -- for testing
INSERT INTO dbo.myNulls
SELECT *
FROM OPENROWSET (
BULK 'D:\BCP\myNulls.bcp',
FORMATFILE = 'D:\BCP\myNulls.fmt'
) AS t1;
-- review results
SELECT * FROM TestDatabase.dbo.myNulls;
Como usar OPENROWSET(BULK...) e valores padrão com um arquivo de formato não XML
Dica de tabelaKEEPDEFAULTS e argumento FORMATFILE . Execute o seguinte script Transact-SQL no Microsoft SSMS (SQL Server Management Studio):
USE TestDatabase;
GO
TRUNCATE TABLE dbo.myNulls; -- for testing
INSERT INTO dbo.myNulls
WITH (KEEPDEFAULTS)
SELECT *
FROM OPENROWSET (
BULK 'D:\BCP\myNulls.bcp',
FORMATFILE = 'D:\BCP\myNulls.fmt'
) AS t1;
-- review results
SELECT * FROM TestDatabase.dbo.myNulls;
Related Tasks
Manter valores de identidade ao importar dados em massa (SQL Server)
Preparar dados para exportação ou importação em massa (SQL Server)
Para usar um arquivo de formato
Usar um arquivo de formato para importação em massa de dados (SQL Server)
Usar um arquivo de formato para ignorar um campo de dados (SQL Server)
Usar um arquivo de formato para ignorar uma coluna de tabela (SQL Server)
Para usar formatos de dados para importação ou exportação em massa
Importar dados de formato de caractere e nativo de versões anteriores do SQL Server
Usar o formato de caractere para importar ou exportar dados (SQL Server)
Usar um formato nativo para importar ou exportar dados (SQL Server)
Usar o formato de caractere Unicode para importar ou exportar dados (SQL Server)
Usar o formato nativo Unicode para importar ou exportar dados (SQL Server)
Para especificar formatos de dados para compatibilidade usando bcp
Especificar o tamanho de prefixo em arquivos de dados usando bcp (SQL Server)
Especificar tipo de armazenamento de arquivo usando bcp (SQL Server)
Confira também
BACKUP (Transact-SQL)
OPENROWSET (Transact-SQL)
Utilitário bcp
BULK INSERT (Transact-SQL)
Dicas de tabela (Transact-SQL)