如何在SQL Server中使用Sub-SELECT插入外键

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:

我刚刚开始使用SQL Server,而且我在填充测试数据时遇到了麻烦。我有两个表,其中一个具有另一个的外键,我希望能够使用以下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:

这个语句在Oracle中运行正常但在SQL Server中我得到一个错误说:

Subqueries are not allowed in this context.

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

有人知道在SQL Server中执行此操作的正确方法吗?

3 个解决方案

#1

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

#2

You can do:

你可以做:

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

#3

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

如果有多个会计部门,您的查询将在Oracle中失败。

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.

否则,只需使用其他人提出的SELECT FROM Department语法。

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

但是,请注意,如果有多个行的名称设置为“在Deparments中记帐”,则此语法将插入John Doe两次或更多次。

#1

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

#2