CODEWITHSUNDEEP
sqloracle-databaseselectinsert-into
Kevin Cruijssen
Kevin Cruijssen

Reputation: 9326

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

I have the following two tables: Users and Settings.
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 (user_id in my case) with the others 'hard-coded'.

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

Upvotes: 1

Views: 1571

Answers (1)

user330315
user330315

Reputation:

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;

Upvotes: 2

Related Questions