Insert results of subquery into table with a constant

Question

The outline of the tables in question are as follows:

I have a table, lets call it join, that has two columns, both foreign keys to other tables. Let's call the two columns userid and buildingid 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 multiple buildings by having multiple entries in this table. So user 13 might be assigned to buildings 1, 2, and 3 by the following

13 1
13 2
13 3

I'm trying to figure out how to do this in a query if the building numbers are constant, that is, I'm assigning 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)

Does that make sense? I've also tried using

select 1

The error I'm running into is that the subquery returns more than one result. I also attempted to create a join, basically with a static select query that was also unsuccessful.

Any thoughts?

Thanks, Chris

Solution

Almost! When you want to insert to values of a query, don't try to put them in the values clause. insert can take a select as an argument for the 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 by using the following syntax:

select userid, 1 as buildingid
into join
from users

That only works if the table doesn't exist, though, but it's a quick and dirty way to create table copies!

Comments

[Chris Thompson]

Dough! So close! That was it! Just as a point of clarification, is it possible to chain multiple select statements to do multiple insertions as you can when you do use the values clause?

[Eric]

Not in T-SQL, unfortunately. If you ever use MySQL, that supports chaining values together. What you can do is insert into join (userid, buildingid) select * from (select 1, 1 union all select 2, 1 union all select 3, 1 ...).

[Chris Thompson]

Ah, very clever. Thanks again for your help!