Kevin Cruijssen Kevin Cruijssen - 1 year ago 80
SQL Question

Insert multiple rows with hardcoded data, and one column from a multi-result select

I have the following two tables:


What I want to do is add a row to the settings for each existing User. So in pseudo-code:

foreach(NUMBER user_id IN Users){
INSERT INTO "Settings" SET ("name", "value", "type", type_id, overridable)
VALUES ('date_format', 'dd-MM-yyyy', 'USER', user_id, '1');

How do I do this in SQL?

Here at the Using INSERT with the VALUE Clause and a SELECT Subquery part I see the following:

INSERT INTO MyTable (PriKey, Description)
SELECT ForeignKey, Description
FROM SomeView;

which is almost what I want to accomplish, except that it gets all it's insert-values from the other table, instead of only one (
in my case) with the others 'hard-coded'.

EDIT: I'm using SQL with an Oracle database.

Answer Source

Use a select .. insert and provide the constants in the select list:

INSERT INTO "Settings" ("name", "value", "type", type_id, overridable)
select 'date_format', 'dd-MM-yyyy', 'USER', user_id, '1'
from users;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download