Insert into new table with userId column for all users from existing users table-CodePudding

I have user_settings table with two columns, userId and value which is currently empty but have another users table and now I need to add migration with query which should popuplate user_settings table for all users with some default value for value column. I began with INSERT INTO "user_settings" ("userId", "value") VALUES ($1, $2) and got stuck. How to loop all id's from users table and fill this user_settings table with some default value...

CodePudding user response：

There is really no need to 'loop' through the rows in users, as you can easily achieve this using an INSERT INTO SELECT statement (https://www.w3schools.com/sql/sql_insert_into_select.asp) like this:

SET @default = 'your_settings';

INSERT INTO `user_settings` (
  `userId`,
  `value`
) 
SELECT
  id,
  @default
FROM
  users

Suppose you are using a DBMS that supports triggers, you could also add a trigger to insert default settings upon inserting new records in users. In MySQL it might look something like this:

DELIMITER $$

CREATE TRIGGER after_user_insert
AFTER INSERT
ON 
  users 
FOR EACH ROW
BEGIN
    INSERT INTO user_settings (
      userId,
      value
    ) 
    VALUES (
      NEW.id,
      'your_settings'
    );
END$$

DELIMITER ;

(Assuming the primary key on users table is called id.)

CodePudding user response：

Assuming some syntax elements here because you didn't say which database system you are using.

INSERT INTO "user_settings" ("userId", "value")
SELECT DISTINCT "userId", 'defaultvalue'
FROM "users"

To learn more, read training materials such as those found at: https://www.w3schools.com/sql/default.asp