选择每一行并插入到另一个表中 [英] Select each row and insert into another table

问题描述

乍一看，这是我的查询

  INSERT INTO T2 
 SELECT T1。*，T3。 DATETIME，T3.STATEID 
 FROM T1 WHERE T3.DATETIME BETWEEN '01 -NOV-15'AND SYSDATE 
 LEFT OUTER JOIN T3 
 ON（T1.DOCID = T3.DOCID）; 
  

T1 AND T2 在列数方面几乎是一致的;只有 T2 有两列额外的（CREATIONDATE，STATEID）



每一行 T1 需要插入 T2 +两个附加列 CREATIONDATE& STATEID ，它们仅在 T3 中可用。 T1 和'T3'共享 DOCID ，可以将它们加入...

有些问题依然存在，非常感谢帮助。

获取错误

 错误报告 -  
 SQL错误：ORA-00933：SQL命令未正确结束
 00933. 00000  - SQL命令未正确结束
  

解决方案

在 WHERE 子句，它必须位于连接表的列表之后：

  INSERT INTO T2 
 SELECT T1。*，T3.DATETIME，T3.STATEID 
 FROM T1 
 LEFT OUTER JOIN T3 
 ON（T1.DOCID = T3.DOCID）
 WHERE T3.DATETIME BETWEEN' 01-NOV-15'AND SYSDATE; 
  

At a first glance, this is my query

 INSERT INTO T2 
 SELECT T1.*,  T3.DATETIME, T3.STATEID
 FROM T1 WHERE T3.DATETIME BETWEEN '01-NOV-15' AND SYSDATE
 LEFT OUTER JOIN T3
 ON (T1.DOCID =  T3.DOCID);

T1 AND T2 are almost identicle in terms of number of columns; only T2 has two columns extra (CREATIONDATE, STATEID)

Each and every row of T1 needs to be inserted in T2 + the two additional columns CREATIONDATE & STATEID which are only available in T3. Both T1 and 'T3' share the DOCID which could be used to join them...

Some issues remain, help is much appreciated..

Getting Error

Error report -
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"

解决方案

The problem in in the position of the WHERE clause, which has to come after the list of joined table:

INSERT INTO T2 
 SELECT T1.*,  T3.DATETIME, T3.STATEID
 FROM T1 
 LEFT OUTER JOIN T3
   ON (T1.DOCID =  T3.DOCID)
WHERE T3.DATETIME BETWEEN '01-NOV-15' AND SYSDATE;

这篇关于选择每一行并插入到另一个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！