How to insert data multiple times in a table (SQL)-CodePudding

I have 3 tables as follows:

DXBusinessPolicy_Policy

ID	Code	Name
1	COMBO.2103001	[Giá nền] T9/2020 #1
2	IPTV-0121.002	[Giá nền] T8/2020 #1

DXBusinessPolicy_Service

ID	Code	Name
1	INT	Internet
2	IPTV	IPTV
3	CMR	Camera
4	FSAFE	Fsafe

DXBusinessPolicy_PolicyService

ID	PolicyID	ServiceID
1	1	1
2	1	2

Here is my stored procedure:

CREATE PROCEDURE InsertPolicyService
    @id int,
    @services varchar(1000) //This is Service Name
AS 
BEGIN 
    INSERT INTO dbo.DXBusinessPolicy_PolicyService (PolicyID, ServiceID) 
        SELECT 
            @id,
            (SELECT dbo.DXBusinessPolicy_Service.ID 
             FROM dbo.DXBusinessPolicy_Service
             WHERE dbo.DXBusinessPolicy_Service.Code IN (SELECT VALUE FROM string_split(@services, ',')))
END 



EXEC InsertPolicyService 2, 'FSAFE,CMR'

I want to insert Policy ID 2 with service named FSAFE and CMR into table DXBusinessPolicy_PolicyService.

I tried to execute this stored procedure, but I get this error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

My expectation is:

DXBusinessPolicy_PolicyService

ID	PolicyID	ServiceID
1	1	1
2	1	2
3	2	3
4	2	4

CodePudding user response：

Your inner sub-query returns multiple results, which isn't possible when its a sub-query. However you don't need that many queries, just the one:

INSERT INTO dbo.DXBusinessPolicy_PolicyService (PolicyID, ServiceID) 
    SELECT @id, dbo.DXBusinessPolicy_Service.ID 
    FROM dbo.DXBusinessPolicy_Service
    WHERE dbo.DXBusinessPolicy_Service.Code IN (
        SELECT VALUE FROM string_split(@services,','))
    );