Manter valores nulos ou use os valores padrão durante a importação em massa (SQL Server)Keep Nulls or Use Default Values During Bulk Import (SQL Server)

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure simAzure Synapse Analytics (SQL DW) simParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

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.By default, when data is imported into a table, the bcp command and BULK INSERT statement observe any defaults that are defined for the columns in the table. Por exemplo, se houver um campo nulo em um arquivo de dados, o valor padrão para a coluna será carregado no campo nulo.For example, if there is a null field in a data file, the default value for the column is loaded instead. O comando bcp e a instrução BULK INSERT permitem que você especifique a retenção de campos nulos.The bcp command and BULK INSERT statement both allow you to specify that nulls values be retained.

Em contraste, uma instrução INSERT regular retém o valor nulo em vez de inserir um valor padrão.In contrast, a regular INSERT statement retains the null value instead of inserting a default value. 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.The INSERT ... SELECT * FROM OPENROWSET(BULK...) statement provides the same basic behavior as regular INSERT but additionally supports a table hint for inserting the default values.

ContornoOutline
Manter valores nulosKeeping Null Values
Usando valores padrão com INSERT... SELECT * FROM OPENROWSET(BULK...)Using Default Values with INSERT ... SELECT * FROM OPENROWSET(BULK...)
Condições de teste de exemploExample Test Conditions
 ● Tabela de exemplo ● Sample Table
 ● Arquivo de dados de exemplo ● Sample Data File
 ● Arquivo de formato não XML de exemplo ● Sample Non-XML Format File
Manter valores nulos ou use os valores padrão durante a importação em massaKeep Nulls or Use Default Values During Bulk Import
 ● Usando bcp e mantendo valores nulos sem um arquivo de formato ● Using bcp and Keeping Null Values without a Format File
 ● Usando bcp e mantendo valores nulos com um arquivo de formato não XML ● Using bcp and Keeping Null Values with a Non-XML Format File
 ● Usando bcp e valores padrão sem um arquivo de formato ● Using bcp and Using Default Values without a Format File
 ● Usando bcp e valores padrão com um arquivo de formato não XML ● Using bcp and Using Default Values with a Non-XML Format File
 ● Usando BULK INSERT e mantendo valores nulos sem um arquivo de formato ● Using BULK INSERT and Keeping Null Values without a Format File
 ● Usando BULK INSERT e mantendo valores nulos com um arquivo de formato não XML ● Using BULK INSERT and Keeping Null Values with a Non-XML Format File
 ● Usando BULK INSERT e valores padrão sem um arquivo de formato ● Using BULK INSERT and Using Default Values without a Format File
 ● Usando BULK INSERT e valores padrão com um arquivo de formato não XML ● Using BULK INSERT and Using Default Values with a Non-XML Format File
 ● Usando OPENROWSET(BULK...) e mantendo valores nulos com um arquivo de formato não XML ● Using OPENROWSET(BULK...) and Keeping Null Values with a Non-XML Format File
 ● Usando OPENROWSET(BULK...) e valores padrão com um arquivo de formato não XML ● Using OPENROWSET(BULK...) and Using Default Values with a Non-XML Format File

Manter valores nulosKeeping Null Values

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.The following qualifiers specify that an empty field in the data file retains its null value during the bulk-import operation, rather than inheriting a default value (if any) for the table columns. Para OPENROWSET, por padrão, toda coluna não especificada na operação de carregamento em massa é definida como NULL.For OPENROWSET, by default, any columns that are not specified in the bulk-load operation are set to NULL.

ComandoCommand QualificadorQualifier Tipo de qualificadorQualifier type
bcpbcp -k-k OpçãoSwitch
BULK INSERTBULK INSERT KEEPNULLS*KEEPNULLS* ArgumentoArgument
INSERT ... SELECT * FROM OPENROWSET(BULK...)INSERT ... SELECT * FROM OPENROWSET(BULK...) N/AN/A N/AN/A

* Para BULK INSERT, se os valores padrão não estiverem disponíveis, a coluna de tabela deverá ser definida para permitir valores nulos.* For BULK INSERT, if default values are not available, the table column must be defined to allow null values.

Observação

Esses qualificadores desabilitam a verificação de definições DEFAULT em uma tabela por esses comandos de importação em massa.These qualifiers disable checking of DEFAULT definitions on a table by these bulk-import commands. No entanto, para qualquer instrução INSERT simultânea, são previstas definições DEFAULT.However, for any concurrent INSERT statements, DEFAULT definitions are expected.

Usar valores padrão com INSERT... SELECT * FROM OPENROWSET(BULK...)Using Default Values with 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).You can specify that for an empty field in the data file, the corresponding table column uses its default value (if any). Para usar valores padrão, use a dica de tabela KEEPDEFAULTS.To use default values, use the table hint KEEPDEFAULTS.

Condições de teste de exemploExample Test Conditions

Os exemplos neste tópico baseiam-se na tabela, no arquivo de dados e no arquivo de formato definidos abaixo.The examples in this topic are based on the table, data file, and format file defined below.

Tabela de exemploSample Table

O script abaixo cria um banco de dados de teste e uma tabela chamada myNulls.The script below creates a test database and a table named myNulls. Observe que a quarta coluna de tabela, Kids, tem um valor padrão.Notice that the fourth table column, Kids, has a default value. Execute o seguinte comando Transact-SQL no Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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 ExemploSample Data File

Usando o Bloco de Notas, crie um arquivo vazio D:\BCP\myNulls.bcp e insira os dados abaixo.Using Notepad, create an empty file D:\BCP\myNulls.bcp and insert the data below. Observe que não há nenhum valor no terceiro registro, na quarta coluna.Note that there is no value in the third record, fourth column.

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:Alternatively, you can execute the following PowerShell script to create and populate the data file:

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 XMLSample Non-XML Format File

O SQL Server dá suporte a dois tipos de arquivo de formato: XML e não XML.SQL Server support two types of format file: non-XML format and XML format. O formato não XML é o formato original com suporte em versões anteriores do SQL Server.The non-XML format is the original format that is supported by earlier versions of SQL Server. Examine Arquivos de formato não XML (SQL Server) para obter informações detalhadas.Please review Non-XML Format Files (SQL Server) for detailed information. 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.The following command will use the bcp utility to generate a non-xml format file, myNulls.fmt, based on the schema of 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.To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. A opção format também exige a opção -f .The format option also requires the -f option. 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.In addition, for this example, the qualifier c is used to specify character data, t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. No prompt de comando, digite o seguinte comando:At a command prompt, enter the following command:

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.Ensure your non-XML format file ends with a carriage return\line feed. Caso contrário, você provavelmente receberá a seguinte mensagem de erro:Otherwise you will likely receive the following error message:

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).For more information about creating format files, see Create a Format File (SQL Server).

Manter valores nulos ou use os valores padrão durante a importação em massaKeep Nulls or Use Default Values During Bulk Import

Os exemplos abaixo usam o banco de dados, o arquivo de dados e os arquivos de formato criados acima.The examples below use the database, datafile, and format files created above.

Como usar bcp e manter valores nulos sem um arquivo de formatoUsing bcp and Keeping Null Values without a Format File

Opção -k .-k switch. No prompt de comando, digite o seguinte comando:At a command prompt, enter the following command:

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 Using bcp and Keeping Null Values with a Non-XML Format File

Opções -k e -f .-k and -f switches. No prompt de comando, digite o seguinte comando:At a command prompt, enter the following command:

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 formatoUsing bcp and Using Default Values without a Format File

No prompt de comando, digite o seguinte comando:At a command prompt, enter the following command:

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 Using bcp and Using Default Values with a Non-XML Format File

Opção -f .-f switch. No prompt de comando, digite o seguinte comando:At a command prompt, enter the following command:

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 formatoUsing BULK INSERT and Keeping Null Values without a Format File

ArgumentoKEEPNULLS .KEEPNULLS argument. Execute o seguinte comando Transact-SQL no Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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 Using BULK INSERT and Keeping Null Values with a Non-XML Format File

KEEPNULLS e o argumento FORMATFILE .KEEPNULLS and the FORMATFILE argument. Execute o seguinte comando Transact-SQL no Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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 formatoUsing BULK INSERT and Using Default Values without a Format File

Execute o seguinte comando Transact-SQL no Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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 Using BULK INSERT and Using Default Values with a Non-XML Format File

ArgumentoFORMATFILE .FORMATFILE argument. Execute o seguinte comando Transact-SQL no Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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 Using OPENROWSET(BULK...) and Keeping Null Values with a Non-XML Format File

ArgumentoFORMATFILE .FORMATFILE argument. Execute o seguinte comando Transact-SQL no Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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;

Como usar OPENROWSET(BULK...) e valores padrão com um arquivo de formato não XML Using OPENROWSET(BULK...) and Using Default Values with a Non-XML Format File

Dica de tabelaKEEPDEFAULTS e argumento FORMATFILE .KEEPDEFAULTS table hint and FORMATFILE argument. Execute o seguinte comando Transact-SQL no Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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 relacionadasRelated Tasks

Para usar um arquivo de formatoTo use a format file

Para usar formatos de dados para importação ou exportação em massaTo use data formats for bulk import or bulk export

Para especificar formatos de dados para compatibilidade usando bcpTo specify data formats for compatibility when using bcp

Consulte TambémSee Also

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