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

Artigo
06/25/2013

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 SELECT * FROM OPENROWSET(BULK...) fornece o mesmo comportamento básico de INSERT regular, mas, além disso, oferece suporte a uma dica de tabela para inserir os valores padrão.

Observação
Para arquivos de formato de exemplo que ignoram uma coluna de tabela, consulte Usar um arquivo de formato para ignorar uma coluna de tabela (SQL Server).

Tabela e arquivo de dados de exemplo

Para executar os exemplos neste tópico, é necessário criar uma tabela e um arquivo de dados de exemplo.

Tabela de exemplo

Os exemplos requerem que uma tabela denominada MyTestDefaultCol2 seja criada no banco de dados de exemplo AdventureWorks no esquema dbo. Para criar essa tabela, no Editor de Consultas do Microsoft SQL Server Management Studio, execute:

USE AdventureWorks;
GO
CREATE TABLE MyTestDefaultCol2 
(Col1 smallint,
Col2 nvarchar(50) DEFAULT 'Default value of Col2',
Col3 nvarchar(50) 
);
GO

Observe que a segunda coluna de tabela, Col2, tem um valor padrão.

Arquivo de formato de exemplo

Alguns dos exemplos de importação em massa usam um formato de arquivo não XML, MyTestDefaultCol2-f-c.Fmt que correspondem exatamente à tabela MyTestDefaultCol2. Para criar esse arquivo de formato, no prompt de comando do Microsoft Windows, digite:

bcp AdventureWorks..MyTestDefaultCol2 format nul -c -f C:\MyTestDefaultCol2-f-c.Fmt -t, -r\n -T

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

Arquivo de dados de exemplo

O exemplo usa um arquivo de dados de exemplo, MyTestEmptyField2-c.Dat, que não contém nenhum valor no segundo campo. O arquivo de dados MyTestEmptyField2-c.Dat contém os registros a seguir.

1,,DataField3
2,,DataField3

Mantendo valores nulos com bcp ou BULK INSERT

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.

Comando	Qualificador	Tipo de qualificador
bcp	-k	Opção
BULK INSERT	KEEPNULLS1	Argumento

1 Para BULK INSERT, se os valores padrão não estiverem disponíveis, a coluna de tabela deve 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.

Para obter mais informações, consulte Utilitário bcp e BULK INSERT (Transact-SQL).

Exemplos

Os exemplos nesta seção efetuam a importação em massa usando bcp ou BULK INSERT e mantêm valores nulos.

A segunda coluna de tabela, Col2, tem um valor padrão. O campo correspondente do arquivo de dados contém uma cadeia de caracteres vazia. Por padrão, quando bcp ou BULK INSERT é usado para importar dados desse arquivo de dados para a tabela MyTestDefaultCol2, o valor padrão de Col2 é inserido, produzindo o seguinte resultado:

1	Default value of Col2	DataField3
2	Default value of Col2	DataField3

Para inserir "NULL" em vez de "Default value of Col2", é necessário usar a opção -k ou a opção KEEPNULL, conforme demonstrado nos exemplos bcp e BULK INSERT a seguir.

Usando bcp e mantendo valores nulos

O exemplo a seguir demonstra como manter valores nulos em um comando bcp. O comando bcp contém as seguintes opções:

Opção	Descrição
-f	Especifica que o comando está usando um arquivo de formato...
-k	Especifica que colunas vazias devem reter um valor nulo durante a operação, em vez de qualquer valor padrão nas colunas inseridas.
-T	Especifica que o utilitário bcp faz conexão com o SQL Server por meio de uma conexão confiável.

No prompt de comando do Windows, digite:

bcp AdventureWorks..MyTestDefaultCol2 in C:\MyTestEmptyField2-c.Dat -f C:\MyTestDefaultCol2-f-c.Fmt -k -T

Usando BULK INSERT e mantendo valores nulos

O exemplo a seguir demonstra como usar a opção KEEPNULLS em uma instrução BULK INSERT. De uma ferramenta de consulta, como o Editor de Consultas do SQL Server Management Studio, execute:

USE AdventureWorks;
GO
BULK INSERT MyTestDefaultCol2
   FROM 'C:\MyTestEmptyField2-c.Dat'
   WITH (
      DATAFILETYPE = 'char',
      FIELDTERMINATOR = ',',
      KEEPNULLS
   );
GO

Mantendo valores padrão com INSERT...SELECT * FROM OPENROWSET(BULK...)

Por padrão, qualquer coluna não especificada na operação de carregamento em massa é definida como NULL por INSERT... SELECT * FROM OPENROWSET(BULK...). Porém, 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, especifique a seguinte dica de tabela:

Comando	Qualificador	Tipo de qualificador
INSERT ... SELECT * FROM OPENROWSET(BULK...)	WITH(KEEPDEFAULTS)	Dica de tabela

Observação
Para obter mais informações, consulte INSERT (Transact-SQL), SELECT (Transact-SQL), OPENROWSET (Transact-SQL) e Dicas de tabela (Transact-SQL)

Exemplos

O exemplo INSERT... O exemplo de SELECT * FROM OPENROWSET(BULK...) importa dados em massa e mantém os valores padrão.

Para executar os exemplos, é necessário criar a tabela de exemplo MyTestDefaultCol2, o arquivo de dados MyTestEmptyField2-c.Dat e usar um arquivo de formato MyTestDefaultCol2-f-c.Fmt. Para obter informações sobre como criar esses exemplos, consulte "Tabela e arquivo de dados de exemplo", anteriormente neste tópico.

A segunda coluna de tabela, Col2, tem um valor padrão. O campo correspondente do arquivo de dados contém uma cadeia de caracteres vazia. Quando INSERT... SELECT * FROM OPENROWSET(BULK...) importa os campos desse arquivo de dados na tabela MyTestDefaultCol2, por padrão, NULL é inserido na Col2 no lugar do valor padrão. Esse comportamento padrão produz o seguinte resultado:

1	NULL	DataField3
2	NULL	DataField3

Para inserir o valor padrão "Default value of Col2" no lugar de "NULL" é necessário usar a dica de tabela KEEPDEFAULTS, como demonstrado no exemplo a seguir. De uma ferramenta de consulta, como o Editor de Consultas do SQL Server Management Studio, execute:

USE AdventureWorks;
GO
INSERT INTO MyTestDefaultCol2
    WITH (KEEPDEFAULTS)
    SELECT *
      FROM OPENROWSET(BULK  'C:\MyTestEmptyField2-c.Dat',
      FORMATFILE='C:\MyTestDefaultCol2-f-c.Fmt'     
      ) as t1 ;
GO