Manter valores nulos ou padrão durante a importação 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)

Por padrão, quando os dados são importados para uma tabela, o comando bcp e a instrução BULK INSERT observam todos 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. O comando bcp e a instrução BULK INSERT permitem especificar que os valores nulos sejam mantidos.

Por outro lado, uma instrução regular INSERT 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 que a inserção regular, mas também suporta 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 manté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 da tabela. Para OPENROWSET BULK, por padrão, todas as colunas que não são especificadas na operação de carregamento em massa são definidas como NULL.

Comando	Eliminatórias	Tipo de qualificador
bcp	-k	Alternar
BULK INSERT	KEEPNULLS*	Argumento
INSERT ... SELECT * FROM OPENROWSET(BULK...)	N/A	N/A

* Para BULK INSERT (Transact-SQL), se os valores padrão não estiverem disponíveis, a coluna da tabela deve ser definida para permitir valores nulos.

Observação

Esses qualificadores desativam a verificação das definições de DEFAULT numa tabela por estes comandos de importação em massa. No entanto, são esperadas definições INSERT para quaisquer declarações simultâneas DEFAULT.

Use valores padrão com INSERT ... SELECT * FROM OPENROWSET BULK

Você pode especificar que, para um campo vazio no arquivo de dados, a coluna da tabela correspondente usa seu valor padrão (se houver). Para usar valores padrão, use sugestões de tabela.

Para obter mais informações, consulte OPENROWSET BULK.

Exemplo de condições de ensaio

Os exemplos usam a base de dados e os arquivos de formato criados neste artigo.

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

Tabela de exemplo

O script cria um banco de dados de teste e uma tabela chamada myNulls. A quarta coluna da tabela, Kids, tem um valor padrão. Execute o seguinte 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 D:\BCP\myNulls.bcp vazio e insira os seguintes dados de exemplo. Não há valor no terceiro registro, 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 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. Para obter mais informações, consulte Usar arquivos de formato não XML (SQL Server).

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 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.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\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

Para obter mais informações sobre como criar arquivos de formato, consulte Criar um arquivo de formato com bcp (SQL Server).

Manter nulos ou usar valores padrão durante a importação em massa

Os exemplos usam o banco de dados, o arquivo de dados e os arquivos de formato criados neste artigo.

Use bcp e mantenha valores nulos sem um arquivo de formato

O -k interruptor.

Em um 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;"

Use bcp e mantenha valores nulos com um arquivo de formato não-XML

Os -k e -f interruptores.

Em um 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;"

Usar bcp e valores padrão sem um arquivo de formato

Em um 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;"

Usar bcp e valores padrão com um ficheiro de formato não XML

O -f interruptor.

Em um 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;"

Use BULK INSERT e mantenha valores nulos sem um arquivo de formato

O KEEPNULLS argumento.

Execute o seguinte 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;

Use BULK INSERT e mantenha valores nulos com um arquivo de formato não XML

O KEEPNULLS e o FORMATFILE argumento.

Execute o seguinte 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;

Use BULK INSERT e use valores padrão sem um arquivo de formato

Execute o seguinte 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;

Use BULK INSERT e valores padrão com um arquivo de formato não XML

O FORMATFILE argumento.

Execute o seguinte 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;

Use OPENROWSET BULK e mantenha valores nulos com um arquivo de formato não-XML

O FORMATFILE argumento.

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

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;

Use OPENROWSET BULK e mantenha os valores padrão com um arquivo de formato não XML

A dica da tabela KEEPDEFAULTS e o argumento FORMATFILE.

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

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;

Tarefas relacionadas

• Manter valores de identidade ao importar dados em massa (SQL Server)

• Preparar dados para exportação ou importação em massa

Para usar um arquivo de formato

• Criar um arquivo de formato com bcp (SQL Server)

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

• Usar um arquivo de formato para mapear colunas de tabela para campos de arquivo 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 nativos e de formato de caractere de versões anteriores do SQL Server

• Usar formato de caractere para importar ou exportar dados (SQL Server)

• Usar 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 ao usar bcp

• Especificar terminadores de campo e linha (SQL Server)

• Especificar o comprimento do prefixo em arquivos de dados usando bcp (SQL Server)

• Especificar o tipo de armazenamento de arquivos usando bcp (SQL Server)

Conteúdo relacionado

• CÓPIA DE SEGURANÇA (Transact-SQL)
• OPENROWSET (Transact-SQL)
• OPENROWSET BULK (Transact-SQL)
• Utilitário bcp
• INSERÇÃO A GRANEL (Transact-SQL)
• Dicas de tabela (Transact-SQL)