0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
tua
  • 33
  • 4
  • Not with the single insert statement. But you can create stored procedure and call it - see https://stackoverflow.com/questions/5178697/mysql-insert-into-multiple-tables-database-normalization – mickvav Feb 02 '23 at 19:49
  • which db system are you using? – drum Feb 02 '23 at 20:04

2 Answers2

1

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

insert into links (user_id, position_id)
select 'JOE', position_id from positions
The Impaler
  • 45,731
  • 9
  • 39
  • 76
0

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
targhs
  • 1,477
  • 2
  • 16
  • 29
  • OP said there are 3 records in positions. This would attempt to insert the 3 desired rows 3 times each. – EdmCoff Feb 02 '23 at 20:37