Artigo
04/01/2012

INSERT (Transact-SQL)

Adiciona uma ou mais linhas a uma tabela ou exibição no SQL Server 2008 R2. Para obter exemplos, consulte Exemplos de INSERT (Transact-SQL).

Ícone de vínculo de tópico Convenções de sintaxe Transact-SQL

Sintaxe

-- Standard INSERT syntax
[ WITH <common_table_expression> [ ,...n ] ]
INSERT 
{
        [ TOP ( expression ) [ PERCENT ] ] 
        [ INTO ] 
        { <object> | rowset_function_limited 
          [ WITH ( <Table_Hint_Limited> [ ...n ] ) ] }
    {
        [ ( column_list ) ] 
        [ <OUTPUT Clause> ]
        { VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n ] 
          | derived_table 
          | execute_statement
          | <dml_table_source>
          | DEFAULT VALUES 
        }
    }
}
[; ]

-- Syntax for external tool only
INSERT 
{
    [BULK]
    [ database_name . [ schema_name ] . | schema_name . ]
    [ table_name | view_name ]
    ( <column_definition> )
    [ WITH (
        [ [ , ] CHECK_CONSTRAINTS ]
        [ [ , ] FIRE_TRIGGERS ]
        [ [ , ] KEEP_NULLS ]
        [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ]
        [ [ , ] ROWS_PER_BATCH = rows_per_batch ]
        [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
        [ [ , ] TABLOCK ]
           ) ]
}
[; ]

<object> ::=
{ 
    [ server_name . database_name . schema_name . 
      | database_name .[ schema_name ] . 
      | schema_name . 
    ]
  table_or_view_name
}

<dml_table_source> ::=
    SELECT <select_list>
    FROM ( <dml_statement_with_output_clause> ) 
      [AS] table_alias [ ( column_alias [ ,...n ] ) ]
    [ WHERE <search_condition> ]
        [ OPTION ( <query_hint> [ ,...n ] ) ]

<column_definition> ::=
 column_name <data_type>
    [ COLLATE collation_name ]
    [ NULL | NOT NULL ]

<data type> ::= 
[ type_schema_name . ] type_name 
    [ ( precision [ , scale ] | max ]

Argumentos

WITH <common_table_expression>
Especifica o conjunto de resultados nomeado temporário, também conhecido como expressão de tabela comum, definido dentro do escopo da instrução INSERT. O conjunto de resultados é derivado de uma instrução SELECT.

Também podem ser usadas expressões de tabela comuns (CTEs) com as instruções SELECT, DELETE, UPDATE, MERGE e CREATE VIEW. Para obter mais informações, consulte WITH common_table_expression (Transact-SQL).
TOP (expression) [ PERCENT ]
Especifica o número ou porcentagem de linhas aleatórias que serão inseridas. expression pode ser um número ou uma porcentagem de linhas. As linhas referenciadas na expressão TOP que são usadas com INSERT, UPDATE ou DELETE não são organizadas em nenhuma ordem.

São necessários parênteses delimitando expression em TOP nas instruções INSERT, UPDATE e DELETE. Para obter mais informações, consulte TOP (Transact-SQL).
INTO
É uma palavra-chave opcional que pode ser usada entre INSERT e a tabela de destino.
server_name
É o nome do servidor vinculado no qual a tabela ou exibição está localizada. server_name pode ser especificado como um nome de servidor vinculado ou usando-se a função OPENDATASOURCE.

Quando server_name é especificado como um servidor vinculado, database_name e schema_name são necessários. Quando server_name é especificado com OPENDATASOURCE, database_name e schema_name podem não se aplicar a todas as fontes de dados e podem estar sujeitos aos recursos do provedor OLE DB que acessa o objeto remoto. Para obter mais informações, consulte Consultas distribuídas.
database_name
É o nome do banco de dados.
schema_name
É o nome do esquema ao qual a tabela ou exibição pertence.
table_or view_name
É o nome da tabela ou exibição que irá receber os dados.

Uma variável table, dentro de seu escopo, pode ser usada como uma origem de tabela em uma instrução INSERT.

A exibição referida por table_or_view_name deve ser atualizável e fazer referência exata a uma tabela base na cláusula FROM da exibição. Por exemplo, um INSERT em uma exibição de várias tabelas deve usar um column_list que faça referência apenas a colunas de uma tabela base. Para obter mais informações sobre exibições atualizáveis, consulte CREATE VIEW (Transact-SQL).
rowset_function_limited
É a função OPENQUERY ou OPENROWSET. O uso destas funções está sujeito aos recursos do provedor OLE DB que acessa o objeto remoto. Para obter mais informações, consulte Consultas distribuídas.

WITH ( <table_hint_limited> [... n ] )
Especifica uma ou mais dicas de tabela permitidas para uma tabela de destino. A palavra-chave WITH e os parênteses são necessários.

READPAST, NOLOCK e READUNCOMMITTED não são permitidos. Para obter mais informações sobre dicas de tabela, consulte Dicas de tabela (Transact-SQL).

Importante
A capacidade de especificar dicas HOLDLOCK, SERIALIZABLE, READCOMMITTED, REPEATABLEREAD ou UPDLOCK em tabelas que são destinos de instruções INSERT será removida em uma versão futura do SQL Server. Essas dicas não afetam o desempenho de instruções INSERT. Evite usá-las em novos projetos de desenvolvimento e planeje modificar os aplicativos que as utilizam atualmente.

A especificação da dica TABLOCK em uma tabela que é o destino de uma instrução INSERT tem o mesmo efeito que a especificação da dica TABLOCKX. Um bloqueio exclusivo é obtido na tabela.

(column_list)
É uma lista de uma ou mais colunas nas quais inserir dados. column_list deve ser incluída entre parênteses e estar delimitada por vírgulas.

Se uma coluna não estiver na column_list, o Mecanismo de Banco de Dados deve ser capaz de fornecer um valor baseado na definição da coluna. Caso contrário, a linha não poderá ser carregada. O Mecanismo de Banco de Dados fornece um valor automaticamente para a coluna se a coluna:
- Tiver uma propriedade IDENTITY. O próximo valor de identidade incremental for usado.
- Tiver um padrão. O valor padrão da coluna será usado.
- Tiver um tipo de dados timestamp. O valor do carimbo de data/hora atual será usado.
- Permitir valor nulo. Um valor nulo será usado.
- For uma coluna computada. O valor calculado será usado.
column_list e uma lista de valores devem ser usados quando valores explícitos são inseridos em uma coluna de identidades e a opção SET IDENTITY_INSERT deve estar definida como ON para a tabela.
Cláusula OUTPUT
Retorna linhas inseridas como parte da operação de inserção. Os resultados podem ser retornados ao aplicativo de processamento ou inseridos em uma tabela ou variável de tabela para processamento futuro.

A cláusula OUTPUT não tem suporte em instruções DML que fazem referência a exibições particionadas locais, exibições particionadas distribuídas ou tabelas remotas, ou instruções INSERT que contêm um execute_statement. A cláusula OUTPUT INTO não é suportada em instruções INSERT que contenham uma cláusula <dml_table_source>.
VALUES
Apresenta a(s) lista(s) de valores de dados a serem inseridos. Deve haver valores de dados para cada coluna em column_list, se especificada, ou na tabela. A lista de valores deve ser incluída entre parênteses.

Se os valores na lista não estiverem na mesma ordem que as colunas na tabela ou se não tiverem um valor para cada coluna da tabela, column_list deverá ser usado para especificar explicitamente a coluna que armazena cada valor de entrada.

É possível usar o construtor de linhas do Transact-SQL (também chamado construtor de valor de tabela) para especificar várias linhas em uma única instrução INSERT. O construtor de linhas consiste em uma única cláusula VALUES com várias listas de valores entre parênteses e separados por uma vírgula. Para obter mais informações, consulte Construtor de valor de tabela (Transact-SQL).
DEFAULT
Força o Mecanismo de Banco de Dados a carregar o valor padrão definido para uma coluna. Se não existir um padrão para a coluna e a coluna aceitar valores nulos, NULL será inserido. Para uma coluna definida com o tipo de dados timestamp, o próximo valor do carimbo de data/hora será inserido. DEFAULT não é válido para uma coluna de identidade.
expression
É uma constante, uma variável ou uma expressão. A expressão não pode conter uma instrução EXECUTE.

Ao referenciar tipos de dados de caractere Unicode nchar, nvarchar, and ntext, 'expression' deve ter a letra maiúscula 'N' como prefixo. Se 'N' não for especificado, o SQL Server irá converter a cadeia de caracteres na página de código correspondente ao agrupamento padrão do banco de dados ou coluna. Qualquer caractere não localizado nessa página de código será perdido. Para obter mais informações, consulte Programação do lado do servidor com Unicode.
derived_table
É qualquer instrução SELECT válida que retorne linhas de dados a serem carregadas na tabela. A instrução SELECT não pode conter uma CTE (expressão de tabela comum).
execute_statement
É qualquer instrução EXECUTE que retorne dados com instruções SELECT ou READTEXT.

Se execute_statement for usado com INSERT, cada conjunto de resultados deverá ser compatível com as colunas da tabela ou da column_list.

execute_statement pode ser usado para executar procedimentos armazenados no mesmo servidor ou em um servidor remoto. O procedimento no servidor remoto é executado e os conjuntos de resultados são retornados ao servidor local e carregados na tabela no servidor local. Em uma transação distribuída, execute_statement não pode ser emitido em um servidor vinculado de auto-retorno quando a conexão tem vários conjuntos de resultados ativos (MARS) habilitados.

Se execute_statement retornar dados com a instrução READTEXT, cada instrução READTEXT poderá retornar um máximo de 1 MB (1024 KB) de dados. execute_statement também pode ser usado com procedimentos estendidos. execute_statement insere os dados retornados pelo thread principal do procedimento estendido. No entanto, saídas de threads diferentes do thread principal não são inseridas.

Você não pode especificar um parâmetro avaliado por tabela como o destino de uma instrução INSERT EXEC; porém, ele pode ser especificado como uma origem na cadeia de caracteres INSERT EXEC ou procedimento armazenado. Para obter mais informações, consulte Parâmetros com valor de tabela (Mecanismo de Banco de Dados).
<dml_table_source>
Especifica que as linhas inseridas na tabela de destino são as retornadas pela cláusula OUTPUT de uma instrução INSERT, UPDATE, DELETE ou MERGE, opcionalmente filtradas por uma cláusula WHERE. Se <dml_table_source> estiver especificado, o destino da instrução INSERT externa deverá atender às seguintes restrições:
- Deve ser uma tabela base, não uma exibição.
- Não pode ser uma tabela remota.
- Não pode ter gatilhos definidos.
- Não pode participar de relações de chave primária/chave estrangeira.
- Ele não pode participar de replicação de mesclagem ou de assinaturas atualizáveis para replicação transacional.
O nível de compatibilidade do banco de dados deve ser definido como 100 ou superior. Para obter mais informações, consulte cláusula OUTPUT (Transact-SQL).

<select_list>
É uma lista separada por vírgulas que especifica quais colunas retornadas pela cláusula OUTPUT devem ser inseridas. As colunas de <select_list> devem ser compatíveis com as colunas nas quais os valores serão inseridos. <select_list> não pode fazer referência a funções de agregação ou a TEXTPTR.

Observação
Todas as variáveis listadas na lista SELECT fazem referência a seus valores originais, independentemente de qualquer alteração feita neles em <dml_statement_with_output_clause>.

<dml_statement_with_output_clause>
É uma instrução INSERT, UPDATE, DELETE ou MERGE válida que retorna linhas afetadas em uma cláusula OUTPUT. A instrução não pode conter uma cláusula WITH nem pode ter como destino tabelas remotas ou exibições particionadas. Se UPDATE ou DELETE for especificada, ela não poderá ser uma instrução UPDATE ou DELETE baseada em cursor. Linhas de origem não podem ser referenciadas como instruções DML aninhadas.
WHERE <search_condition>
É qualquer cláusula WHERE que contém uma <search_condition> válida que filtra as linhas retornadas por <dml_statement_with_output_clause>. Para obter mais informações, consulte Critério de pesquisa (Transact-SQL). Quando usada nesse contexto, <search_condition> não pode conter subconsultas, funções escalares definidas pelo usuário que executam acesso a dados, funções agregadas, TEXTPTR, nem predicados de pesquisa de texto completo.
DEFAULT VALUES
Força a nova linha a conter os valores padrão definidos para cada coluna.
BULK
Usado por ferramentas externas para carregar um fluxo de dados binários. Esta opção não é dirigida para uso com ferramentas como SQL Server Management Studio, SQLCMD, OSQL ou interfaces de programação de aplicativo de acesso a dados, como o SQL Server Native Client.
FIRE_TRIGGERS
Especifica que qualquer gatilho de inserção definido na tabela de destino seja executado durante a operação de carregamento de fluxo de dados binários. Para obter mais informações, consulte BULK INSERT (Transact-SQL).
CHECK_CONSTRAINTS
Especifica que todas as restrições na tabela ou exibição de destino devem ser verificadas durante a operação de carregamento de fluxo de dados binários. Para obter mais informações, consulte BULK INSERT (Transact-SQL).
KEEPNULLS
Especifica que as colunas vazias devem reter um valor nulo durante a operação de carregamento de fluxo de dados binários. Para obter mais informações, consulte Mantendo valores nulos ou usando valores padrão durante a importação em massa.
KILOBYTES_PER_BATCH = kilobytes_per_batch
Especifica o número aproximado de kilobytes (KB) de dados por lote como kilobytes_per_batch. Para obter mais informações, consulte BULK INSERT (Transact-SQL).
ROWS_PER_BATCH =rows_per_batch
Indica o número aproximado de linhas de dados no fluxo de dados binários. Para obter mais informações, consulte BULK INSERT (Transact-SQL).

Observação Um erro de sintaxe será gerado se uma lista de colunas não for fornecida.

Práticas recomendadas

Use a função @@ROWCOUNT para retornar o número de linhas inseridas ao cliente. Para obter mais informações, consulte @@ROWCOUNT (Transact-SQL).

Práticas recomendadas para importar dados em massa

Usando INSERT INTO…SELECT em importação de dados em massa com log mínimo

Você pode usar INSERT INTO <target_table> SELECT <columns> FROM <source_table> para transferir de modo eficaz um número grande de linhas de uma tabela, como uma tabela de preparo, para outra tabela com log mínimo. O log mínimo pode melhorar o desempenho da instrução e reduzir a possibilidade de a operação preencher o espaço de log disponível durante a transação.

O log mínimo dessa instrução possui os seguintes requisitos:

O modelo de recuperação do banco de dados é definido como simples ou bulk-logged.
A tabela de destino é um heap vazio ou não vazio.
A tabela de destino não é usada na replicação.
A dica TABLOCK é especificada para a tabela de destino.

As linhas inseridas em um heap como o resultado de uma ação de inserção em uma instrução MERGE também podem ser minimamente registradas.

Diferentemente da instrução BULK INSERT, que possui um bloqueio de atualização em massa menos restritivo, INSERT INTO.SELECT com a dica TABLOCK possui um bloqueio exclusivo (X) na tabela. Isso significa que você não pode inserir linhas usando operações de inserção paralelas. Para obter mais informações sobre bloqueios, consulte Modos de bloqueio.

Usando OPENROWSET e BULK para importação de dados em massa

A função OPENROWSET pode aceitar as seguintes dicas de tabela, que fornecem otimizações de carregamento em massa com a instrução INSERT:

A dica TABLOCK pode minimizar o número de registros de log para a operação de inserção. O modelo de recuperação do banco de dados deve ser definido para simples ou bulk-logged e a tabela de destino não pode ser usada na replicação. Para obter mais informações, consulte Pré-requisitos para log mínimo em importação em massa.
A dica IGNORE_CONSTRAINTS pode desabilitar temporariamente a verificação de restrição FOREIGN KEY e CHECK.
A dica IGNORE_TRIGGERS pode desabilitar temporariamente a execução de gatilhos.
A dica KEEPDEFAULTS permite a inserção de um valor padrão da coluna de tabela, se houver algum, em vez de NULL, se o registro de dados não tiver um valor para a coluna.
A dica KEEPIDENTITY permite que os valores de identidade no arquivo de dados importado sejam usados para a coluna de identidade na tabela de destino.

Essas otimizações são semelhantes àquelas disponíveis com o comando BULK INSERT. Para obter mais informações, consulte Dicas de tabela (Transact-SQL).

Tipos de Dados

Quando você inserir linhas, considere o comportamento do seguinte tipo de dados:

Se um valor estiver sendo carregado em colunas com um tipo de dados char, varchar ou varbinary, o preenchimento ou truncamento de espaços em branco à direita (espaços para char e varchar, zeros para varbinary) será determinado pela configuração de SET ANSI_PADDING definida para a coluna durante a criação da tabela. Para obter mais informações, consulte SET ANSI_PADDING (Transact-SQL).

A tabela a seguir mostra a operação padrão de SET ANSI_PADDING OFF.

Tipo de dados	Operação padrão
char	Valor de preenchimento com espaços para a largura definida da coluna.
varchar	Remove espaços à direita do último caractere não-espaço ou do caractere de espaço único para cadeias de caracteres compostas apenas de espaços.
varbinary	Remova zeros à direita.

Se uma cadeia de caracteres vazia (' ') for carregada em uma coluna com um tipo de dados varchar ou text, a operação padrão será carregar uma cadeia de caracteres de comprimento zero.
A inserção de um valor nulo em uma coluna text ou image não cria um ponteiro de texto válido, nem pré-aloca uma página de texto de 8 KB. Para obter mais informações sobre como inserir dados text e image, consulte Usando as funções text, ntext e image.
Colunas criadas com o tipo de dados uniqueidentifier armazenam valores binários de 16 bytes especialmente formatados. Diferentemente do que ocorre com as colunas de identidade, o Mecanismo de Banco de Dados não gera valores para colunas com o tipo de dados uniqueidentifier. Durante uma operação de inserção, as variáveis com um tipo de dados uniqueidentifier e constantes de cadeias de caracteres no formato xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx (36 caracteres incluindo hífens, onde x é um dígito hexadecimal no intervalo de 0 a 9 ou de a a f) podem ser usadas para colunas uniqueidentifier. Por exemplo, 6F9619FF-8B86-D011-B42D-00C04FC964FF é um valor válido para uma variável ou coluna uniqueidentifier. Use a função NEWID() para obter uma ID globalmente exclusiva (GUID).

Inserindo valores em colunas de tipo definido pelo usuário

É possível inserir valores em colunas de tipo definido pelo usuário das seguintes maneiras:

Fornecendo um valor do tipo definido pelo usuário.
Fornecendo um valor em um tipo de dados do sistema SQL Server, contanto que o tipo definido pelo usuário ofereça suporte à conversão implícita ou explícita do referido tipo. O exemplo a seguir mostra como inserir um valor em uma coluna de tipo definido pelo usuário Point convertendo explicitamente de uma cadeia de caracteres:
```
INSERT INTO Cities (Location)
VALUES ( CONVERT(Point, '12.3:46.2') );
```
Um valor binário também pode ser fornecido sem executar conversão explícita, porque todos os tipos definidos pelo usuário podem ser implicitamente convertidos de binário. Para obter mais informações sobre conversão e tipos definidos pelo usuário, consulte Executando operações em tipos definidos pelo usuário.
Chamando uma função definida pelo usuário que retorna um valor do tipo definido pelo usuário. O exemplo a seguir usa uma função definida pelo usuário CreateNewPoint() para criar um novo valor de tipo definido pelo usuário Point e inserir o valor na tabela Cities.
```
INSERT INTO Cities (Location)
VALUES ( dbo.CreateNewPoint(x, y) );
```

Tratamento de erros

Você pode implementar a manipulação de erros para a instrução INSERT especificando essa instrução em uma construção TRY.CATCH. Para obter mais informações, consulte Usando TRY...CATCH na Transact-SQL.

Se uma instrução INSERT violar uma restrição ou regra ou se ela tiver um valor incompatível com o tipo de dados da coluna, a instrução falhará e uma mensagem de erro será retornada.

Se INSERT estiver carregando várias linhas com SELECT ou EXECUTE, qualquer violação de uma regra ou restrição que ocorra dos valores que estão sendo carregados faz com que a instrução seja interrompida e nenhuma linha seja carregada.

Quando uma instrução INSERT encontra um erro aritmético (estouro, divisão por zero ou um erro de domínio) que ocorre durante a avaliação de expressão, o Mecanismo de Banco de Dados trata esses erros como se a opção SET ARITHABORT estivesse definida como ON. O lote é interrompido e uma mensagem de erro é retornada. Durante a avaliação da expressão quando SET ARITHABORT e SET ANSI_WARNINGS estão definidas como OFF, se uma instrução INSERT, DELETE ou UPDATE encontrar um erro aritmético, de estouro, de divisão por zero ou um erro de domínio, o SQL Server irá inserir ou atualizar um valor NULL. Se a coluna de destino não for anulável, a ação de inserção ou atualização falhará e o usuário receberá uma mensagem de erro. Para obter mais informações, consulte Comportamento quando ARITHABORT e ARITHIGNORE estão definidos como ON.

Interoperabilidade

Quando um gatilho INSTEAD OF é definido em ações INSERT em uma tabela ou exibição, o gatilho é executado em vez da instrução INSERT. Para obter mais informações sobre gatilhos INSTEAD OF, consulte CREATE TRIGGER (Transact-SQL).

Limitações e restrições

Quando você insere valores em tabelas remotas e nem todos os valores de todas as colunas são especificados, é necessário identificar as colunas para as quais os valores devem ser inseridos.

A configuração da opção SET ROWCOUNT é ignorada para instruções INSERT em exibições locais e remotas particionadas. Além disso, essa opção não tem suporte para instruções INSERT emitidas em tabelas remotas.

Importante
O uso de SET ROWCOUNT não afetará as instruções DELETE, INSERT e UPDATE em uma futura versão do SQL Server. Não use SET ROWCOUNT com instruções DELETE, INSERT e UPDATE em um novo trabalho de desenvolvimento e planeje modificar os aplicativos que a utilizam atualmente. Em vez disso, recomendamos o uso da cláusula TOP.

Comportamento de bloqueio

Uma instrução INSERT sempre adquire um bloqueio exclusivo (X) na tabela que modifica e mantém esse bloqueio até que a transação seja concluída. Com um bloqueio exclusivo (X), nenhuma outra transação pode modificar os dados; operações de leitura podem ser realizadas apenas com o uso da dica NOLOCK ou nível de isolamento de leitura não confirmada. Para obter mais informações, consulte Bloqueios no mecanismo de banco de dados.

Comportamento de registro em log

A instrução INSERT sempre é totalmente registrada em log, exceto quando a função OPENROWSET é usada com a palavra-chave BULK ou ao usar INSERT INTO <target_table> SELECT <columns> FROM <source_table>. Essas operações podem ser registradas minimamente. Para obter mais informações, consulte a seção "Práticas recomendadas para o carregamento de dados em massa" anteriormente neste tópico.

Segurança

Durante uma conexão de servidor vinculado, o servidor de envio fornece um nome de logon e uma senha para conexão com o servidor de recebimento em seu nome. Para que essa conexão funcione, você deve criar um mapeamento de logon entre os servidores vinculados usando sp_addlinkedsrvlogin. Para obter mais informações, consulte Segurança para servidores vinculados.

Quando você usar OPENROWSET(BULK…), é importante entender como o SQL Server manipula a representação. Para obter mais informações, consulte "Considerações de segurança" em Importando dados em massa usando BULK INSERT ou OPENROWSET(BULK...).

Permissões

A permissão INSERT é necessária na tabela de destino.

As permissões INSERT seguem o padrão para membros da função de servidor fixa sysadmin, de db_owner e das funções de banco de dados fixas db_datawriter, e do proprietário da tabela. Os membros das funções sysadmin, db_owner e db_securityadmin, e o proprietário da tabela podem transferir permissões para outros usuários.

Para executar INSERT com a opção BULK da função OPENROWSET, você precisa ser membro da função de servidor fixa sysadmin ou bulkadmin.