How to insert a foreign key using a Sub-SELECT in SQL Server

Question

I've just started working with SQL Server for the first time and I'm having trouble populating test data. I have two tables where one has a foreign key to the other and I would like to be able to insert a new record using the following SQL:

insert into Employee (
    EmployeeName,
    DepartmentId
) values (
    "John Doe",
    (select Id from Department where DepartmentName = 'Accounting')
);

This statement works fine in Oracle but in SQL Server I get an error saying:

Subqueries are not allowed in this context.

Does anybody know the right way to do this in SQL Server?

Answer 1

INSERT INTO Employee 
    (EmployeeName, DepartmentId)
SELECT 
    'John Doe' AS EmployeeName, Id AS DepartmentId
FROM 
    Department WHERE DepartmentName = 'Accounting';