Select each row and insert into another table

问题:

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"

回答1:

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;

回答2:

Your syntax is incorrect, where comes after the join:

 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