SQL Server: multiple inserts in a single statement achievable?-CodePudding

I'm using SQL Server. I have a table Users with 2 columns ID (string) and Info (string), and a table Positions with 2 columns PositionID (int) and Description (string).

I need to insert one record into table Links with 2 columns: UserID (string) and PositionID (int), for each record in table Positions.

Thus, given Users.ID = "JOE" and 3 records in Positions table with IDs 1 through 3, I need to insert 3 records into Links:

UserID | PositionID
------- -----------
JOE    | 1
JOE    | 2
JOE    | 3

Is this achievable with a single statement?

CodePudding user response：

You can insert the result of a select statement. For example:

insert into links (user_id, position_id)
select 'JOE', position_id from positions

CodePudding user response：

Adding to @The impaler's solution

insert into links (user_id, position_id)
select 'JOE', 1 from positions
union all select 'JOE', 2 from positions
union all select 'JOE', 3 from positions