Insert the results of the subquery into a table with a constant

Question

The scheme of the tables in question is as follows:

I have a table, let's call it join, which has two columns, both foreign keys for other tables. Let’s name the two columns userid and buildid, so join looks like

+--------------+ | join | |--------------| |userid | |buildingid | +--------------+ 

I basically need to insert a bunch of rows into this table. Each user will be assigned to several buildings, having several entries in this table. Thus, user 13 can be assigned to buildings 1, 2, and 3 as follows

 13 1 13 2 13 3 

I am trying to figure out how to do this in a query if the building numbers are constant, that is, I assign a group of people to the same buildings. Basically, (this is wrong) I want to do

 insert into join (userid, buildingid) values ((select userid from users), 1) 

It makes sense? I also tried using

 select 1 

The error I encountered is that the subquery returns more than one result. I also tried to create a connection, mainly with a static selection request, which was also unsuccessful.

Any thoughts?

Thank you, Chris

Answer 1

Nearly! If you want to insert query values, do not try to put them in the values clause. insert can take select as an argument to values!

 insert into join (userid, buildingid) select userid, 1 from users 

Also, in the spirit of learning more, you can create a table that doesn't exist using the following syntax:

 select userid, 1 as buildingid into join from users 

This only works if the table does not exist, but it is a quick and dirty way to create table copies!