insert data into multiple tables sql code example
Example 1: insert data into multiple tables mysql
BEGIN;
INSERT INTO users (username, password)
VALUES('test', 'test');
INSERT INTO profiles (userid, bio, homepage)
VALUES(LAST_INSERT_ID(),'Hello world!', 'http://www.stackoverflow.com');
COMMIT;
Example 2: how to insert multiple records at once in sql acess
INSERT INTO target_table ( column_name1, column_name2 )
SELECT column_name1, column_name2
FROM (SELECT tableNameColumn AS column_name1, tableNameColumn2 FROM tableName, tableName2
WHERE tableNameColumn = 3 AND tableNameColumn2 = 1
UNION ALL
SELECT tableNameColumn AS column_name1, tableNameColumn2 FROM tableName, tableName2
WHERE tableNameColumn = 3 AND tableNameColumn2 = 1) AS TMP
example:
INSERT INTO ProgKnowledge ( UserID, ProgramID )
SELECT UserID, ProgramID
FROM (SELECT Users.ID AS UserID, Programs.ProgramID FROM Users, Programs
WHERE Programs.ProgramID = 3 AND Users.ID = 1
UNION ALL
SELECT Users.ID AS UserID, Programs.ProgramID FROM Users, Programs
WHERE Programs.ProgramID = 4 AND Users.ID = 1) AS add;