Need help on ( Keep it existing, Add new ) & ( Remove existing, Add new )

Question

Post reply

Need help on ( Keep it existing, Add new ) & ( Remove existing, Add new )

Adelia

SSC-Addicted

Points: 422

September 13, 2021 at 3:50 pm

Go to Answer

#3929141

I've table & EXISTING data as below,

001

Scripts as following,

CREATE TABLE [dbo].[IncidentProgressAssignPeople](
[Id] [int] IDENTITY(1,1) NOT NULL,
[IncidentProgressId] [int] NOT NULL,
[ToUserId] [nvarchar](450) NOT NULL,
[ReplyDate] [datetime] NOT NULL,
[isInProgress] [bit] NULL,
[isInProgressDate] [datetime] NULL,
[isResolved] [bit] NULL,
[isResolvedDate] [datetime] NULL,
[dtVersion] [timestamp] NULL,
 CONSTRAINT [PK_IncidentProgressAssignPeople] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[IncidentProgressAssignPeople] ON 
GO
INSERT [dbo].[IncidentProgressAssignPeople] ([Id], [IncidentProgressId], [ToUserId], [ReplyDate], [isInProgress], [isInProgressDate], [isResolved], [isResolvedDate]) VALUES (4, 145, N'86fb57bd-0cf8-4e55-9b8d-91418720f326', CAST(N'2021-09-13T23:00:30.037' AS DateTime), 0, NULL, 0, NULL)
GO
SET IDENTITY_INSERT [dbo].[IncidentProgressAssignPeople] OFF
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [IncidentProgressAssignPeople_UQ001]    Script Date: 13/9/2021 11:31:28 PM ******/ALTER TABLE [dbo].[IncidentProgressAssignPeople] ADD  CONSTRAINT [IncidentProgressAssignPeople_UQ001] UNIQUE NONCLUSTERED 
(
[IncidentProgressId] ASC,
[ToUserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[IncidentProgressAssignPeople] ADD  CONSTRAINT [DF_IncidentProgressAssignPeople_ReplyDate]  DEFAULT (getdate()) FOR [ReplyDate]
GO
ALTER TABLE [dbo].[IncidentProgressAssignPeople] ADD  CONSTRAINT [DF_IncidentProgressAssignPeople_isInProgress]  DEFAULT ('false') FOR [isInProgress]
GO
ALTER TABLE [dbo].[IncidentProgressAssignPeople] ADD  CONSTRAINT [DF_IncidentProgressAssignPeople_isResolved]  DEFAULT ('false') FOR [isResolved]
GO

There's 2 scenario

Keep it existing, Add new
Remove existing, Add new

Below script is - Keep it existing, Add New.

Declare @GetIncidentProgressId int
Set @GetIncidentProgressId = 145

Declare @valItem xml

-- Keep it existing, add new
Set @valItem = '<data><userId><ToUserId>86fb57bd-0cf8-4e55-9b8d-91418720f326</ToUserId></userId><userId><ToUserId>f1fd0730-28d9-4a1d-bd12-c6af741b04ec</ToUserId></userId></data>'

select @GetIncidentProgressId,
a.b.value('ToUserId[1]','nvarchar(450)')
from
@valItem.nodes('/data/userId') a(b)
Except 
Select [IncidentProgressId], [ToUserId] from [dbo].[IncidentProgressAssignPeople] 
Where [IncidentProgressId] = @GetIncidentProgressId

/*
(No column name)(No column name)
145                f1fd0730-28d9-4a1d-bd12-c6af741b04ec
*/

I don't know - How to Remove existing, Add new. So far, I've this. It Add New Data. But not Remove existing

Declare @GetIncidentProgressId int
Set @GetIncidentProgressId = 145

Declare @valItem xml

-- Remove existing, add new
Set @valItem = '<data><userId><ToUserId>f1fd0730-28d9-4a1d-bd12-c6af741b04ec</ToUserId></userId><userId><ToUserId>5f7e233f-9f01-43a7-9645-1cb57cacb9c9</ToUserId></userId></data>'

select @GetIncidentProgressId,
a.b.value('ToUserId[1]','nvarchar(450)')
from
@valItem.nodes('/data/userId') a(b)
Except 
Select [IncidentProgressId], [ToUserId] from [dbo].[IncidentProgressAssignPeople] 
Where [IncidentProgressId] = @GetIncidentProgressId


/*
(No column name)(No column name)
145                 5f7e233f-9f01-43a7-9645-1cb57cacb9c9
145                 f1fd0730-28d9-4a1d-bd12-c6af741b04ec
*/

Please help. I'm stuck

This topic was modified 3 years, 2 months ago by Adelia.

Adelia

SSC-Addicted

Points: 422
More actions
September 14, 2021 at 2:26 pm
Answer
#3929459

I got the answer. Yeahhh

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63500 More actions · Answer 1

You have data. How about posting CREATE TABLE and INSERT scripts so we can see what you're working with? While you're at it, post the expected result... but for the love of god not as a picture.

Or did you not want an answer?

Adelia SSC-Addicted Points: 422 More actions · Answer 2

pietlinden wrote:

You have data. How about posting CREATE TABLE and INSERT scripts so we can see what you're working with? While you're at it, post the expected result... but for the love of god not as a picture. Or did you not want an answer?

I did. See above. May be you missing. See, Scripts as following, .....