Inserir (SQL) - Insert (SQL)
Uma instrução SQL INSERT adiciona um ou mais registros a qualquer tabela em um banco de dados relacional .
Forma básica
As declarações de inserção têm o seguinte formato:
INSERT INTO tabela ( column1 [, column2 , column3 ...]) VALUES ( valor1 [, value2 , value3 ...])
O número de colunas e valores deve ser o mesmo. Se uma coluna não for especificada, o valor padrão da coluna será usado. Os valores especificados (ou implícitas) pelo INSERIR declaração deve satisfazer todas as restrições aplicáveis (tais como chaves primárias , CONFIRA restrições , e NOT NULL restrições). Se ocorrer um erro de sintaxe ou se alguma restrição for violada, a nova linha não será adicionada à tabela e um erro será retornado.
Exemplo:
INSERT INTO phone_book (name, number) VALUES ('John Doe', '555-1212');
A abreviação também pode ser usada, aproveitando a ordem das colunas quando a tabela foi criada. Não é necessário especificar todas as colunas na tabela, pois qualquer outra coluna terá seu valor padrão ou permanecerá nula :
INSERT INTO tabela VALUES ( valor1 , [ valor2 , ...])
Exemplo para inserir dados em 2 colunas na tabela phone_book e ignorar quaisquer outras colunas que possam estar após as 2 primeiras na tabela.
INSERT INTO phone_book VALUES ('John Doe', '555-1212');
Formulários avançados
Multirow inserts
Um recurso SQL (desde SQL-92 ) é o uso de construtores de valor de linha para inserir várias linhas de uma vez em uma única instrução SQL:
INSERT INTO tablename (column-a, [column-b, ...])
VALUES ('value-1a', ['value-1b', ...]),
('value-2a', ['value-2b', ...]),
...
Este recurso é compatível com DB2 , SQL Server (desde a versão 10.0 - ou seja, 2008), PostgreSQL (desde a versão 8.2), MySQL , SQLite (desde a versão 3.7.11) e H2 .
Exemplo (assumindo que 'nome' e 'número' são as únicas colunas na tabela 'lista telefônica'):
INSERT INTO phone_book VALUES ('John Doe', '555-1212'), ('Peter Doe', '555-2323');
que pode ser visto como uma abreviatura para as duas declarações
INSERT INTO phone_book VALUES ('John Doe', '555-1212');
INSERT INTO phone_book VALUES ('Peter Doe', '555-2323');
Observe que as duas instruções separadas podem ter semânticas diferentes (especialmente com relação aos disparadores de instrução ) e podem não fornecer o mesmo desempenho de uma única inserção de várias linhas.
Para inserir várias linhas no MS SQL, você pode usar essa construção:
INSERT INTO phone_book
SELECT 'John Doe', '555-1212'
UNION ALL
SELECT 'Peter Doe', '555-2323';
Observe que esta não é uma instrução SQL válida de acordo com o padrão SQL ( SQL: 2003 ) devido à cláusula de subseleção incompleta.
Para fazer o mesmo no Oracle, use a tabela DUAL , que sempre consiste em uma única linha:
INSERT INTO phone_book
SELECT 'John Doe', '555-1212' FROM DUAL
UNION ALL
SELECT 'Peter Doe','555-2323' FROM DUAL
Uma implementação dessa lógica em conformidade com o padrão mostra o seguinte exemplo, ou conforme mostrado acima:
INSERT INTO phone_book
SELECT 'John Doe', '555-1212' FROM LATERAL ( VALUES (1) ) AS t(c)
UNION ALL
SELECT 'Peter Doe','555-2323' FROM LATERAL ( VALUES (1) ) AS t(c)
O Oracle PL / SQL oferece suporte à instrução INSERT ALL , em que várias instruções de inserção são encerradas por um SELECT :
INSERT ALL
INTO phone_book VALUES ('John Doe', '555-1212')
INTO phone_book VALUES ('Peter Doe', '555-2323')
SELECT * FROM DUAL;
No Firebird, a inserção de várias linhas pode ser realizada assim:
INSERT INTO phone_book (name, number)
SELECT 'John Doe', '555-1212' FROM RDB$DATABASE
UNION ALL
SELECT 'Peter Doe', '555-2323' FROM RDB$DATABASE;
O Firebird, entretanto, restringe o número de linhas que podem ser inseridas desta forma, pois há um limite para o número de contextos que podem ser usados em uma única consulta.
Copiando linhas de outras tabelas
Uma instrução INSERT também pode ser usada para recuperar dados de outras tabelas, modificá-los se necessário e inseri-los diretamente na tabela. Tudo isso é feito em uma única instrução SQL que não envolve nenhum processamento intermediário no aplicativo cliente. Uma subseleção é usada em vez da cláusula VALUES . A subseleção pode conter junções, chamadas de função e pode até mesmo consultar a mesma tabela na qual os dados são inseridos. Logicamente, a seleção é avaliada antes do início da operação de inserção real. Um exemplo é dado abaixo.
INSERT INTO phone_book2
SELECT *
FROM phone_book
WHERE name IN ('John Doe', 'Peter Doe')
Uma variação é necessária quando alguns dos dados da tabela de origem estão sendo inseridos na nova tabela, mas não o registro inteiro. (Ou quando os esquemas das tabelas não são os mesmos.)
INSERT INTO phone_book2 (name, number)
SELECT name, number
FROM phone_book
WHERE name IN ('John Doe', 'Peter Doe')
A instrução SELECT produz uma tabela (temporária) e o esquema dessa tabela temporária deve corresponder ao esquema da tabela onde os dados são inseridos.
Valores padrão
É possível inserir uma nova linha sem especificar nenhum dado, usando valores padrão para todas as colunas. No entanto, alguns bancos de dados rejeitam a instrução se nenhum dado for fornecido, como o Microsoft SQL Server e, neste caso, a palavra-chave DEFAULT pode ser usada.
INSERT INTO phone_book
VALUES ( DEFAULT )
Às vezes, os bancos de dados também oferecem suporte a sintaxe alternativa para isso; por exemplo, o MySQL permite omitir a palavra-chave DEFAULT e o T-SQL pode usar VALORES DEFAULT em vez de VALORES (DEFAULT) . A palavra-chave DEFAULT também pode ser usada na inserção normal para preencher explicitamente uma coluna usando o valor padrão dessa coluna:
INSERT INTO phone_book VALUES ( DEFAULT, '555-1212' )
O que acontece quando uma coluna não especifica um valor padrão depende do banco de dados. Por exemplo, MySQL e SQLite preencherão com um valor em branco (exceto quando no modo estrito), enquanto muitos outros bancos de dados rejeitarão a instrução.
Recuperando a chave
Os designers de banco de dados que usam uma chave substituta como chave primária para cada tabela irão se deparar com o cenário ocasional em que precisam recuperar automaticamente a chave primária gerada pelo banco de dados de uma instrução SQL INSERT para uso em outras instruções SQL. A maioria dos sistemas não permite que as instruções SQL INSERT retornem dados de linha. Portanto, torna-se necessário implementar uma solução alternativa em tais cenários. Implementações comuns incluem:
- Usando um procedimento armazenado específico do banco de dados que gera a chave substituta, executa a operação INSERT e, finalmente, retorna a chave gerada. Por exemplo, no Microsoft SQL Server, a chave é recuperada por meio da função especial SCOPE_IDENTITY () , enquanto no SQLite a função é chamada de last_insert_rowid () .
- Usando uma instrução SELECT específica do banco de dados em uma tabela temporária contendo as últimas linhas inseridas. O DB2 implementa esse recurso da seguinte maneira:
SELECT * FROM NEW TABLE ( INSERT INTO phone_book VALUES ( 'Peter Doe','555-2323' ) ) AS t
- O DB2 para z / OS implementa esse recurso da seguinte maneira.
SELECT EMPNO, HIRETYPE, HIREDATE FROM FINAL TABLE ( INSERT INTO EMPSAMP (NAME, SALARY, DEPTNO, LEVEL) VALUES('Mary Smith', 35000.00, 11, 'Associate') );
- Usando uma instrução SELECT após a instrução INSERT com uma função específica do banco de dados que retorna a chave primária gerada para a linha inserida mais recentemente. Por exemplo, LAST_INSERT_ID () para MySQL .
- Usando uma combinação única de elementos do SQL INSERT original em uma instrução SELECT subsequente .
- Usando um GUID na instrução SQL INSERT e recuperando-o em uma instrução SELECT .
- Usando a cláusula OUTPUT na instrução SQL INSERT para MS-SQL Server 2005 e MS-SQL Server 2008.
- Usando uma instrução INSERT com cláusula RETURNING para Oracle .
INSERT INTO phone_book VALUES ( 'Peter Doe','555-2323' ) RETURNING phone_book_id INTO v_pb_id
- Usando uma instrução INSERT com cláusula RETURNING para PostgreSQL (desde 8.2). A lista retornada é idêntica ao resultado de um INSERT .
-
O Firebird tem a mesma sintaxe em declarações de linguagem de modificação de dados (DSQL); a declaração pode adicionar no máximo uma linha. Em procedimentos armazenados, gatilhos e blocos de execução (PSQL), a sintaxe Oracle mencionada anteriormente é usada.
INSERT INTO phone_book VALUES ( 'Peter Doe','555-2323' ) RETURNING phone_book_id
-
O Firebird tem a mesma sintaxe em declarações de linguagem de modificação de dados (DSQL); a declaração pode adicionar no máximo uma linha. Em procedimentos armazenados, gatilhos e blocos de execução (PSQL), a sintaxe Oracle mencionada anteriormente é usada.
- Usar a função IDENTITY () em H2 retorna a última identidade inserida.
SELECT IDENTITY();
Gatilhos
Se os gatilhos forem definidos na tabela em que a instrução INSERT opera, esses gatilhos serão avaliados no contexto da operação. Os triggers BEFORE INSERT permitem a modificação dos valores que devem ser inseridos na tabela. Os gatilhos AFTER INSERT não podem mais modificar os dados, mas podem ser usados para iniciar ações em outras tabelas, por exemplo, para implementar o mecanismo de auditoria.