Manter valores nulos ou padrão durante a importação em massa (SQL Server)

Artigo
09/26/2022
8 minutos para o fim da leitura

Aplica-se a: SQL Server (todas as versões com suporte) 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.

Contorno
Manter valores nulos Usando valores padrão com INSERT... SELECT * FROM OPENROWSET(BULK...) Condições de teste de exemplo ● Tabela de amostra ● Arquivo de dados de amostra ● Arquivo em formato não XML de amostra Manter valores nulos ou use os valores padrão durante a importação em massa ● Como usar bcp e manter valores nulos sem um arquivo de formato ● Como usar bcp e manter valores nulos com um arquivo de formato não XML ● Como usar bcp e valores padrão sem um arquivo de formato ● Como usar bcp e valores padrão com um arquivo de formato não XML ● Como usar BULK INSERT e manter valores nulos sem um arquivo de formato ● Como usar BULK INSERT e manter valores nulos com um arquivo de formato não XML ● Como usar BULK INSERT e valores padrão sem um arquivo de formato ● Como usar BULK INSERT e valores padrão com um arquivo de formato não XML ● Como usar OPENROWSET(BULK...) e manter valores nulos com um arquivo de formato não XML ● Como usar OPENROWSET(BULK...) e valores padrão com um arquivo de formato não XML

Contorno

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	Opção
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 comando Transact-SQL no Microsoft SQL Server Management Studio (SSMS):

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 comando Transact-SQL no Microsoft SQL Server Management Studio (SSMS):

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 comando Transact-SQL no Microsoft SQL Server Management Studio (SSMS):

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 comando Transact-SQL no Microsoft SQL Server Management Studio (SSMS):

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 comando Transact-SQL no Microsoft SQL Server Management Studio (SSMS):

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