CODEWITHSUNDEEP
sqldatabaseselectinsertdeclare
cleanuseless
cleanuseless

Reputation: 3

SQL - inserting a combination of set and dynamic data

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.

Upvotes: 0

Views: 47

Answers (1)

Lajos Arpad
Lajos Arpad

Reputation: 76953

You could do an insert-select, like:

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

Upvotes: 0

Related Questions