Inserir em… valores (SELECT… FROM…)

Eu estou tentando INSERT INTO uma tabela usando a input de outra tabela. Embora isso seja totalmente viável para muitos mecanismos de database, eu sempre pareço ter dificuldade em lembrar a syntax correta do mecanismo SQL do dia ( MySQL , Oracle , SQL Server , Informix e DB2 ).

Existe uma syntax silver-bullet vinda de um padrão SQL (por exemplo, SQL-92 ) que me permita inserir os valores sem me preocupar com o database subjacente?

Experimentar:

 INSERT INTO table1 ( column1 ) SELECT col1 FROM table2 

Este é o padrão ANSI SQL e deve funcionar em qualquer DBMS

Isso definitivamente funciona para:

  • Oráculo
  • MS SQL Server
  • MySQL
  • Postgres
  • SQLite v3
  • Teradata
  • DB2
  • Sybase
  • Vertica
  • HSQLDB
  • H2
  • AWS RedShift
  • SAP HANA

@ Shadow_x99 : Isso deve funcionar bem, e você também pode ter várias colunas e outros dados:

 INSERT INTO table1 ( column1, column2, someInt, someVarChar ) SELECT table2.column1, table2.column2, 8, 'some string etc.' FROM table2 WHERE table2.ID = 7; 

Edit: Eu devo mencionar que eu usei apenas esta syntax com Access, SQL 2000/2005 / Express, MySQL e PostgreSQL, então eles devem ser cobertos. Um comentarista apontou que ele funcionará com o SQLite3.

Para obter apenas um valor em um multi valor INSERT de outra tabela, fiz o seguinte no SQLite3:

 INSERT INTO column_1 ( val_1, val_from_other_table ) VALUES('val_1', (SELECT val_2 FROM table_2 WHERE val_2 = something)) 

Ambas as respostas que vejo funcionam bem no Informix especificamente, e são basicamente SQL padrão. Isto é, a notação:

 INSERT INTO target_table[()] SELECT ... FROM ...; 

funciona bem com Informix e, eu esperaria, todo o DBMS. (Uma vez em 5 ou mais anos atrás, esse é o tipo de coisa que o MySQL nem sempre suportava; agora ele tem suporte decente para esse tipo de syntax SQL padrão e, AFAIK, funcionaria bem nesta notação.) é opcional, mas indica as colunas de destino em seqüência, então a primeira coluna do resultado do SELECT irá para a primeira coluna listada, etc. Na ausência da lista de colunas, a primeira coluna do resultado do SELECT vai para o primeira coluna da tabela de destino.

O que pode ser diferente entre os sistemas é a notação usada para identificar tabelas em diferentes bancos de dados – o padrão não tem nada a dizer sobre operações entre bancos de dados (sem falar em inter-DBMS). Com o Informix, você pode usar a seguinte notação para identificar uma tabela:

 [dbase[@server]:][owner.]table 

Ou seja, você pode especificar um database, opcionalmente identificando o servidor que hospeda esse database se ele não estiver no servidor atual, seguido por um proprietário opcional, ponto e, finalmente, o nome da tabela real. O padrão SQL usa o termo esquema para o que o Informix chama o proprietário. Assim, no Informix, qualquer uma das seguintes notações poderia identificar uma tabela:

 table "owner".table dbase:table dbase:owner.table dbase@server:table dbase@server:owner.table 

O proprietário em geral não precisa ser cotado; no entanto, se você usar aspas, será necessário digitar corretamente o nome do proprietário – ele se diferencia maiúsculas de minúsculas. Isso é:

 someone.table "someone".table SOMEONE.table 

todos identificam a mesma tabela. Com o Informix, há uma leve complicação com os bancos de dados MODE ANSI, em que os nomes dos proprietários geralmente são convertidos em letras maiúsculas (o informix é a exceção). Ou seja, em um database MODE ANSI (não comumente usado), você poderia escrever:

 CREATE TABLE someone.table ( ... ) 

e o nome do proprietário no catálogo do sistema seria “ALGUÉM”, em vez de “alguém”. Se você colocar o nome do proprietário entre aspas duplas, ele atuará como um identificador delimitado. Com o SQL padrão, os identificadores delimitados podem ser usados ​​em muitos lugares. Com Informix, você pode usá-los apenas em torno de nomes de proprietários – em outros contextos, o Informix trata strings com aspas simples e aspas duplas como strings, em vez de separar strings com aspas simples como strings e strings com aspas duplas como identificadores delimitados. (Claro, apenas para completar, existe uma variável de ambiente, DELIMIDENT, que pode ser definida – para qualquer valor, mas Y é mais seguro – para indicar que aspas duplas sempre cercam identificadores delimitados e aspas simples sempre envolvem strings.)

Observe que o MS SQL Server consegue usar [identificadores delimitados] entre colchetes. Parece estranho para mim e certamente não faz parte do padrão SQL.

A maioria dos bancos de dados segue a syntax básica,

 INSERT INTO TABLE_NAME SELECT COL1, COL2 ... FROM TABLE_YOU_NEED_TO_TAKE_FROM ; 

Cada database que utilizei segue esta syntax, ou seja, DB2 , SQL Server , MY SQL , PostgresQL

Para adicionar algo na primeira resposta, quando queremos apenas alguns registros de outra tabela (neste exemplo apenas um):

 INSERT INTO TABLE1 (COLUMN1, COLUMN2, COLUMN3, COLUMN4) VALUES (value1, value2, (SELECT COLUMN_TABLE2 FROM TABLE2 WHERE COLUMN_TABLE2 like "blabla"), value4); 

Isso pode ser feito sem especificar as colunas na parte INSERT INTO se você estiver fornecendo valores para todas as colunas na parte SELECT .

Vamos supor que a tabela 1 tenha duas colunas. Esta consulta deve funcionar:

 INSERT INTO table1 SELECT col1, col2 FROM table2 

Isso não funcionaria (o valor para col2 não é especificado):

 INSERT INTO table1 SELECT col1 FROM table2 

Estou usando o MS SQL Server. Não sei como funcionam outros RDMS.

Este é outro exemplo usando valores com select:

 INSERT INTO table1(desc, id, email) SELECT "Hello World", 3, email FROM table2 WHERE ... 

Inserção simples quando a sequência da coluna da tabela é conhecida:

  Insert into Table1 values(1,2,...) 

Inserção simples mencionando coluna:

  Insert into Table1(col2,col4) values(1,2) 

Inserção em massa quando o número de colunas selecionadas de uma tabela (tabela # 2) é igual à tabela de inserção (Tabela 1)

  Insert into Table1 {Column sequence} Select * -- column sequence should be same. from #table2 

Inserção em massa quando você deseja inserir apenas na coluna desejada de uma tabela (tabela 1):

  Insert into Table1 (Column1,Column2 ....Desired Column from Table1) Select Column1,Column2..desired column from #table2 from #table2 

Em vez de VALUES parte da consulta INSERT , basta usar a consulta SELECT como abaixo.

 INSERT INTO table1 ( column1 , 2, 3... ) SELECT col1, 2, 3... FROM table2 

Aqui está outro exemplo onde a fonte é obtida usando mais de uma tabela:

 INSERT INTO cesc_pf_stmt_ext_wrk( PF_EMP_CODE , PF_DEPT_CODE , PF_SEC_CODE , PF_PROL_NO , PF_FM_SEQ , PF_SEQ_NO , PF_SEP_TAG , PF_SOURCE) SELECT PFl_EMP_CODE , PFl_DEPT_CODE , PFl_SEC , PFl_PROL_NO , PF_FM_SEQ , PF_SEQ_NO , PFl_SEP_TAG , PF_SOURCE FROM cesc_pf_stmt_ext, cesc_pfl_emp_master WHERE pfl_sep_tag LIKE '0' AND pfl_emp_code=pf_emp_code(+); COMMIT; 
 INSERT INTO yourtable SELECT fielda, fieldb, fieldc FROM donortable; 

Isso funciona em todos os DBMS

Veja como inserir de várias tabelas. Este exemplo específico é onde você tem uma tabela de mapeamento em muitos ou muitos cenários:

 insert into StudentCourseMap (StudentId, CourseId) SELECT Student.Id, Course.Id FROM Student, Course WHERE Student.Name = 'Paddy Murphy' AND Course.Name = 'Basket weaving for beginners' 

(Eu percebo que a correspondência no nome do aluno pode retornar mais de um valor, mas você tem a idéia. A correspondência com algo diferente de um Id é necessária quando o Id é uma coluna de identidade e é desconhecida.)

Isso funcionou para mim:

 insert into table1 select * from table2 

A sentença é um pouco diferente da da Oracle.

Para o Microsoft SQL Server, recomendo aprender a interpretar o SYNTAX fornecido no MSDN. Com o Google, é mais fácil do que nunca procurar a syntax.

Para este caso em particular, tente

Google: insira o site: microsoft.com

O primeiro resultado será http://msdn.microsoft.com/pt-br/library/ms174335.aspx

role até o exemplo (“Usando as opções SELECT e EXECUTE para inserir dados de outras tabelas”) se achar difícil interpretar a syntax dada na parte superior da página.

 [ WITH  [ ,...n ] ] INSERT { [ TOP ( expression ) [ PERCENT ] ] [ INTO ] {  | rowset_function_limited [ WITH (  [ ...n ] ) ] } { [ ( column_list ) ] [  ] { VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n ] | derived_table < <<<------- Look here ------------------------ | execute_statement <<<<------- Look here ------------------------ |  < <<<------- Look here ------------------------ | DEFAULT VALUES } } } [;] 

Isso deve ser aplicável para qualquer outro RDBMS disponível lá. Não faz sentido lembrar toda a syntax para todos os produtos IMO.

Você poderia tentar isso se quiser inserir todas as colunas usando a tabela SELECT * INTO .

 SELECT * INTO Table2 FROM Table1; 

Eu realmente prefiro o seguinte no SQL Server 2008:

 SELECT Table1.Column1, Table1.Column2, Table2.Column1, Table2.Column2, 'Some String' AS SomeString, 8 AS SomeInt INTO Table3 FROM Table1 INNER JOIN Table2 ON Table1.Column1 = Table2.Column3 

Ele elimina a etapa de adicionar o conjunto Insert () e você apenas seleciona quais valores vão na tabela.

 select * into tmp from orders 

Parece bom, mas funciona apenas se o tmp não existir (cria e preenche). (SQL sever)

Para inserir na tabela tmp existente:

 set identity_insert tmp on insert tmp ([OrderID] ,[CustomerID] ,[EmployeeID] ,[OrderDate] ,[RequiredDate] ,[ShippedDate] ,[ShipVia] ,[Freight] ,[ShipName] ,[ShipAddress] ,[ShipCity] ,[ShipRegion] ,[ShipPostalCode] ,[ShipCountry] ) select * from orders set identity_insert tmp off 

Melhor maneira de inserir vários registros de quaisquer outras tabelas.

 INSERT INTO dbo.Users ( UserID , Full_Name , Login_Name , Password ) SELECT UserID , Full_Name , Login_Name , Password FROM Users_Table (INNER JOIN / LEFT JOIN ...) (WHERE CONDITION...) (OTHER CLAUSE) 

Se você seguir a rota INSERT VALUES para inserir várias linhas, certifique-se de delimitar os VALUES em conjuntos usando parênteses, portanto:

 INSERT INTO `receiving_table` (id, first_name, last_name) VALUES (1002,'Charles','Babbage'), (1003,'George', 'Boole'), (1001,'Donald','Chamberlin'), (1004,'Alan','Turing'), (1005,'My','Widenius'); 

Caso contrário, o MySQL afirma que “A contagem de colunas não corresponde à contagem de valores na linha 1”, e você acaba escrevendo uma postagem trivial quando finalmente descobre o que fazer a respeito.

 INSERT INTO FIRST_TABLE_NAME (COLUMN_NAME) SELECT COLUMN_NAME FROM ANOTHER_TABLE_NAME WHERE CONDITION;