Home » SQL & PL/SQL » SQL & PL/SQL » INSERT SELECT not working
INSERT SELECT not working [message #686624] Tue, 01 November 2022 11:15 Go to next message
Unclefool
Messages: 65
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 #686625 is a reply to message #686624] Tue, 01 November 2022 11:30 Go to previous messageGo to next message
John Watson
Messages: 8794
Registered: January 2010
Location: Global Village
Senior Member
Your table has two columns. You will need to tell Oracle which column your date is supposed to go into.
Re: INSERT SELECT not working [message #686626 is a reply to message #686625] Tue, 01 November 2022 11:40 Go to previous messageGo to next message
Unclefool
Messages: 65
Registered: August 2021
Member
I tried that and it didn't work
INSERT INTO time_data(seq_num, dt)

Re: INSERT SELECT not working [message #686627 is a reply to message #686626] Tue, 01 November 2022 12:08 Go to previous messageGo to next message
John Watson
Messages: 8794
Registered: January 2010
Location: Global Village
Senior Member
You might want to attend an intro to SQL course Smile

In the meantime, if you want help here, I think you need to show what is happening.
Re: INSERT SELECT not working [message #686628 is a reply to message #686627] Wed, 02 November 2022 04:03 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3201
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 #686629 is a reply to message #686628] Wed, 02 November 2022 04:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68348
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Thu, 28 July 2022 15:31, in OP's previous topic

You give him no chance to learn and encourage his laziness, [...] Sad

Tell me and I'll forget; show me and I may remember; involve me and I'll understand
Re: INSERT SELECT not working [message #686630 is a reply to message #686629] Wed, 02 November 2022 04:19 Go to previous message
John Watson
Messages: 8794
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
Previous Topic: ORA-29005: The certificate is invalid
Next Topic: Pivot query
Goto Forum:
  


Current Time: Sun Feb 05 01:24:04 CST 2023