Home » Developer & Programmer » Forms » how to insert data in two table same time? (database10g)
|
|
| Re: how to insert data in two table same time? [message #542127 is a reply to message #542106] |
Sat, 04 February 2012 11:48   |
 |
Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
One option is to create a database trigger which will insert data into TABLE2 upon insertion into TABLE1.
Another one is to create an ON-INSERT form trigger which suppresses default Forms processing; in there, you'd INSERT INTO TABLE1 and INSERT INTO TABLE2.
Or, create form on a view and use INSTEAD OF database trigger which would do the same as previous suggestion (two INSERT INTO statements).
[Updated on: Sat, 04 February 2012 11:50] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: how to insert data in two table same time? [message #542536 is a reply to message #542527] |
Tue, 07 February 2012 17:11 |
 |
mughals_king
Messages: 392
Registered: January 2012
Location: pakistan
|
Senior Member |
|
|
Thanks Sir for your quick reply sir. I think this query belongs to MYSQL i dont know the exact link of this query i saw these things somewhere in website but i am sure this query belongs to mysql. actually i wanted to do exactly the same thing in sql or sqlplus anywaz thanks for your precious information. Sir i am really thankful to you and looking forward for the future answers from this forum or from LittleFoot kind of geniuses, Sir if i made a mistake somewhere while writing a english i am sorry for that because my mother tongue is not a english.
i have few things which is related to same query which i had downloaded from net as below.
--------------------------------------------------
$qry = "INSERT INTO table (one, two, three) VALUES('$one','$two','$three')";$result = @mysql_query($qry);$qry2 = "INSERT INTO table2 (one,two, three) VVALUES('$one','$two','$three')";$result = @mysql_query($qry2);
------------------------------------------------------------
Thanks & Best regards
[Updated on: Tue, 07 February 2012 17:13] Report message to a moderator |
|
|
|
| Re: how to insert data in two table same time? [message #542561 is a reply to message #542536] |
Wed, 08 February 2012 01:37 |
|
Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
I don't know MySQL, but code you posted doesn't seem to be what you are saying. These are 4 commands (separated by a semi-colon), written in the same line.
As far as I can tell, Oracle doesn't support it in SQL; here are a few examples:SQL> create table mug (col1 number, col2 varchar2(5));
Table created.
SQL> insert into mug (col1, col2) values (1, 'Abc'); insert into mug (col1, col2) values (2, 'Bde');
insert into mug (col1, col2) values (1, 'Abc'); insert into mug (col1, col2) values (2, 'Bde')
*
ERROR at line 1:
ORA-00911: invalid character
SQL> insert into mug (col1, col2) values (1, 'Abc')/ insert into mug (col1, col2) values (2, 'Bde')/
2 /
insert into mug (col1, col2) values (1, 'Abc')/ insert into mug (col1, col2) values (2, 'Bde')/
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> insert into mug (col1, col2) values (1, 'Abc');/ insert into mug (col1, col2) values (2, 'Bde');/
2 /
insert into mug (col1, col2) values (1, 'Abc');/ insert into mug (col1, col2) values (2, 'Bde');/
*
ERROR at line 1:
ORA-00911: invalid character
SQL> drop table mug;
Table dropped.
On the other hand, you can put several statements into the same line in PL/SQL:SQL> create table mug (col1 number, col2 varchar2(5));
Table created.
SQL> create table mug_2 (col1 number, col2 varchar2(5));
Table created.
SQL> begin
2 insert into mug (col1, col2) values (1, 'Abc'); insert into mug_2 (col1, col2) values (2, 'Bde');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select * from mug;
COL1 COL2
---------- -----
1 Abc
SQL> select * from mug_2;
COL1 COL2
---------- -----
2 Bde
SQL>
Obviously, this works and it inserted values into two different tables from the same line, but - as I said - it is NOT a single statement, these are TWO separate INSERT statements.
|
|
|
|
| Re: how to insert data in two table same time? [message #542652 is a reply to message #542561] |
Wed, 08 February 2012 06:13 |
 |
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
|
Senior Member |
|
|
if you want to multi insert into two table you can use this
here we have two table dept_test and dept
INSERT ALL
INTO dept_test (no, name)
VALUES (1, 'parthiv')
INTO dept_test (no, name)
VALUES (1, 'parthiv')
INTO dept_test (no, name)
VALUES (1, 'parthiv')
INTO dept
VALUES (10, 'abc', 'test')
SELECT * FROM DUAL;
and if you want to multiple insert into one table you can use this syntax
INSERT ALL
INTO dept_test (no, name)
VALUES (1, 'parthiv')
INTO dept_test (no, name)
VALUES (1, 'parthiv')
INTO dept_test (no, name)
VALUES (1, 'parthiv')
SELECT * FROM DUAL; [Updated on: Wed, 08 February 2012 06:29] Report message to a moderator |
|
|
|
|
|
| Re: how to insert data in two table same time? [message #542693 is a reply to message #542653] |
Thu, 09 February 2012 14:02 |
|
mughals_king
Messages: 392
Registered: January 2012
Location: pakistan
|
Senior Member |
|
|
Thanks for precious information senior/seniorina "shaan121" and also to Mr.Littlefoot i was very delighted to have this code & glad to hear this that we can do this and i think also we can do accomplish this task like this
create table Test(No varchar2(64),name varchar2(25));
SQL> insert into test
select '001', 'Bob' from dual
union all
select '002', 'James' from dual
union all
select '003', 'Dave' from dual
thanks again to both & kindly consider which i have created a new topic which is related to Tree menu & call_form
i shall be very thankful to you
best regards
[Updated on: Thu, 09 February 2012 14:03] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: how to insert data in two table same time? [message #542702 is a reply to message #542695] |
Thu, 09 February 2012 14:51 |
|
mughals_king
Messages: 392
Registered: January 2012
Location: pakistan
|
Senior Member |
|
|
Thanks Mr.Michel Cadot for this thread just. we were simply tried to insert record in two tables which we have acomplished with the help of some experts as u saw in the forum anyways thanks again for taking this much interest to solve our problems specially for those who r new commers in this forum @ next time we will formatted the code.
best regards
[Updated on: Thu, 09 February 2012 14:56] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Sat Jul 13 12:56:19 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
