How to save the data on different tables?

Question

I have some problem with my SQL statement that I use. I would like to store the data to the two tables that has same ID. the process of insertion should be in one time.

The first table:

ID      name
---     ----
001     john
002     kate

The second table:

 ID     status
 ---    -----------
 001    married
 002    not married

this is the SQL statement that I have tried but it is not working:

insert into `table_one`, 'table table_two' 
values ( ID='003', name='joe'), 
values(ID='003', status='married')

I hope that you can give me some solution..

thanks a lot..

What are you using? mySql? Sql Server? Oracle? Something else? — Kris Krause, Apr 04 '12 at 18:50
Possible duplicate of http://stackoverflow.com/questions/175066/sql-server-is-it-possible-to-insert-into-two-tables-at-the-same-time — Aleks G, Apr 04 '12 at 18:52
i use mysql.. i would like to insert the data into two table in one time.. — user1137094, Apr 04 '12 at 19:32

score 5 · Answer 1 · answered Apr 04 '12 at 18:50

5

An insert statement works on a single table; you need two separate insert statements - one for each table.

If you would like both inserts to either succeed or to fail (as opposed to one succeeding and one failing) you should execute both statements in a single transaction.

answered Apr 04 '12 at 18:50

Sergey Kalinichenko

714,442
84
1,110
1,523

Can i use AND or something else to mix the statement so i only write one single sql statement to insert into two tables? – user1137094 Apr 04 '12 at 19:43
2

@user1137094 No, Data Manipulation Language subset of SQL does not let you manipulate multiple tables at once. However, you can write a *stored procedure* that would combine two inserts into something that you can call in a single statement. – Sergey Kalinichenko Apr 04 '12 at 19:48

score 1 · Answer 2 · answered Apr 04 '12 at 18:51

1

You can't insert data into 2 different tables in one sql statement. You need to split it into 2 statements:

insert into table_one (ID,name) values ('003','joe');
insert into table_two (ID,status) values ('003','married');

Enclose it in a transaction if you need to guarantee that both statements either fail or succeed.

answered Apr 04 '12 at 18:51

Icarus

63,293
14
100
115

actually i would like to save the new data in one time. can i use AND function? to mix the sql insertion.. – user1137094 Apr 04 '12 at 19:35
No, you can't use AND to mix the sql insertion. You are saving the data one time. Even if you could express this one statement the database engine will likely split the insert into two steps anyway. I don't see why are you concerned over this. – Icarus Apr 04 '12 at 19:38

score 0 · Answer 3 · answered Apr 04 '12 at 18:51

0

INSERT INTO 'table_one'(ID, name)
VALUES ('003', 'joe')

INSERT INTO 'table_two' (ID, status)
VALUES ('003', 'married')

answered Apr 04 '12 at 18:51

sll

61,540
22
104
156

How to save the data on different tables?

3 Answers3