INSERT (Transact-SQL)
Aplica-se a:
Banco de Dados SQL do SQL do SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics Warehouse do PDW (Analytics Platform System) no Microsoft Fabric Banco de dados SQL no Microsoft Fabric
Adiciona uma ou mais linhas a uma tabela ou exibição no SQL Server. Para obter exemplos, confira Exemplos.
Convenções de sintaxe de Transact-SQL
Sintaxe
Sintaxe para SQL Server e Banco de Dados SQL do Azure e Banco de Dados SQL de Malha
-- Syntax for SQL Server and Azure SQL Database and Fabric SQL database
[ 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
}
}
}
[;]
<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 ] ) ]
-- External tool only syntax
INSERT
{
[BULK]
{ database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_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 ]
) ]
}
[; ] <column_definition> ::=
column_name <data_type>
[ COLLATE collation_name ]
[ NULL | NOT NULL ]
<data type> ::=
[ type_schema_name . ] type_name
[ ( precision [ , scale ] | max ]
Sintaxe para o Azure Synapse Analytics e o Parallel Data Warehouse e o Microsoft Fabric Warehouse
-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse and Microsoft Fabric
INSERT [INTO] { database_name.schema_name.table_name | schema_name.table_name | table_name }
[ ( column_name [ ,...n ] ) ]
{
VALUES ( { NULL | expression } )
| SELECT <select_criteria>
}
[ OPTION ( <query_option> [ ,...n ] ) ]
[;]
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. Para obter mais informações, confira WITH common_table_expression (Transact-SQL).
TOP (expression) [ PERCENT ]
Especifica o número ou a porcentagem de linhas aleatórias que serão inseridas. expression pode ser um número ou uma porcentagem das linhas. Para saber mais, confira TOP (Transact-SQL).
INTO
É uma palavra-chave opcional que pode ser usada entre INSERT e a tabela de destino.
server_name
Aplica-se a: SQL Server 2008 (10.0.x) e posterior.
É o nome do servidor vinculado no qual a tabela ou exibição especificada está localizada. server_name pode ser especificado como o nome do servidor vinculado ou com a função OPENDATASOURCE.
Quando server_name é especificado como um servidor vinculado, database_name e schema_name são obrigató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 às funcionalidades do Provedor OLE DB que acessa o objeto remoto.
database_name
Aplica-se a: SQL Server 2008 (10.0.x) e posterior.
É 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 referenciada por table_or_view_name precisa ser atualizável e referenciar exatamente uma tabela base na cláusula FROM da exibição. Por exemplo, um INSERT em uma exibição de várias tabelas deve usar uma column_list que referencia apenas colunas de uma tabela base. Para obter mais informações sobre exibições atualizáveis, confiraCREATE VIEW (Transact-SQL).
rowset_function_limited
Aplica-se a: SQL Server 2008 (10.0.x) e posterior.
É a função OPENQUERY ou OPENROWSET. O uso dessas funções está sujeito aos recursos do provedor OLE DB que acessa o objeto remoto.
WITH ( <table_hint_limited> [... n ] )
Especifica uma ou mais dicas de tabela permitidas para uma tabela de destino. A palavra-chave WITH e parênteses são necessários.
READPAST, NOLOCK e READUNCOMMITTED não são permitidos. Para obter mais informações sobre dicas de tabela, confiraDicas de tabela (Transact-SQL).
Importante
A capacidade de especificar as 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.
Especificar a dica TABLOCK em uma tabela que é o destino de uma instrução INSERT tem o mesmo efeito de especificar a dica TABLOCKX. Um bloqueio exclusivo é obtido na tabela.
(column_list)
É uma lista de uma ou mais colunas onde os dados devem ser inseridos. column_list deve ser colocada entre parênteses e separada por vírgulas.
Se uma coluna não estiver na column_list, o Mecanismo de Banco de Dados deverá poder 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 fornecerá 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 for usado.
Tem um tipo de dados timestamp. O valor do carimbo de data/hora atual for usado.
Permite valor nulo. Um valor nulo for usado.
For uma coluna computada. O valor calculado for usado.
column_list deve ser usada quando valores explícitos são inseridos em uma coluna de identidade e a opção SET IDENTITY_INSERT deve ser 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 referenciam exibições particionadas locais, exibições particionadas distribuídas, tabelas remotas nem instruções INSERT que contêm execute_statement. A cláusula OUTPUT INTO não é compatível com instruções INSERT que contêm uma cláusula <dml_table_source>. Para saber mais sobre os argumentos e o comportamento dessa cláusula, confira Cláusula OUTPUT (Transact-SQL).
VALUES
Apresenta a(s) lista(s) de valores de dados a serem inseridos. Deve haver um valor de dados para cada coluna em column_list, se especificado, ou na tabela. A lista de valores deve ser colocada entre parênteses.
Se os valores na lista Value 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, confira Construtor de valor de tabela (Transact-SQL).
Observação
O construtor de valor de tabela não tem suporte no Azure Synapse Analytics. Alternativamente, instruções subsequentes do INSERT podem ser executadas para inserir várias linhas. No Azure Synapse Analytics, os valores de inserção só podem ser valores literais constantes ou referências variáveis. Para inserir um valor não literal, defina uma variável como um valor não constante e insira a variável.
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 é inserido. DEFAULT não é válido para uma coluna de identidade.
expressão
É uma constante, uma variável ou uma expressão. A expressão não pode conter uma instrução EXECUTE.
Ao referenciar os tipos de dados de caractere Unicode nchar, nvarchar e ntext, 'expression' deve ter a letra maiúscula 'N' como prefixo. Se N não for especificado, o SQL Server converte a cadeia na página de código correspondente à ordenação padrão do banco de dados ou coluna. Qualquer caractere não localizado nessa página de código será perdido.
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. Para obter mais informações, confira EXECUTE (Transact-SQL).
As opções de RESULT SETS da instrução EXECUTE não podem ser especificadas em uma instrução INSERT...EXEC.
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 loopback quando a conexão tem vários MARS (conjuntos de resultados ativos múltiplos) habilitados.
Se execute_statement retornar dados com a instrução READTEXT, cada instrução READTEXT poderá retornar, no máximo, 1 MB (1.024 KB) de dados. execute_statement também pode ser usada com procedimentos estendidos. execute_statement insere os dados retornados pelo thread principal do procedimento estendido; porém, a saída de threads diferente do thread principal não é inserida.
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, confira Usar 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> for 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.
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, confira 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 estão sendo inseridos. <select_list> não pode referenciar funções de agregação nem TEXTPTR.
Observação
Todas as variáveis listadas na lista SELECT referem-se a seus valores originais, independentemente das alterações feita nelas 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 um <search_condition> válido que filtra as linhas retornadas por <dml_statement_with_output_clause>. Para obter mais informações, confira Condição de Pesquisa (Transact-SQL). Quando usado nesse contexto, <search_condition> não pode conter subconsultas, funções escalares definidas pelo usuário que executam o acesso a dados, funções de agregação, TEXTPTR nem predicados de pesquisa de texto completo.
DEFAULT VALUES
Aplica-se a: SQL Server 2008 (10.0.x) e posterior.
Força a nova linha a conter os valores padrão definidos para cada coluna.
BULK
Aplica-se a: SQL Server 2008 (10.0.x) e posterior.
Usado por ferramentas externas para carregar um fluxo de dados binários. Esta opção não se destina ao 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
Aplica-se a: SQL Server 2008 (10.0.x) e posterior.
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, confira BULK INSERT (Transact-SQL).
CHECK_CONSTRAINTS
Aplica-se a: SQL Server 2008 (10.0.x) e posterior.
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, confira BULK INSERT (Transact-SQL).
KEEPNULLS
Aplica-se a: SQL Server 2008 (10.0.x) e posterior.
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, confira Manter Valores Nulos ou Usar os Valores Padrão Durante a Importação em Massa (SQL Server).
KILOBYTES_PER_BATCH = kilobytes_per_batch
Especifica o número aproximado de KB (kilobytes) de dados por lote como kilobytes_per_batch. Para obter mais informações, confira BULK INSERT (Transact-SQL).
ROWS_PER_BATCH =rows_per_batch
Aplica-se a: SQL Server 2008 (10.0.x) e posterior.
Indica o número aproximado de linhas de dados no fluxo de dados binários. Para obter mais informações, confira BULK INSERT (Transact-SQL).
Observação
Um erro de sintaxe é gerado se uma lista de colunas não é fornecida.
Comentários
Para obter informações específicas à inserção de dados em tabelas de grafo do SQL, consulte INSERT (SQL Graph).
Práticas Recomendadas
Use a função @@ROWCOUNT para retornar o número de linhas inseridas ao aplicativo cliente. Para saber mais, confira @@ROWCOUNT (Transact-SQL).
Práticas recomendadas para importar dados em massa
Usar INSERT INTO...SELECT na importação de dados em massa com paralelismo e log mínimo
Use INSERT INTO <target_table> SELECT <columns> FROM <source_table> para transferir com eficiência um grande número 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 contém um bloqueio de BU (atualização em massa) menos restritivo, INSERT INTO … SELECT com a dica TABLOCK contém um bloqueio exclusivo (X) na tabela. Isso significa que você não pode inserir linhas usando várias operações de inserção em execução simultaneamente.
No entanto, a partir do SQL Server 2016 (13.x) e do nível de compatibilidade do banco de dados 130, uma única instrução INSERT INTO … SELECT pode ser executada em paralelo ao inserir em heaps ou em CCI (índices columnstore clusterizados). Ao usar a dica de TABLOCK, é possível empregar inserções paralelas.
O paralelismo para a instrução acima tem os seguintes requisitos, que são semelhantes aos requisitos de registros de log mínimo:
- A tabela de destino é um heap vazio ou não vazio.
- A tabela de destino tem um CCI (índice columnstore clusterizado), mas não tem índices não clusterizados.
- A tabela de destino não tem uma coluna de identidade com IDENTITY_INSERT definida como OFF.
- A dica
TABLOCKé especificada para a tabela de destino.
Para cenários em que os requisitos de log mínimo e inserção paralela são atendidos, ambos os aprimoramentos funcionarão juntos para garantir a taxa de transferência máxima de suas operações de carregamento de dados.
Para obter mais informações sobre o uso do INSERT no seu Warehouse no Microsoft Fabric, consulte Ingerir dados no seu Warehouse usando Transact-SQL.
Observação
Inserções em tabelas temporárias locais (identificadas pelo prefixo #) e em tabelas temporárias globais (identificadas por prefixos ##) também estão habilitadas para paralelismo usando a dica TABLOCK.
Usar OPENROWSET e BULK na Importação em massa de dados
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
TABLOCKpode 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 como simples ou bulk-logged e a tabela de destino não pode ser usada na replicação. Para obter mais informações, confira Pré-requisitos para registro em log mínimo em importação em massa. - A dica
TABLOCKpode habilitar as operações de inserção paralelas. A tabela de destino é um heap ou um CCI (índice columnstore clusterizado) sem índices não clusterizados, e a tabela de destino não pode ter uma coluna de identidade especificada. - A dica
IGNORE_CONSTRAINTSpode desabilitar temporariamente a verificação de restrição CHECK e FOREIGN KEY. - A dica
IGNORE_TRIGGERSpode desabilitar temporariamente a execução de gatilhos. - A dica
KEEPDEFAULTSpermite a inserção de um valor padrão da coluna de tabela, caso haja algum, em vez de NULL, quando o registro de dados não tem um valor para a coluna. - A dica
KEEPIDENTITYpermite 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, confira 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, veja 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 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.
Colunas criadas com o tipo de dados uniqueidentifier armazenam valores binários de 16 bytes especialmente formatados. Ao contrário do que ocorre com as colunas de identidade, o Mecanismo de Banco de Dados não gera automaticamente 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 cadeia de caracteres no formato xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx (36 caracteres, incluindo hifens, em que x é um dígito hexadecimal no intervalo 0-9 ou 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 um GUID (ID global exclusiva).
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
Pointcom a conversão explícita 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.
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árioPointe inserir o valor na tabelaCities.INSERT INTO Cities (Location) VALUES ( dbo.CreateNewPoint(x, y) );
Tratamento de erros
Você pode implementar o tratamento de erro para a instrução INSERT especificando essa instrução em um constructo TRY...CATCH.
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 nos 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 da 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.
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, confira 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.
Quando TOP é usado com INSERT, as linhas referenciadas não são organizadas em nenhuma ordem e a cláusula ORDER BY não pode ser especificada diretamente nessas instruções. Se você precisar usar TOP para inserir linhas em uma ordem cronológica significativa, deverá usar TOP junto com uma cláusula ORDER BY especificada em uma instrução de subseleção. Consulte a seção Exemplos a seguir neste tópico.
Consultas INSERT que usam SELECT com ORDER BY para popular linhas garantem a forma como os valores de identidade são calculados, mas não a ordem na qual as linhas são inseridas.
No Parallel Data Warehouse, a cláusula ORDER BY será inválida em VIEWS, CREATE TABLE AS SELECT, INSERT SELECT, funções embutidas, tabelas derivadas, subconsultas e expressões de tabela comuns, a menos que TOP também esteja especificado.
Comportamento de log
A instrução INSERT é sempre totalmente registrada em log, exceto ao usar a função OPENROWSET 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, é necessário criar um mapeamento de logon entre os servidores vinculados usando sp_addlinkedsrvlogin.
Quando você usar OPENROWSET(BULK…), é importante entender como o SQL Server manipula a representação. Para obter mais informações, confira "Considerações sobre segurança" em Importar dados em massa usando BULK INSERT ou OPENROWSET(BULK...) (SQL Server).
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, das funções de banco de dados fixas db_owner e db_datawriter e do proprietário da tabela. Os membros das funções sysadmin, db_owner e db_securityadmin, bem como 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.
Exemplos
| Categoria | Elementos de sintaxe em destaque |
|---|---|
| Sintaxe básica | INSERT * construtor de valor de tabela |
| Manipulando valores de coluna | IDENTITY * NEWID * valores padrão * tipos definidos pelo usuário |
| Inserindo dados de outras tabelas | INSERT...SELECT * INSERT...EXECUTE * WITH expressão de tabela comum * TOP * OFFSET FETCH |
| Especificando objetos de destino que não sejam de tabelas padrão | Exibições * variáveis de tabela |
| Inserindo linhas em uma tabela remota | Servidor vinculado * função de conjunto de linhas OPENQUERY * função de conjunto de linhas OPENDATASOURCE |
| Carregamento de dados em massa por meio de tabelas ou arquivos de dados | INSERT…SELECT * função OPENROWSET |
| Substituindo o comportamento padrão do otimizador de consulta usando dicas | Dicas de tabela |
| Capturando os resultados da instrução INSERT | cláusula OUTPUT |
Sintaxe básica
Os exemplos nesta seção demonstram a funcionalidade básica da instrução INSERT usando a sintaxe mínima necessária.
a. Inserindo uma única linha de dados
O exemplo a seguir insere uma linha na tabela Production.UnitMeasure do banco de dados AdventureWorks2022. As colunas nesta tabela são UnitMeasureCode, Name e ModifiedDate. Como os valores de todas as colunas são fornecidos e listados na mesma ordem que as colunas da tabela, os nomes das colunas não precisam ser especificados na lista de colunas*.*
INSERT INTO Production.UnitMeasure
VALUES (N'FT', N'Feet', '20080414');
B. Inserindo várias linhas de dados
O exemplo a seguir usa o construtor de valor de tabela para inserir três linhas na tabela Production.UnitMeasure do banco de dados AdventureWorks2022 em uma única instrução INSERT. Como os valores de todas as colunas são fornecidos e listados na mesma ordem que as colunas da tabela, os nomes das colunas não precisam ser especificados na lista de colunas.
Observação
O construtor de valor de tabela não tem suporte no Azure Synapse Analytics.
INSERT INTO Production.UnitMeasure
VALUES (N'FT2', N'Square Feet ', '20080923'), (N'Y', N'Yards', '20080923')
, (N'Y3', N'Cubic Yards', '20080923');
C. Inserindo dados que não estão na mesma ordem que as colunas da tabela
O exemplo a seguir usa uma lista de colunas para especificar explicitamente os valores inseridos em cada coluna. A ordem das colunas na tabela Production.UnitMeasure do banco de dados AdventureWorks2022 é UnitMeasureCode, Name e ModifiedDate. No entanto, as colunas não estão listadas nessa ordem em column_list.
INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode,
ModifiedDate)
VALUES (N'Square Yards', N'Y2', GETDATE());
Manipulando valores de coluna
Os exemplos nesta seção demonstram métodos de inserção de valores em colunas definidas com uma propriedade IDENTITY, valor DEFAULT ou com tipos de dados, como uniqueidentifier ou colunas de tipo definido pelo usuário.
D. Inserindo dados em uma tabela com colunas que têm valores padrão
O exemplo a seguir mostra como inserir linhas em uma tabela com colunas que geram automaticamente um valor ou têm um valor padrão. Column_1 é uma coluna computada que gera automaticamente um valor concatenando uma cadeia de caracteres com o valor inserido em column_2. Column_2 é definido com uma restrição padrão. Se um valor não for especificado para essa coluna, o valor padrão será usado. Column_3 é definido com o tipo de dados rowversion, que gera automaticamente um número binário exclusivo de incremento. Column_4 não gera um valor automaticamente. Quando um valor para esta coluna não é especificado, NULL é inserido. As instruções INSERT inserem linhas que contêm valores para algumas das colunas, mas não todas. Na última instrução INSERT, nenhuma coluna é especificada e somente os valores padrão são inseridos com o uso da cláusula DEFAULT VALUES.
CREATE TABLE dbo.T1
(
column_1 AS 'Computed column ' + column_2,
column_2 varchar(30)
CONSTRAINT default_name DEFAULT ('my column default'),
column_3 rowversion,
column_4 varchar(40) NULL
);
GO
INSERT INTO dbo.T1 (column_4)
VALUES ('Explicit value');
INSERT INTO dbo.T1 (column_2, column_4)
VALUES ('Explicit value', 'Explicit value');
INSERT INTO dbo.T1 (column_2)
VALUES ('Explicit value');
INSERT INTO T1 DEFAULT VALUES;
GO
SELECT column_1, column_2, column_3, column_4
FROM dbo.T1;
GO
E. Inserindo dados em uma tabela com uma coluna de identidade
O exemplo a seguir mostra métodos diferentes para inserção de dados em uma coluna de identidade. As primeiras duas instruções INSERT permitem identificar valores de identidade a serem gerados para as novas linhas. A terceira instrução INSERT substitui a propriedade IDENTITY da coluna com a instrução SET IDENTITY_INSERT e insere um valor explícito na coluna de identidade.
CREATE TABLE dbo.T1 ( column_1 int IDENTITY, column_2 VARCHAR(30));
GO
INSERT T1 VALUES ('Row #1');
INSERT T1 (column_2) VALUES ('Row #2');
GO
SET IDENTITY_INSERT T1 ON;
GO
INSERT INTO T1 (column_1,column_2)
VALUES (-99, 'Explicit identity value');
GO
SELECT column_1, column_2
FROM T1;
GO
F. Inserindo dados em uma coluna uniqueidentifier usando NEWID()
O exemplo a seguir usa a função NEWID() para obter um GUID para column_2. Ao contrário do que acontece com colunas de identidade, o Mecanismo de Banco de Dados não gera valores automaticamente para colunas com o tipo de dados uniqueidentifier, conforme mostrado pela segunda instrução INSERT.
CREATE TABLE dbo.T1
(
column_1 int IDENTITY,
column_2 uniqueidentifier,
);
GO
INSERT INTO dbo.T1 (column_2)
VALUES (NEWID());
INSERT INTO T1 DEFAULT VALUES;
GO
SELECT column_1, column_2
FROM dbo.T1;
G. Inserindo dados em colunas de tipo definido pelo usuário
As instruções Transact-SQL a seguir inserem três linhas na PointValuecoluna da Points tabela. Essa coluna usa um UDT (tipo de dado CLR definido pelo usuário). O tipo de dados Point consiste em valores inteiros de X e Y que são expostos como propriedades do UDT. Você deve usar a função CAST ou CONVERT para converter os valores X e Y delimitados por vírgulas no tipo Point. As duas primeiras instruções usam a função CONVERT para converter um valor de cadeia de caracteres no tipo Point e a terceira instrução usa a função CAST. Para obter mais informações, consulte Manipulando dados de UDT.
INSERT INTO dbo.Points (PointValue) VALUES (CONVERT(Point, '3,4'));
INSERT INTO dbo.Points (PointValue) VALUES (CONVERT(Point, '1,5'));
INSERT INTO dbo.Points (PointValue) VALUES (CAST ('1,99' AS Point));
Inserindo dados de outras tabelas
Os exemplos nesta seção demonstram métodos para a inserção de linhas de uma tabela em outra tabela.
H. Usando as opções SELECT e EXECUTE para inserir dados de outras tabelas
O exemplo a seguir mostra como inserir dados de uma tabela em outra tabela usando INSERT...SELECT ou INSERT...EXECUTE. Cada um é baseado em uma instrução SELECT de várias tabelas que inclui uma expressão e um valor literal na lista de colunas.
A primeira instrução INSERT usa uma instrução SELECT para obter os dados das tabelas de origem (Employee, SalesPerson e Person) no banco de dados AdventureWorks2022 e armazenar o conjunto de resultados na tabela EmployeeSales. A segunda instrução INSERT usa a cláusula EXECUTE para chamar um procedimento armazenado que contém a instrução SELECT, e a terceira INSERT usa a cláusula EXECUTE para referenciar a instrução SELECT como uma cadeia literal.
CREATE TABLE dbo.EmployeeSales
( DataSource varchar(20) NOT NULL,
BusinessEntityID varchar(11) NOT NULL,
LastName varchar(40) NOT NULL,
SalesDollars money NOT NULL
);
GO
CREATE PROCEDURE dbo.uspGetEmployeeSales
AS
SET NOCOUNT ON;
SELECT 'PROCEDURE', sp.BusinessEntityID, c.LastName,
sp.SalesYTD
FROM Sales.SalesPerson AS sp
INNER JOIN Person.Person AS c
ON sp.BusinessEntityID = c.BusinessEntityID
WHERE sp.BusinessEntityID LIKE '2%'
ORDER BY sp.BusinessEntityID, c.LastName;
GO
--INSERT...SELECT example
INSERT INTO dbo.EmployeeSales
SELECT 'SELECT', sp.BusinessEntityID, c.LastName, sp.SalesYTD
FROM Sales.SalesPerson AS sp
INNER JOIN Person.Person AS c
ON sp.BusinessEntityID = c.BusinessEntityID
WHERE sp.BusinessEntityID LIKE '2%'
ORDER BY sp.BusinessEntityID, c.LastName;
GO
--INSERT...EXECUTE procedure example
INSERT INTO dbo.EmployeeSales
EXECUTE dbo.uspGetEmployeeSales;
GO
--INSERT...EXECUTE('string') example
INSERT INTO dbo.EmployeeSales
EXECUTE
('
SELECT ''EXEC STRING'', sp.BusinessEntityID, c.LastName,
sp.SalesYTD
FROM Sales.SalesPerson AS sp
INNER JOIN Person.Person AS c
ON sp.BusinessEntityID = c.BusinessEntityID
WHERE sp.BusinessEntityID LIKE ''2%''
ORDER BY sp.BusinessEntityID, c.LastName
');
GO
--Show results.
SELECT DataSource,BusinessEntityID,LastName,SalesDollars
FROM dbo.EmployeeSales;
I. Usando a expressão de tabela comum WITH para definir os dados inseridos
O exemplo a seguir cria a tabela NewEmployee no banco de dados AdventureWorks2022. Uma expressão de tabela comum (EmployeeTemp) define as linhas de uma ou mais tabelas a serem inseridas na tabela NewEmployee. A instrução INSERT faz referência às colunas na expressão de tabela comum.
CREATE TABLE HumanResources.NewEmployee
(
EmployeeID int NOT NULL,
LastName nvarchar(50) NOT NULL,
FirstName nvarchar(50) NOT NULL,
PhoneNumber Phone NULL,
AddressLine1 nvarchar(60) NOT NULL,
City nvarchar(30) NOT NULL,
State nchar(3) NOT NULL,
PostalCode nvarchar(15) NOT NULL,
CurrentFlag Flag
);
GO
WITH EmployeeTemp (EmpID, LastName, FirstName, Phone,
Address, City, StateProvince,
PostalCode, CurrentFlag)
AS (SELECT
e.BusinessEntityID, c.LastName, c.FirstName, pp.PhoneNumber,
a.AddressLine1, a.City, sp.StateProvinceCode,
a.PostalCode, e.CurrentFlag
FROM HumanResources.Employee e
INNER JOIN Person.BusinessEntityAddress AS bea
ON e.BusinessEntityID = bea.BusinessEntityID
INNER JOIN Person.Address AS a
ON bea.AddressID = a.AddressID
INNER JOIN Person.PersonPhone AS pp
ON e.BusinessEntityID = pp.BusinessEntityID
INNER JOIN Person.StateProvince AS sp
ON a.StateProvinceID = sp.StateProvinceID
INNER JOIN Person.Person as c
ON e.BusinessEntityID = c.BusinessEntityID
)
INSERT INTO HumanResources.NewEmployee
SELECT EmpID, LastName, FirstName, Phone,
Address, City, StateProvince, PostalCode, CurrentFlag
FROM EmployeeTemp;
GO
J. Usando TOP para limitar os dados inseridos na tabela de origem
O exemplo a seguir cria a tabela EmployeeSales e insere o nome e os dados de vendas acumuladas no ano dos cinco principais funcionários aleatórios da tabela HumanResources.Employee no banco de dados AdventureWorks2022. A instrução INSERT escolhe quaisquer cinco linhas retornadas pela instrução SELECT. A cláusula OUTPUT exibe as linhas inseridas na tabela EmployeeSales. Observe que a cláusula ORDER BY na instrução SELECT não é usada para determinar os cinco funcionários principais.
CREATE TABLE dbo.EmployeeSales
( EmployeeID nvarchar(11) NOT NULL,
LastName nvarchar(20) NOT NULL,
FirstName nvarchar(20) NOT NULL,
YearlySales money NOT NULL
);
GO
INSERT TOP(5)INTO dbo.EmployeeSales
OUTPUT inserted.EmployeeID, inserted.FirstName,
inserted.LastName, inserted.YearlySales
SELECT sp.BusinessEntityID, c.LastName, c.FirstName, sp.SalesYTD
FROM Sales.SalesPerson AS sp
INNER JOIN Person.Person AS c
ON sp.BusinessEntityID = c.BusinessEntityID
WHERE sp.SalesYTD > 250000.00
ORDER BY sp.SalesYTD DESC;
Se você precisar usar TOP para inserir linhas em uma ordem cronológica significativa, deverá usar TOP com ORDER BY em uma instrução de subseleção, conforme mostrado no exemplo a seguir. A cláusula OUTPUT exibe as linhas inseridas na tabela EmployeeSales. Observe que os cinco funcionários principais agora são inseridos com base nos resultados da cláusula ORDER BY em vez de linhas aleatórias.
INSERT INTO dbo.EmployeeSales
OUTPUT inserted.EmployeeID, inserted.FirstName,
inserted.LastName, inserted.YearlySales
SELECT TOP (5) sp.BusinessEntityID, c.LastName, c.FirstName, sp.SalesYTD
FROM Sales.SalesPerson AS sp
INNER JOIN Person.Person AS c
ON sp.BusinessEntityID = c.BusinessEntityID
WHERE sp.SalesYTD > 250000.00
ORDER BY sp.SalesYTD DESC;
Especificando objetos de destino diferentes de tabelas padrão
Os exemplos desta seção demonstram como inserir linhas com a especificação de uma exibição ou variável de tabela.
K. Inserindo dados especificando uma exibição
O exemplo a seguir especifica um nome de exibição como objeto de destino. No entanto, a nova linha é inserida na tabela básica subjacente. A ordem dos valores na instrução INSERT deve corresponder à ordem das colunas da exibição. Para obter mais informações, confira Modificar dados por meio de uma exibição.
CREATE TABLE T1 ( column_1 int, column_2 varchar(30));
GO
CREATE VIEW V1 AS
SELECT column_2, column_1
FROM T1;
GO
INSERT INTO V1
VALUES ('Row 1',1);
GO
SELECT column_1, column_2
FROM T1;
GO
SELECT column_1, column_2
FROM V1;
GO
L. Inserindo dados em uma variável de tabela
O exemplo a seguir especifica uma variável de tabela como o objeto de destino no banco de dados AdventureWorks2022.
-- Create the table variable.
DECLARE @MyTableVar table(
LocationID int NOT NULL,
CostRate smallmoney NOT NULL,
NewCostRate AS CostRate * 1.5,
ModifiedDate datetime);
-- Insert values into the table variable.
INSERT INTO @MyTableVar (LocationID, CostRate, ModifiedDate)
SELECT LocationID, CostRate, GETDATE()
FROM Production.Location
WHERE CostRate > 0;
-- View the table variable result set.
SELECT * FROM @MyTableVar;
GO
Inserindo linhas em uma tabela remota
Os exemplos desta seção demonstram como inserir linhas em uma tabela de destino remoto usando um servidor vinculado ou uma função de conjunto de linhas para referenciar a tabela remota.
M. Inserindo dados em uma tabela remota usando um servidor vinculado
O exemplo a seguir insere linhas em uma tabela remota. O exemplo começa criando um link com a fonte de dados remota usando sp_addlinkedserver. O nome do servidor vinculado, MyLinkServer, é especificado, em seguida, como parte do nome de objeto de quatro partes no formulário server.catalog.schema.object.
Aplica-se a: SQL Server 2008 (10.0.x) e posterior.
USE master;
GO
-- Create a link to the remote data source.
-- Specify a valid server name for @datasrc as 'server_name'
-- or 'server_nameinstance_name'.
EXEC sp_addlinkedserver @server = N'MyLinkServer',
@srvproduct = N' ',
@provider = N'SQLNCLI',
@datasrc = N'server_name',
@catalog = N'AdventureWorks2022';
GO
-- Specify the remote data source in the FROM clause using a four-part name
-- in the form linked_server.catalog.schema.object.
INSERT INTO MyLinkServer.AdventureWorks2022.HumanResources.Department (Name, GroupName)
VALUES (N'Public Relations', N'Executive General and Administration');
GO
N. Inserindo dados em uma tabela remota usando a função OPENQUERY
O exemplo a seguir insere uma linha em uma tabela remota especificando a função do conjunto de linhas OPENQUERY. O nome de servidor vinculado criado no exemplo anterior é usado neste exemplo.
Aplica-se a: SQL Server 2008 (10.0.x) e posterior.
INSERT OPENQUERY (MyLinkServer,
'SELECT Name, GroupName
FROM AdventureWorks2022.HumanResources.Department')
VALUES ('Environmental Impact', 'Engineering');
GO
O. Inserindo dados em uma tabela remota usando a função OPENDATASOURCE
O exemplo a seguir insere uma linha em uma tabela remota especificando a função do conjunto de linhas OPENDATASOURCE. Especifique um nome do servidor válido para a fonte de dados usando o formato server_name ou server_name\instance_name.
Aplica-se a: SQL Server 2008 (10.0.x) e posterior.
-- Use the OPENDATASOURCE function to specify the remote data source.
-- Specify a valid server name for Data Source using the format
-- server_name or server_nameinstance_name.
INSERT INTO OPENDATASOURCE('SQLNCLI',
'Data Source= <server_name>; Integrated Security=SSPI')
.AdventureWorks2022.HumanResources.Department (Name, GroupName)
VALUES (N'Standards and Methods', 'Quality Assurance');
GO
P. Fazendo uma inserção em uma tabela externa criada com o PolyBase
Exporte dados do SQL Server para o Hadoop ou armazenamento do Azure. Primeiro, crie uma tabela externa que aponta para o diretório ou arquivo de destino. Em seguida, use INSERT INTO para exportar dados de uma tabela do SQL Server local para uma fonte de dados externa. A instrução INSERT INTO cria o arquivo ou o diretório de destino se eles não existirem, e os resultados da instrução SELECT são exportados para o local especificado no formato de arquivo especificado. Para obter mais informações, consulte Introdução ao PolyBase.
Aplica-se a: SQL Server.
-- Create an external table.
CREATE EXTERNAL TABLE [dbo].[FastCustomers2009] (
[FirstName] char(25) NOT NULL,
[LastName] char(25) NOT NULL,
[YearlyIncome] float NULL,
[MaritalStatus] char(1) NOT NULL
)
WITH (
LOCATION='/old_data/2009/customerdata.tbl',
DATA_SOURCE = HadoopHDP2,
FILE_FORMAT = TextFileFormat,
REJECT_TYPE = VALUE,
REJECT_VALUE = 0
);
-- Export data: Move old data to Hadoop while keeping
-- it query-able via external table.
INSERT INTO dbo.FastCustomer2009
SELECT T.* FROM Insured_Customers T1 JOIN CarSensor_Data T2
ON (T1.CustomerKey = T2.CustomerKey)
WHERE T2.YearMeasured = 2009 and T2.Speed > 40;
Carregamento de dados em massa a partir de tabelas ou arquivos de dados
Os exemplos nesta seção demonstram dois métodos para carregar dados em massa em uma tabela usando a instrução INSERT.
Q. Inserindo dados em um heap com registro em log mínimo
O exemplo a seguir cria uma nova tabela (um heap) e insere dados de outra tabela nela usando o registro em log mínimo. O exemplo pressupõe que o modelo de recuperação do banco de dados AdventureWorks2022 esteja definido como FULL. Para assegurar um registro em log mínimo, o modelo de recuperação do banco de dados AdventureWorks2022 é definido como BULK_LOGGED antes da inserção das linhas e redefinido como FULL após a instrução INSERT INTO...SELECT. Além disso, a dica TABLOCK é especificada para o tabela de destino Sales.SalesHistory. Isso garante que a instrução use espaço mínimo no log de transação e seja executada de forma eficaz.
-- Create the target heap.
CREATE TABLE Sales.SalesHistory(
SalesOrderID int NOT NULL,
SalesOrderDetailID int NOT NULL,
CarrierTrackingNumber nvarchar(25) NULL,
OrderQty smallint NOT NULL,
ProductID int NOT NULL,
SpecialOfferID int NOT NULL,
UnitPrice money NOT NULL,
UnitPriceDiscount money NOT NULL,
LineTotal money NOT NULL,
rowguid uniqueidentifier ROWGUIDCOL NOT NULL,
ModifiedDate datetime NOT NULL );
GO
-- Temporarily set the recovery model to BULK_LOGGED.
ALTER DATABASE AdventureWorks2022
SET RECOVERY BULK_LOGGED;
GO
-- Transfer data from Sales.SalesOrderDetail to Sales.SalesHistory
INSERT INTO Sales.SalesHistory WITH (TABLOCK)
(SalesOrderID,
SalesOrderDetailID,
CarrierTrackingNumber,
OrderQty,
ProductID,
SpecialOfferID,
UnitPrice,
UnitPriceDiscount,
LineTotal,
rowguid,
ModifiedDate)
SELECT * FROM Sales.SalesOrderDetail;
GO
-- Reset the recovery model.
ALTER DATABASE AdventureWorks2022
SET RECOVERY FULL;
GO
R. Usando uma função OPENROWSET com BULK para carregar dados em massa em uma tabela
O exemplo a seguir insere linhas de um arquivo de dados em uma tabela especificando a função OPENROWSET. A dica de tabela IGNORE_TRIGGERS é especificada para otimização de desempenho. Para obter mais exemplos, confira Importar Dados em Massa usando BULK INSERT ou OPENROWSET(BULK...) (SQL Server).
Aplica-se a: SQL Server 2008 (10.0.x) e posterior.
INSERT INTO HumanResources.Department WITH (IGNORE_TRIGGERS) (Name, GroupName)
SELECT b.Name, b.GroupName
FROM OPENROWSET (
BULK 'C:SQLFilesDepartmentData.txt',
FORMATFILE = 'C:SQLFilesBulkloadFormatFile.xml',
ROWS_PER_BATCH = 15000)AS b ;
Substituindo o comportamento padrão do otimizador de consulta usando dicas
Os exemplos desta seção demonstram como usar dicas de tabela para substituir temporariamente o comportamento padrão do otimizador de consulta durante o processamento da instrução INSERT.
Cuidado
Como o otimizador de consulta do SQL Server normalmente seleciona o melhor plano de execução para uma consulta, é recomendável que desenvolvedores e administradores de banco de dados experientes usem as dicas apenas como um último recurso.
S. Usando a dica TABLOCK para especificar um método de bloqueio
O exemplo a seguir especifica que um bloqueio exclusivo (X) é executado na tabela Production.Location e é mantido até o fim da instrução INSERT.
Aplica-se a: SQL Server, Banco de Dados SQL.
INSERT INTO Production.Location WITH (XLOCK)
(Name, CostRate, Availability)
VALUES ( N'Final Inventory', 15.00, 80.00);
Capturando os resultados da instrução INSERT
Os exemplos desta seção demonstram como usar a Cláusula OUTPUT para retornar informações ou expressões baseadas em cada linha afetada por uma instrução INSERT. Esses resultados podem ser retornados ao aplicativo de processamento para uso em mensagens de confirmação, arquivamentos e outros requisitos similares de aplicativo.
T. Usando OUTPUT com uma instrução INSERT
O exemplo a seguir insere uma linha na tabela ScrapReason e usa a cláusula OUTPUT para retornar os resultados da instrução para a variável de tabela @MyTableVar. Como a coluna ScrapReasonID está definida com uma propriedade IDENTITY, não é especificado um valor na instrução INSERT para essa coluna. No entanto, observe que o valor gerado pelo Mecanismo de Banco de Dados para a coluna é retornado na cláusula OUTPUT na coluna INSERTED.ScrapReasonID.
DECLARE @MyTableVar table( NewScrapReasonID smallint,
Name varchar(50),
ModifiedDate datetime);
INSERT Production.ScrapReason
OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
INTO @MyTableVar
VALUES (N'Operator error', GETDATE());
--Display the result set of the table variable.
SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate
FROM Production.ScrapReason;
U. Usando OUTPUT com colunas de identidade e colunas computadas
O exemplo a seguir cria a tabela EmployeeSales e, em seguida, insere várias linhas nela por meio de uma instrução INSERT com uma instrução SELECT para recuperar dados das tabelas de origem. A tabela EmployeeSales contém uma coluna de identidade (EmployeeID) e uma coluna computada (ProjectedSales). Como esses valores são gerados pelo Mecanismo de Banco de Dados durante a operação de inserção, nenhuma dessas colunas pode ser definida em @MyTableVar.
CREATE TABLE dbo.EmployeeSales
( EmployeeID int IDENTITY (1,5)NOT NULL,
LastName nvarchar(20) NOT NULL,
FirstName nvarchar(20) NOT NULL,
CurrentSales money NOT NULL,
ProjectedSales AS CurrentSales * 1.10
);
GO
DECLARE @MyTableVar table(
LastName nvarchar(20) NOT NULL,
FirstName nvarchar(20) NOT NULL,
CurrentSales money NOT NULL
);
INSERT INTO dbo.EmployeeSales (LastName, FirstName, CurrentSales)
OUTPUT INSERTED.LastName,
INSERTED.FirstName,
INSERTED.CurrentSales
INTO @MyTableVar
SELECT c.LastName, c.FirstName, sp.SalesYTD
FROM Sales.SalesPerson AS sp
INNER JOIN Person.Person AS c
ON sp.BusinessEntityID = c.BusinessEntityID
WHERE sp.BusinessEntityID LIKE '2%'
ORDER BY c.LastName, c.FirstName;
SELECT LastName, FirstName, CurrentSales
FROM @MyTableVar;
GO
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
FROM dbo.EmployeeSales;
V. Inserindo dados retornados de uma cláusula OUTPUT
O exemplo a seguir captura dados retornados pela cláusula OUTPUT de uma instrução MERGE e insere esses dados em outra tabela. A instrução MERGE atualiza diariamente a coluna Quantity da tabela ProductInventory, com base em pedidos processados na tabela SalesOrderDetail do banco de dados AdventureWorks2022. Ela também exclui linhas de produtos cujos inventários chegaram a 0. O exemplo captura as linhas excluídas e as insere em outra tabela, ZeroInventory, que rastreia produtos sem-estoque.
--Create ZeroInventory table.
CREATE TABLE Production.ZeroInventory (DeletedProductID int, RemovedOnDate DateTime);
GO
INSERT INTO Production.ZeroInventory (DeletedProductID, RemovedOnDate)
SELECT ProductID, GETDATE()
FROM
( MERGE Production.ProductInventory AS pi
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
JOIN Sales.SalesOrderHeader AS soh
ON sod.SalesOrderID = soh.SalesOrderID
AND soh.OrderDate = '20070401'
GROUP BY ProductID) AS src (ProductID, OrderQty)
ON (pi.ProductID = src.ProductID)
WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0
THEN DELETE
WHEN MATCHED
THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
OUTPUT $action, deleted.ProductID) AS Changes (Action, ProductID)
WHERE Action = 'DELETE';
IF @@ROWCOUNT = 0
PRINT 'Warning: No rows were inserted';
GO
SELECT DeletedProductID, RemovedOnDate FROM Production.ZeroInventory;
W. Inserindo dados usando a opção SELECT
O exemplo a seguir mostra como inserir várias linhas de dados usando uma instrução INSERT com uma opção SELECT. A primeira instrução INSERT usa uma instrução SELECT diretamente para recuperar dados das tabelas de origem e, em seguida, armazenar o conjunto de resultados na tabela EmployeeTitles.
CREATE TABLE EmployeeTitles
( EmployeeKey INT NOT NULL,
LastName varchar(40) NOT NULL,
Title varchar(50) NOT NULL
);
INSERT INTO EmployeeTitles
SELECT EmployeeKey, LastName, Title
FROM ssawPDW.dbo.DimEmployee
WHERE EndDate IS NULL;
X. Especificando um rótulo com a instrução INSERT
O exemplo a seguir mostra o uso de um rótulo com uma instrução INSERT.
-- Uses AdventureWorks
INSERT INTO DimCurrency
VALUES (500, N'C1', N'Currency1')
OPTION ( LABEL = N'label1' );
Y. Usando um rótulo e uma dica de consulta com a instrução INSERT
Esta consulta mostra a sintaxe básica de uso de um rótulo e uma dica de junção de consulta com a instrução INSERT. Depois que a consulta é enviada para o nó de Controle, o SQL Server, em execução nos nós de Computação, aplicará a estratégia de junção hash ao gerar o plano de consulta do SQL Server. Para obter mais informações sobre dicas de junção e como usar a cláusula OPTION, consulte OPTION (SQL Server PDW).
-- Uses AdventureWorks
INSERT INTO DimCustomer (CustomerKey, CustomerAlternateKey,
FirstName, MiddleName, LastName )
SELECT ProspectiveBuyerKey, ProspectAlternateKey,
FirstName, MiddleName, LastName
FROM ProspectiveBuyer p JOIN DimGeography g ON p.PostalCode = g.PostalCode
WHERE g.CountryRegionCode = 'FR'
OPTION ( LABEL = 'Add French Prospects', HASH JOIN);
Consulte Também
BULK INSERT (Transact-SQL)
DELETE (Transact-SQL)
EXECUTE (Transact-SQL)
FROM (Transact-SQL)
IDENTITY (propriedade) (Transact-SQL)
NEWID (Transact-SQL)
SELECT (Transact-SQL)
UPDATE (Transact-SQL)
MERGE (Transact-SQL)
cláusula OUTPUT (Transact-SQL)
Usar as tabelas inseridas e excluídas