SQL 函数 - 当子查询跟在 =、！=、<、<= 、>、>= 或子查询用作表达式时，不允许这样做

Question

CREATE TABLE Property542
(
PID INT IDENTITY(1,1) NOT NULL ,
PropertyID AS RIGHT ('PID0000' + CAST(PID AS VARCHAR(10)), 10) PERSISTED 
CONSTRAINT PK_Property PRIMARY KEY CLUSTERED,
Availability BIT NOT NULL,
P_Address VARCHAR(30) NOT NULL,
MonthlyRent INT NOT NULL,
Max_tenants INT NOT NULL,
Type_of_Property VARCHAR(15) NOT NULL,
--NIC varchar(10) FOREIGN KEY REFERENCES Landlord (NIC)
)
CREATE TABLE DetachedHouse768
(
PropertyID VARCHAR(10) PRIMARY KEY,
Dept VARCHAR(30) NOT NULL,
)
DECLARE @id TABLE (ID varchar(10))
INSERT INTO Property542
OUTPUT INSERTED.PropertyID INTO @id(ID)
VALUES (1, 'Kandy Road,Pasyala', 5000, 4, 'DetachedHouse'),
(0, 'Kale para,Mathara', 7500, 2, 'Flat'),
(1, 'No34,New Town,Kaluthara', 10000, 1, 'TerransedHouse');
INSERT INTO DetachedHouse768(PropertyID, Dept) 
VALUES ((SELECT ID FROM @id), 'room'),
((SELECT ID FROM @id), 'floor'),
((SELECT ID FROM @id), 'House');

我收到此错误：

子查询返回了 1 个以上的值。当子查询跟在 =、！=、<、<= 、>、>= 或子查询用作表达式时，不允许这样做。该语句已终止。

我想使用此方法一次插入更多数据。

Answer 1

INSERT INTO DetachedHouse768(PropertyID, Dept) 
select i.ID, d.dept
from @id as i
cross join (values('room'), ('floor'), ('House')) as d(dept);

Answer 2

假设您打算为每个先前插入的 Property542 行插入 3DetachedHouse768行(房间、楼层、房屋(，则 DetachedHouse768 上的主要行不正确。PropertyID本身并不是唯一的，因此它不仅是PropertyID和Dept上唯一的组合键。

下面是一个插入...选择带有CROSS JOIN的示例：

CREATE TABLE Property542
(
PID int IDENTITY(1,1) NOT NULL ,
PropertyID  AS RIGHT ('PID0000' + CAST(PID AS VARCHAR(10)), 10) PERSISTED 
CONSTRAINT PK_Property PRIMARY KEY CLUSTERED,
Availability bit NOT null,
P_Address varchar(30) Not null,
MonthlyRent int Not null,
Max_tenants  int Not null,
Type_of_Property varchar(15) Not null
);
CREATE TABLE DetachedHouse768
(
PropertyID varchar(10),
Dept varchar(30) Not null,
CONSTRAINT PK_DetachedHouse768 PRIMARY KEY CLUSTERED(PropertyID,Dept)
);
DECLARE @id TABLE (ID varchar(10))
INSERT INTO Property542
OUTPUT INSERTED.PropertyID INTO @id(ID)
VALUES  (1,'Kandy Road,Pasyala',5000,4,'DetachedHouse')
,(0,' Kale para,Mathara',7500,2,'Flat')
,(1,'No34,New Town,Kaluthara',10000,1,'TerransedHouse');
INSERT INTO DetachedHouse768( PropertyID ,Dept)
SELECT ID, Dept
FROM @id
CROSS JOIN (VALUES
('room')
,('floor')
,('house')
) AS Depts(Dept);

Answer 3

第 2 个INSERT语句中的SELECT ID FROM @id不是确定性的，这意味着它不会只返回 1 个值。

在查询的那部分(子查询(中，它只期望 1 个值，而您的SELECT ID FROM @id(显然(不会这样做，因为缺少您可以使用WHERE子句设置的条件。