SQL - inserting a combination of set and dynamic data

Question

I need to insert a combination of set and dynamic data into a table.

For example, I have a table called "Sports" and wanted to insert the set value "Soccer" to the column "SportName"; and I also wanted to insert the dynamic value (ID) "ghi3" to the "NumberOfPlayersID" column, which is set in the "NumberOfPlayersLnk" table, e.g.

NumberOfPlayersID | Players
------------------+--------
abc1              |  5
def2              |  7
ghi3              | 11

Note that the IDs in the NumberOfPlayersLnk table are not hardcoded and will change with the DB being re-created.

I tried declaring the NumberOfPlayersID from the NumberOfPlayersLnk table, and adding a SELECT to my INSERT statement to fetch the ID that corresponds to the value I want to insert, then specifying the set values after that.

However, I learned later that SELECT statements can't be used with INSERT INTO VALUES, so I'm not exactly sure how to reformat my syntax.

Here's an example of what I tried previously:

DECLARE @NumberOfPlayersID uuid

SELECT @NumberOfPlayersID = NumberOfPlayersID 
FROM NumberOfPlayersLnk 
WHERE Players = '11'

INSERT INTO Sports (SportName, NumberOfPlayersID)
VALUES ('Soccer', @NumberOfPlayersID)

Thank you in advance for any support.

Answer 1

You could do an insert-select, like:

INSERT INTO Sports (SportName, PlayersID)
SELECT 'Soccer', NumberOfPlayersID
FROM NumberOfPlayersLnk
WHERE Players = '11'