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.

Lajos Arpad · Accepted Answer · 2024-04-24 17:04:17Z

0

You could do an insert-select, like:

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

answered Apr 24, 2024 at 17:04

Lajos Arpad

80.4k42 gold badges123 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cleanuseless Over a year ago

As easy as that, thank you so much!

Collectives™ on Stack Overflow

SQL - inserting a combination of set and dynamic data

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related