Home » SQL & PL/SQL » SQL & PL/SQL » multi table insert query
multi table insert query [message #232344] Fri, 20 April 2007 04:11 Go to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi All,

i am getting error in following query

insert ALL into emp values(empno,sal) 
into emp1 values(empno,SAL) 
select 1,10 from dual 


error is as:-ora-00904 sal invalid indentifier

emp and emp1 one have same structure

EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO 


why i am getting erro

oracle version is 9.0.2.6

--Yash


Re: multi table insert query [message #232347 is a reply to message #232344] Fri, 20 April 2007 04:14 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

same question same forum but two threads.,,
why is it so?

[mod-edit]Removed invalid hyperlink, sorry Embarassed

[Updated on: Fri, 20 April 2007 04:19] by Moderator

Report message to a moderator

Re: multi table insert query [message #232349 is a reply to message #232347] Fri, 20 April 2007 04:18 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
i don't know i have posted single time only ...


--Yash
Re: multi table insert query [message #232351 is a reply to message #232349] Fri, 20 April 2007 04:23 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
No problem, I've deleted your duplicate thread.

The reason you get this error is that you haven't named the columns from dual. Give them an alias:
INSERT ALL
      INTO emp
      VALUES (empno, sal)
      INTO emp1
      VALUES (empno, sal)
   SELECT 1   empno
        , 10  sal
   FROM   DUAL
/


But then you'll run into the next problem: ORA-00947: not enough values. Try to figure that one out yourself Wink

MHE

Moved to newbies.
Re: multi table insert query [message #232359 is a reply to message #232351] Fri, 20 April 2007 04:48 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
thx Maaher!! this query is working fine


INSERT ALL
      INTO emp(empno, sal)
      VALUES (empno, sal)
      INTO emp1(empno, sal)
      VALUES (empno, sal)
   SELECT 1   empno
        , 10  sal
   FROM   DUAL



but in this query select column name shoul be same name as inserted table column name ...insted of data type ?


--Yash
Re: multi table insert query [message #232360 is a reply to message #232359] Fri, 20 April 2007 04:50 Go to previous message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
thanks Maaher
i got it
Previous Topic: sql help
Next Topic: PL/SQL Procedure to send PDF File as attachment
Goto Forum:
  


Current Time: Sat Dec 10 03:23:19 CST 2016

Total time taken to generate the page: 0.08162 seconds