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';

Answer 2

You can do:

insert into Employee (
    EmployeeName,
    DepartmentId
)
SELECT 'John Doe', Id
FROM Department
WHERE DepartmentName = 'Accounting'

Answer 3

Your query will fail in Oracle if there is more than one accounting department.

If you rely on this behavior, use this syntax:

INSERT
INTO    Employee
        (
        EmployeeName,
        DepartmentId
        )
SELECT  "John Doe",
        (
        SELECT  id
        FROM    Department
        WHERE   DepartmentName = 'Accounting'
        )

Otherwise, just use the SELECT FROM Department syntax proposed by others.

Note, however, that this syntax will insert John Doe twice or more, if there are multiple rows with name set to Accounting in Deparments.