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 de SQL do AzureAzure 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 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.

Por outro lado, uma instrução regular INSERT retém o valor nulo em vez de inserir um valor padrão. O INSERT... SELECT * FROM OPENROWSET BULK instru provides the same basic behavior as regular INSERT but additionally supports a table hint for inserting the default values.

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 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	Qualificador	Tipo de qualificador
bcp	-k	Comutador
BULK INSERT	KEEPNULLS*	Argumento
INSERT ... SELECT * FROM OPENROWSET(BULK...)	N/D	N/D

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

Observação

Esses qualificadores desabilitam a verificação de DEFAULT definições em uma tabela por esses comandos de importação em massa. No entanto, para quaisquer instruções simultâneas INSERT , DEFAULT as definições são esperadas.

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 dicas de tabela.

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

Condições de teste de exemplo

Os exemplos usam o banco 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 seu computador.

Tabela de exemplo

O script cria um banco de dados de teste e uma tabela chamada myNulls. A quarta coluna Kidsda tabela 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 D:\BCP\myNulls.bcp vazio e insira os dados de exemplo a seguir. Não há nenhum 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;

Arquivo de formato não XML de exemplo

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. 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.fmtcom 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 de formato também requer a opção -f.
• c é usado para especificar dados de caractere
• t, é usado para especificar uma vírgula como um terminador de campo
• 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, 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.

Usar bcp e manter valores nulos sem um arquivo de formato

A -k opção.

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

Usar bcp e manter valores nulos com um arquivo de formato não XML

O -k e -f alterna.

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

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

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

A -f opção.

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

Usar BULK INSERT e manter valores nulos sem um arquivo de formato

O argumento KEEPNULLS.

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;

Usar BULK INSERT e manter valores nulos com um arquivo de formato não XML

O KEEPNULLS argumento e o FORMATFILE argumento.

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;

Usar BULK INSERT e usar 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;

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

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'
        );

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

Usar OPENROWSET BULK e manter valores nulos com um arquivo de formato não XML

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
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;

Usar OPENROWSET BULK e manter valores padrão com um arquivo de formato não XML

A dica e FORMATFILE o argumento da KEEPDEFAULTS tabela.

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;

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 importação em massa de dados (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 no formato nativo e de caractere de versões anteriores do SQL Server

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

• Usar o 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 terminadores de linha e de campo (SQL Server)

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

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

Conteúdo relacionado

• BACKUP (Transact-SQL)
• OPENROWSET (Transact-SQL)
• OPENROWSET BULK (Transact-SQL)
• Utilitário bcp
• BULK INSERT (Transact-SQL)
• Dicas de tabela (Transact-SQL)