CODEWITHSUNDEEP
sqlsql-serverdatabaseselectsql-insert
LOL. NO.
LOL. NO.

Reputation: 677

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.

Upvotes: 5

Views: 8852

Answers (2)

GMB
GMB

Reputation: 222702

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' 
FROM TableA 
WHERE Name = 'John Smith'

Upvotes: 9

Jason
Jason

Reputation: 731

You are pretty close ... simply do

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

Upvotes: 4

Related Questions