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

sqlsql-serversubquery

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?

Best Solution

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