INSERT SELECT not working [message #686624] |
Tue, 01 November 2022 11:15 |
|
Unclefool
Messages: 85 Registered: August 2021
|
Member |
|
|
How can I get this INSERT SELECT to work? Any help would be appreciated.
alter session set nls_date_format='dd-mon-yyyy hh24:mi'
/
Generate a row for each minute in a day
CREATE TABLE time_data (
seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
dt DATE
);
INSERT INTO time_data
SELECT to_char(TRUNC(sysdate)+
numtodsinterval (level-1,'minute'),'mm.dd.yyyy hh24:mi') min
FROM dual
CONNECT BY LEVEL <= (24*60);
|
|
|
|
|
|
Re: INSERT SELECT not working [message #686628 is a reply to message #686627] |
Wed, 02 November 2022 04:03 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
John Watson wrote on Tue, 01 November 2022 13:08
In the meantime, if you want help here, I think you need to show what is happening.
Isn't it obvious - identity column must be excluded from insert column list:
SQL> insert into time_data select sysdate from dual;
insert into time_data select sysdate from dual
*
ERROR at line 1:
ORA-00947: not enough values
SQL> insert into time_data(dt) select sysdate from dual;
1 row created.
SQL>
SY.
|
|
|
|
Re: INSERT SELECT not working [message #686630 is a reply to message #686629] |
Wed, 02 November 2022 04:19 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
There is still a learning opportunity: fixing the bug regarding inserting a string into a date. 1 INSERT INTO time_data(dt)
2 SELECT to_char(TRUNC(sysdate)+
3 numtodsinterval (level-1,'minute'),'mm.dd.yyyy hh24:mi') min
4 FROM dual
5* CONNECT BY LEVEL <= (24*60)
orclz> /
SELECT to_char(TRUNC(sysdate)+
*
ERROR at line 2:
ORA-01843: not a valid month
|
|
|