SQL INSERT statement for TWO TABLES at time with INNER JOIN

Question

I have two tables hello and login_table and below is their structure

user_info
-------
some_id | name | address

login_table
-------
id | username | password

some_id and id are autoincrement indexes.

Now how can i use INSERT statement with INNER JOIN in SQL

at present, i want add below data with same some_id and id

`name` = John
`address` = wall street
`username` = john123
`password` = passw123

below code shows, what i have tried so far.

insert into login_table lt
INNER JOIN user_info ui ON ui.some_id = lt.id
(ui.name, ui.address, lt.username, lt.password) 
values
('John', 'wall street', 'john123', 'passw123')

And this is not the one value, i want to add more than one value at a time.. how can i achieve.

thanks for help.

I don't think this is possible - you'll need two statements – Dave May 16 '12 at 11:10 — Dave, May 16 '12 at 11:10
Also http://stackoverflow.com/a/1382849/632951 – Pacerier May 10 '15 at 07:53 — Pacerier, May 10 '15 at 07:53

score 11 · Accepted Answer · answered May 16 '12 at 11:13

If you need to perform the two INSERT operations atomically, use a transaction:

START TRANSACTION;
INSERT INTO login_table (username, password) VALUES ('john123', 'passw123');
INSERT INTO user_info (name, address) VALUES ('John', 'wall street');
COMMIT;

N.B. Your storage engine must support transactions for this to work (e.g. InnoDB).

To insert multiple values into a table at once, use the multiple rows form of INSERT. As stated in the manual:

INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas. Example:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
The values list for each row must be enclosed within parentheses. The following statement is illegal because the number of values in the list does not match the number of column names:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3,4,5,6,7,8,9);
VALUE is a synonym for VALUES in this context. Neither implies anything about the number of values lists, and either may be used whether there is a single values list or multiple lists.

will also have same `id` ie.. in both table.. `id`'s are auto increment.. i tried and tested but its not working... — Rafee, Jun 13 '12 at 10:21
@Rafee in addition to the above, you can use the LAST_INSERT_ID() when you need to have the same id in both tables in case id was auto-generated. INSERT INTO foo (auto,text) VALUES(NULL,'text'); INSERT INTO bar (id,text) VALUES(LAST_INSERT_ID(),'text'); — Nicholas Credli, Mar 27 '13 at 15:38

score 0 · Answer 2 · answered May 16 '12 at 11:13

0

Insert to two tables is impossible. The second part of your question is possible: you can insert multiple rows in one statement like this:

insert into some_table(col1, col2) values (1,2), (3,4), (5,6);

answered May 16 '12 at 11:13

Adrian Serafin

7,665
5
46
67

score 0 · Answer 3 · answered Dec 16 '21 at 12:40

0

USE [ERPDb]
GO

INSERT INTO [AAA].[UserRole] ([UserId], [RoleId])
    SELECT u.Id, (SELECT Id FROM [AAA].[Role] WHERE Title = 'Employee') FROM [AAA].[User] u
        INNER JOIN [dbo].[BaseDealer] bd ON u.Id = bd.Id
    WHERE bd.DealerType = 0
GO

answered Dec 16 '21 at 12:40

Farzam Yamini

1

SQL INSERT statement for TWO TABLES at time with INNER JOIN

3 Answers3