Quote (Ideophobe @ May 4 2015 06:11pm)
have been trying it in the environment and it doesnt work
found my answer though
INSERT ALL
INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
SELECT * FROM dual;
for some reason there needs to be a select * from dual clause at the end
i dont really understand it but it has to be there you cant do without it, you still have to mark all your intos, its much easier with mysql
mysql was much easier just insert ... into ... values (), (), ()
you misunderstood the point of the command. its intention isn't to act like INSERT...VALUES, but rather INSERT...SELECT for multiple tables.
suppose you wanted to copy all the data from one table and put it in another. you would do it like so:
Code
insert into backup_table(...) select * from original_table;
now what if you wanted to copy all the data from one table and put it into multiple tables? this is one way of doing it:
Code
insert into backup_table1(...) select * from original_table;
insert into backup_table2(...) select * from original_table;
insert into backup_table3(...) select * from original_table;
however, you'll notice that you just did a full table scan 3 times on the same table. is there a way to insert the same data into multiple tables without doing a full table scan each time? enter INSERT ALL.
Code
insert all
into backup_table1
into backup_table2
into backup_table3
select * from original_table
going back to your example, since you don't want to do INSERT...SELECT but rather INSERT...VALUES, you need to select a single row and fill it with constants, eg INSERT...SELECT * from dual. which is what you did.
personally, i've never used it since i've never needed this scenario. i'm not sure i understand if there is any benefit to using it with constants as opposed to multiple INSERT statements. personally, i'd just write multiple insert statements. it's cleaner, clearer, can be used with any dbms, better used with prepared statements, and i'm guessing it's easier to find errors.
This post was edited by carteblanche on May 4 2015 04:46pm