Table1 RoomID | UserID | IsDeleted 1 | U1 | 0 1 | U2 | 0 1 | U3 | 0 DECLARE @Userids VARCHAR(MAX) = 'U5,U6' , @RoomId int = 3007 INSERT INTO Table1 (RoomID, UserID, IsDeleted) SELECT @RoomId, Item, 0 FROM [dbo].[SplitString](@Userids,',') -- [dbo].[SplitString] is function to get item from comma separated string
Now the issue it inserts redundant data. For Example @Userids
is take as 'U5,U7'
then the table results into rows as
Table1 RoomID | UserID | IsDeleted 1 | U1 | 0 1 | U2 | 0 1 | U3 | 0 1 | U5 | 0 1 | U6 | 0 1 | U5 | 0 1 | U7 | 0
My desirable result should be
Table1 RoomID | UserID | IsDeleted 1 | U1 | 0 1 | U2 | 0 1 | U3 | 0 1 | U5 | 0 1 | U6 | 0 1 | U7 | 0
I also tried below query which resulted into 0 row insertion.I mean no row was inserted
DECLARE @Userids VARCHAR(MAX) = 'U5,U7' , @BroadCastId int = 3007 ;WITH CTE AS ( SELECT Item FROM [dbo].[SplitString](@Userids,',') ) INSERT INTO Table1 (RoomID, UserID, IsDeleted) SELECT @RoomId, Item, 0, GETUTCDATE() FROM CTE WHERE NOT EXISTS (SELECT RoomID FROM Table1 WHERE RoomID = @RoomId AND IsDeleted = 0 AND UserID IN (SELECT Item FROM CTE))