October 1, 2018 at 12:47 pm
I want to insert into the same table with an update or an insert into but by incrementing the SomeThingElseID by 1 and changing the someID to the ID of the someID that is being updated i.e when the insert into or the update happens there are 6 records for someID = 2 but the last three records are updated from someID = 1. I tried a couple of ways but cannot get over the primray key constrainst violation.
thank you
/****** Object: Table [dbo].[SomeCause] Script Date: 10/1/2018 12:42:50 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SomeCause](
[SomeID] [int] NOT NULL,
[SomeType] [nvarchar](5) NOT NULL,
[SomeThingElseID] [smallint] NOT NULL,
[SomeDesc] [nvarchar](4000) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[SomeCause] ([SomeID], [SomeType], [SomeThingElseID], [SomeDesc]) VALUES (1, N'type', 1, N'A')
GO
INSERT [dbo].[SomeCause] ([SomeID], [SomeType], [SomeThingElseID], [SomeDesc]) VALUES (1, N'type', 2, N'B')
GO
INSERT [dbo].[SomeCause] ([SomeID], [SomeType], [SomeThingElseID], [SomeDesc]) VALUES (1, N'type', 3, N'C')
GO
INSERT [dbo].[SomeCause] ([SomeID], [SomeType], [SomeThingElseID], [SomeDesc]) VALUES (2, N'type', 1, N'A')
GO
INSERT [dbo].[SomeCause] ([SomeID], [SomeType], [SomeThingElseID], [SomeDesc]) VALUES (2, N'type', 2, N'B')
GO
INSERT [dbo].[SomeCause] ([SomeID], [SomeType], [SomeThingElseID], [SomeDesc]) VALUES (2, N'type', 3, N'C')
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [_PK] Script Date: 10/1/2018 12:42:50 PM ******/
ALTER TABLE [dbo].[SomeCause] ADD CONSTRAINT [_PK] PRIMARY KEY NONCLUSTERED
(
[SomeID] ASC,
[SomeType] ASC,
[SomeThingElseID] 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
October 1, 2018 at 1:50 pm
You didn't provide enough data to know that you want to INSERT, but you likely want something along these lines:
INSERT INTO [dbo].[SomeCause] ( SomeID, SomeType, SomeThingElseID, SomeDesc )
SELECT SC.SomeID, SC.SomeType, SC.SomeThingElseID_New, SC.SomeDesc
FROM (
SELECT *, SomeThingElseID + ROW_NUMBER() OVER(PARTITION BY SomeID
ORDER BY SomeThingElseID) AS SomeThingElseID_New
FROM dbo.SomeCause
WHERE someID = 2 /*@someID*/
) AS SC
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 1, 2018 at 2:08 pm
I tried the same insert approach and got a similar error. When the update happens the tables should have 9 records with someId=2 having six records i.e. the existing ones and the new ones copied from the someID = 1 when we loop though all the records on that table.
(2, N'type', 1, N'A')
(2, N'type', 2, N'B')
(2, N'type', 3, N'C')
(2, N'type', 4, N'A')
(2, N'type', 5, N'B')
(2, N'type', 6, N'C')
Perhaps i should try a different approach that is not based on SQL to get the updated id.
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint '_PK'. Cannot insert duplicate key in object 'dbo.SomeCause'. The duplicate key value is (2, type, 2).
The statement has been terminated.
October 1, 2018 at 2:35 pm
Sorry, extremely busy at work, didn't finish code before. Maybe as below?
INSERT INTO [dbo].[SomeCause] ( SomeID, SomeType, SomeThingElseID, SomeDesc )
SELECT SC.SomeID, SC.SomeType, SC.SomeThingElseID_New, SC.SomeDesc
FROM (
SELECT *, MAX(SomeThingElseID) OVER () +
ROW_NUMBER() OVER(ORDER BY SomeThingElseID) AS SomeThingElseID_New
FROM dbo.SomeCause
WHERE someID = 2 /*@someID*/
) AS SC
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 1, 2018 at 3:06 pm
That does work, thank you so much. Is it possible assign what records are copied on someID so that if there are multiple someID we can change copy records based on that someID
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply