Artigo
09/28/2010

Executando operações de cópia em massa

O recurso de cópia em massa do SQL Server oferece suporte à transferência de grandes quantidades de dados para dentro ou fora de uma tabela ou exibição do SQL Server. Os dados também podem ser transferidos com a especificação de uma instrução SELECT. É possível mover os dados entre o SQL Server e um arquivo de dados do sistema operacional, como um arquivo ASCII. O arquivo de dados pode ter diferentes formatos; o formato é definido para que a cópia em massa seja feita em um arquivo de formato. Como alternativa, os dados podem ser carregados em variáveis de programa e transferidos para o SQL Server usando funções e métodos de cópia em massa.

O CodePlex possui um aplicativo de exemplo que demonstra esse recurso; consulte Exemplos do Mecanismo de Banco de Dados do SQL Server para obter mais informações.

Normalmente, um aplicativo usa o recurso de cópia em massa de uma destas maneiras:

Faz cópia em massa a partir de uma tabela, exibição ou conjunto de resultados de uma instrução Transact-SQL para um arquivo de dados onde os dados são armazenados no mesmo formato que a tabela ou exibição.

Esse arquivo é chamado de arquivo de dados de modo nativo.
Faz cópia em massa a partir de uma tabela, exibição ou conjunto de resultados de uma instrução Transact-SQL para um arquivo de dados onde os dados são armazenados em um formato diferente do formato da tabela ou exibição.

Nesse caso, um arquivo de formato separado é criado para definir as características (tipo de dados, posição, comprimento, terminador, e assim por diante) de cada coluna à medida que ela é armazenada no arquivo de dados. Se todas as colunas forem convertidas em um formato de caractere, o arquivo resultante será chamado de arquivo de dados do modo de caractere.
Faz cópia em massa a partir de um arquivo de dados para uma tabela ou exibição.

Se necessário, um arquivo de formato é usado para determinar o layout do arquivo de dados.
Faz o carregamento de dados para variáveis de programa e importa os dados para uma tabela ou exibição usando as funções de cópia em massa para executar a cópia em massa em uma linha de cada vez.

Os arquivos de dados usados pelas funções de cópia em massa não precisam ser criados por outro programa de cópia em massa. Qualquer outro sistema pode gerar um arquivo de dados e um arquivo de formato para executar a cópia em massa de definições; esses arquivos podem ser usados com um programa de cópia em massa do SQL Server para importar dados para o SQL Server. Por exemplo, você poderia exportar dados de uma planilha em um arquivo delimitado por tabulação, criar um arquivo de formato descrevendo o arquivo delimitado por tabulação e usar um programa de cópia em massa para importar rapidamente os dados para o SQL Server. Os arquivos de dados gerados pela cópia em massa também podem ser importados para outros aplicativos. Por exemplo, você poderia usar as funções de cópia em massa para exportar dados de uma tabela ou exibição para um arquivo delimitado por tabulação que poderia, por sua vez, ser carregado para a planilha.

Observação
A partir do SQL Server 2005, quando você usa o utilitário bcp os relatórios do servidor registram um erro quando ocorre um truncamento de dados numéricos. O SQL Server 2000 e as versões anteriores só retornavam um aviso. Isso pode causar problemas para os aplicativos existentes que ignoram o aviso. Para evitar esses problemas, é preciso garantir que os dados de entrada tenham valores corretos que não serão truncados ou continuar usando a versão SQL Server 2000 do bcp.

A partir do SQL Server 2005, quando você usa o utilitário bcp os relatórios do servidor registram um erro quando ocorre um truncamento de dados numéricos. O SQL Server 2000 e as versões anteriores só retornavam um aviso. Isso pode causar problemas para os aplicativos existentes que ignoram o aviso. Para evitar esses problemas, é preciso garantir que os dados de entrada tenham valores corretos que não serão truncados ou continuar usando a versão SQL Server 2000 do bcp.

Os aplicativos de codificação para programadores que usam funções de cópia em massa deveriam seguir as regras gerais para garantir o bom desempenho dessas funções. Para obter mais informações sobre o suporte a operações de cópia em massa no SQL Server, consulte Sobre operações de importação e exportação em massa.

Limitações e restrições

Um UDT (tipo definido pelo usuário) CLR deve ser associado como dados binários. Mesmo se um arquivo de formato especificar SQLCHAR como o tipo de dados para uma coluna UDT de destino, o utilitário BCP interpretará os dados como binários.

Não use SET FMTONLY OFF com operações de cópia em massa. SET FMTONLY OFF pode fazer sua operação de cópia em massa falhar ou gerar resultados inesperados.

Provedor OLE DB do SQL Server Native Client

O provedor OLE DB do SQL Server Native Client implementa dois métodos para executar operações de cópia em massa com um banco de dados SQL Server. O primeiro método envolve o uso da interface IRowsetFastLoad para operações de cópia em massa baseadas em memória; o segundo envolve o uso da interface IBCPSession para operações de cópia em massa baseadas em arquivo.

Usando operações de cópia em massa baseadas em memória

O provedor OLE DB do SQL Server Native Client implementa a interface IRowsetFastLoad para expor suporte a operações de cópia em massa baseadas em memória do SQL Server. A interface IRowsetFastLoad implementa os métodos IRowsetFastLoad::Commit e IRowsetFastLoad::InsertRow.

Habilitando uma sessão para IRowsetFastLoad

O consumidor notifica o provedor OLE DB do SQL Server Native Client sobre sua necessidade de executar uma cópia em massa, definindo a propriedade de fonte de dados específica do provedor OLE DB do SQL Server Native Client SSPROP_ENABLEFASTLOAD como VARIANT_TRUE. Com o conjunto de propriedades na fonte de dados, o consumidor cria uma sessão de provedor OLE DB do SQL Server. A nova sessão permite que o consumidor acesse a interface IRowsetFastLoad.

Observação
Se a interface IDataInitialize for usada para inicializar a fonte de dados, será necessário definir a propriedade SSPROP_IRowsetFastLoad no parâmetro rgPropertySets do método IOpenRowset::OpenRowset; caso contrário, a chamada para o método OpenRowset retornará E_NOINTERFACE.

Habilitar uma sessão para a cópia em massa restringe o provedor OLE DB do SQL Server Native Client para interfaces na sessão. Uma sessão habilitada para cópia em massa expõe apenas as seguintes interfaces:

IDBSchemaRowset
IGetDataSource
IOpenRowset
ISupportErrorInfo
ITransactionJoin

Para desabilitar a criação de conjuntos de linhas habilitados para cópia em massa e fazer com que a sessão do provedor OLE DB do SQL Server Native Client seja revertida para o processamento padrão, redefina SSPROP_ENABLEFASTLOAD como VARIANT_FALSE.

Conjuntos de linhas IRowsetFastLoad

Os conjuntos de linhas de cópia em massa do provedor OLE DB do SQL Server Native Client são somente gravação, mas eles expõe interfaces que permitem que o consumidor determine a estrutura de uma tabela SQL Server. As seguintes interfaces são expostas em um conjunto de linhas do provedor OLE DB do SQL Server Native Client habilitado para cópia em massa:

IAccessor
IColumnsInfo
IColumnsRowset
IConvertType
IRowsetFastLoad
IRowsetInfo
ISupportErrorInfo

As propriedades específicas de provedor SSPROP_FASTLOADOPTIONS, SSPROP_FASTLOADKEEPNULLS e SSPROP_FASTLOADKEEPIDENTITY controlam o comportamento de um conjunto de linhas de cópia em massa do provedor OLE DB do SQL Server Native Client. As propriedades são especificadas no membro rgProperties de um membro de parâmetro rgPropertySetsdo IOpenRowset.

ID da propriedade	Descrição
SSPROP_FASTLOADKEEPIDENTITY	Coluna: Não Leitura/gravação: leitura/gravação Tipo: VT_BOOL Padrão: VARIANT_FALSE Descrição: Mantém valores de identidade fornecidos pelo consumidor. VARIANT_FALSE: Valores para uma coluna de identidade na tabela SQL Server são gerados pelo SQL Server. Qualquer valor associado à coluna é ignorado pelo provedor OLE DB do SQL Server Native Client. VARIANT_TRUE: O consumidor associa um acessador fornecendo um valor para uma coluna de identidade SQL Server. A propriedade de identidade não está disponível nas colunas que aceitam valores NULL, assim o consumidor fornece um valor exclusivo em cada chamada para IRowsetFastLoad::Insert.
SSPROP_FASTLOADKEEPNULLS	Coluna: Não Leitura/gravação: leitura/gravação Tipo: VT_BOOL Padrão: VARIANT_FALSE Descrição: Mantém valores NULL para colunas com uma restrição DEFAULT. Só afeta colunas SQL Server que aceitam valores NULL e que têm uma restrição DEFAULT aplicada. VARIANT_FALSE: o SQL Server insere o valor padrão para a coluna quando o consumidor do provedor OLE DB do SQL Server Native Client insere uma linha que contém valores NULL para a coluna. VARIANT_TRUE: o SQL Server insere NULL para o valor da coluna quando o consumidor do provedor OLE DB do SQL Server Native Client insere uma linha que contém valores NULL para a coluna.
SSPROP_FASTLOADOPTIONS	Coluna: Não Leitura/gravação: leitura/gravação Tipo: VT_BSTR Padrão: nenhum Descrição: Esta propriedade é a mesma que a opção -h "hint[,...n]" do utilitário bcp. A cadeia de caracteres a seguir pode ser usada como opção na cópia em massa de dados para uma tabela. ORDER(column[ASC \| DESC][,...n]): Ordem de classificação dos dados no arquivo de dados. O desempenho da operação de cópia em massa é aprimorado se o arquivo de dados que está sendo carregado for classificado de acordo com o índice clusterizado na tabela. ROWS_PER_BATCH = bb: O número de linhas de dados por lote (como bb). O servidor otimiza o carregamento em massa de acordo com o valor de bb. Por padrão, ROWS_PER_BATCH é desconhecido. KILOBYTES_PER_BATCH = cc: O número de quilobytes (KB) de dados por lote (como cc). Por padrão, KILOBYTES_PER_BATCH é desconhecido. TABLOCK: Um bloqueio em nível de tabela é obtido enquanto durar a operação de cópia em massa. Essa opção melhora significativamente o desempenho porque manter um bloqueio apenas durante a operação de cópia em massa reduz a contenção de bloqueios na tabela. Uma tabela pode ser carregada simultaneamente por vários clientes se não tiver índices e se TABLOCK for especificado. Por padrão, o comportamento de bloqueio é determinado pela opção de tabela table lock on bulk load. CHECK_CONSTRAINTS: Todas as restrições em table_name são verificadas durante a operação de cópia em massa. Por padrão, as restrições são ignoradas. FIRE_TRIGGER: No SQL Server 2000, com gatilhos habilitados, o registro otimizado não era possível porque a lógica de gatilho era baseada em registros de log. Durante uma operação de importação em massa com gatilhos habilitados, todas as otimizações de registro em massa (inclusive bloqueios de BU) eram desabilitadas. A partir do SQL Server 2005, no entanto, o SQL Server usa controle de versão de linha para gatilhos e armazena as versões de linha no armazenamento de versão do tempdb. Portanto, as otimizações de registro em massa estão disponíveis até mesmo quando os gatilhos estão habilitados. Antes de iniciar a importação em massa de um lote com um número grande de linhas com gatilhos habilitados, você pode precisar expandir o tamanho do tempdb.

SSPROP_FASTLOADKEEPIDENTITY

Coluna: Não

Leitura/gravação: leitura/gravação

Tipo: VT_BOOL

Padrão: VARIANT_FALSE

Descrição: Mantém valores de identidade fornecidos pelo consumidor.

VARIANT_FALSE: Valores para uma coluna de identidade na tabela SQL Server são gerados pelo SQL Server. Qualquer valor associado à coluna é ignorado pelo provedor OLE DB do SQL Server Native Client.

VARIANT_TRUE: O consumidor associa um acessador fornecendo um valor para uma coluna de identidade SQL Server. A propriedade de identidade não está disponível nas colunas que aceitam valores NULL, assim o consumidor fornece um valor exclusivo em cada chamada para IRowsetFastLoad::Insert.

SSPROP_FASTLOADKEEPNULLS

Coluna: Não

Leitura/gravação: leitura/gravação

Tipo: VT_BOOL

Padrão: VARIANT_FALSE

Descrição: Mantém valores NULL para colunas com uma restrição DEFAULT. Só afeta colunas SQL Server que aceitam valores NULL e que têm uma restrição DEFAULT aplicada.

VARIANT_FALSE: o SQL Server insere o valor padrão para a coluna quando o consumidor do provedor OLE DB do SQL Server Native Client insere uma linha que contém valores NULL para a coluna.

VARIANT_TRUE: o SQL Server insere NULL para o valor da coluna quando o consumidor do provedor OLE DB do SQL Server Native Client insere uma linha que contém valores NULL para a coluna.

SSPROP_FASTLOADOPTIONS

Coluna: Não

Leitura/gravação: leitura/gravação

Tipo: VT_BSTR

Padrão: nenhum

Descrição: Esta propriedade é a mesma que a opção -h "hint[,...n]" do utilitário bcp. A cadeia de caracteres a seguir pode ser usada como opção na cópia em massa de dados para uma tabela.

ORDER(column[ASC | DESC][,...n]): Ordem de classificação dos dados no arquivo de dados. O desempenho da operação de cópia em massa é aprimorado se o arquivo de dados que está sendo carregado for classificado de acordo com o índice clusterizado na tabela.

ROWS_PER_BATCH = bb: O número de linhas de dados por lote (como bb). O servidor otimiza o carregamento em massa de acordo com o valor de bb. Por padrão, ROWS_PER_BATCH é desconhecido.

KILOBYTES_PER_BATCH = cc: O número de quilobytes (KB) de dados por lote (como cc). Por padrão, KILOBYTES_PER_BATCH é desconhecido.

TABLOCK: Um bloqueio em nível de tabela é obtido enquanto durar a operação de cópia em massa. Essa opção melhora significativamente o desempenho porque manter um bloqueio apenas durante a operação de cópia em massa reduz a contenção de bloqueios na tabela. Uma tabela pode ser carregada simultaneamente por vários clientes se não tiver índices e se TABLOCK for especificado. Por padrão, o comportamento de bloqueio é determinado pela opção de tabela table lock on bulk load.

CHECK_CONSTRAINTS: Todas as restrições em table_name são verificadas durante a operação de cópia em massa. Por padrão, as restrições são ignoradas.

FIRE_TRIGGER: No SQL Server 2000, com gatilhos habilitados, o registro otimizado não era possível porque a lógica de gatilho era baseada em registros de log. Durante uma operação de importação em massa com gatilhos habilitados, todas as otimizações de registro em massa (inclusive bloqueios de BU) eram desabilitadas.

A partir do SQL Server 2005, no entanto, o SQL Server usa controle de versão de linha para gatilhos e armazena as versões de linha no armazenamento de versão do tempdb. Portanto, as otimizações de registro em massa estão disponíveis até mesmo quando os gatilhos estão habilitados. Antes de iniciar a importação em massa de um lote com um número grande de linhas com gatilhos habilitados, você pode precisar expandir o tamanho do tempdb.

Usando operações de cópia em massa baseadas em arquivo

O provedor OLE DB do SQL Server Native Client implementa a interface IBCPSession para expor suporte para operações de cópia em massa baseadas em arquivo do SQL Server. A interface IBCPSession implementa os métodos IBCPSession::BCPColFmt, IBCPSession::BCPColumns, IBCPSession::BCPControl, IBCPSession::BCPDone, IBCPSession::BCPExec, IBCPSession::BCPInit, IBCPSession::BCPReadFmte IBCPSession::BCPWriteFmt.

Driver ODBC do SQL Server Native Client

O driver ODBC do SQL Server Native Client mantém o mesmo suporte para operações de cópia em massa que o das versões anteriores do driver ODBC do SQL Server. Para obter informações sobre operações de cópia em massa que usam o driver ODBC do SQL Server Native Client, consulte Executando operações de cópia em massa (ODBC).