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

sql, sql-server, subquery

Solution

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

Problem

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?

Original source