INSERT SELECT statement with only one column for SELECT

I’m using SQL Server Management Studio 18 and I’ve got the following tables with the following columns:

TableA

[ID]
[Name]
[InternalEmplID]

TableB

[ID]
[InternalEmplID]
[Month]
[Year]

I want to do an INSERT SELECT into TableB, maybe something like the following:

INSERT INTO TableB
(InternalEmplID, Month, Year)
VALUES
((SELECT InternalEmplIDFROM TableA WHERE Name = 'John Smith'), 'July', '2020')

Basically, I just want to make an INSERT SELECT where I’m grabbing a column from another table (InternalEmplID), and then for the other columns (Month and Year), I want to add data that’s not dependent on columns/data from another table. Is this possible? I’m not too familiar with INSERT SELECT‘s so this is all new to me.

Answer

Your query will fail if the select returns more than one row. A more flexible option is to use the INSERT ... SELECT syntax and provide literal values in the additional columns:

INSERT INTO TableB (InternalEmplID, Month, Year)
SELECT InternalEmplIDFROM, 'July', '2020' TableA WHERE Name = 'John Smith'