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

Question

I just started working with SQL Server for the first time, and I had problems filling out test data. I have two tables that have a foreign key for 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:

Subqueries are not allowed in this context.

Does anyone know the correct 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

Oracle, .

, :

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

SELECT FROM Department, .

Note, however, that this syntax will insert John Doetwice or more if multiple lines with are nameset to Accountingin Deparments.