October 2, 2018 at 1:05 pm
how can we add a +1 on every insert or an update ? I have table with 3 primary keys and i cannot use MAX(ID) because it fails on the next insert.
My table is shown below.
thank you
CREATE TABLE [dbo].[SomeCause](
[SomeID] [int] NOT NULL,
[SomeType] [nvarchar](5) NOT NULL,
[SomeThingElseID] [smallint] NOT NULL,
[SomeDesc] [nvarchar](4000) NULL,
CONSTRAINT [_PK] PRIMARY KEY NONCLUSTERED
(
[SomeID] ASC,
[SomeType] ASC,
[SomeThingElseID] 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
INSERT [dbo].[SomeCause] ([SomeID], [SomeType], [SomeThingElseID], [SomeDesc]) VALUES (1, N'type', 1, N'SomeID1A')
GO
INSERT [dbo].[SomeCause] ([SomeID], [SomeType], [SomeThingElseID], [SomeDesc]) VALUES (1, N'type', 2, N'SomeID2B')
GO
INSERT [dbo].[SomeCause] ([SomeID], [SomeType], [SomeThingElseID], [SomeDesc]) VALUES (1, N'type', 3, N'SomeID3C')
GO
INSERT [dbo].[SomeCause] ([SomeID], [SomeType], [SomeThingElseID], [SomeDesc]) VALUES (2, N'type', 1, N'SomeID2A')
GO
INSERT [dbo].[SomeCause] ([SomeID], [SomeType], [SomeThingElseID], [SomeDesc]) VALUES (2, N'type', 2, N'SomeID2B')
GO
INSERT [dbo].[SomeCause] ([SomeID], [SomeType], [SomeThingElseID], [SomeDesc]) VALUES (2, N'type', 3, N'SomeID2C')
GO
October 3, 2018 at 3:30 am
I'm not quite sure what you're trying to do here.
Where exactly do you want to add the +1?
Is it an extra column, or do you want to update the existing columns?
October 3, 2018 at 4:05 am
I too am not sure what you're trying to do. How are you attempting to do the insert and what error message do you get? You could try creating two sequence objects, one for each ID you want to increment, and get your values from those.
John
October 3, 2018 at 9:05 am
When an insert happens i am trying to get the result as below, so i am trying to copy the records form SomeID 1 into 2 but i want the records on someId 2 to be inserted with a +1 on somethinglelseID everytime it is inserted. I
(2, N'type', 1, N'SomeID2A')
(2, N'type', 2, N'SomeID2A')
(2, N'type', 3, N'SomeID2A')
(2, N'type', 4, N'SomeID1A')
(2, N'type', 5, N'SomeID1A')
(2, N'type', 6, N'SomeID1A')
October 3, 2018 at 9:40 am
If you're adding one at a time you could do this:
DECLARE @SomeID [int] = 1,
@SomeType [nvarchar](5) = 'type',
@SomeDesc [nvarchar](4000) = 'Example';
INSERT INTO [dbo].[SomeCause] ([SomeID], [SomeType], [SomeThingElseID], [SomeDesc])
SELECT @SomeID, @SomeType, MAX([SomeThingElseID]) +1, @SomeDesc
FROM [dbo].[SomeCause]
WHERE SomeID = @SomeID
AND SomeType = @SomeType;
October 3, 2018 at 9:49 am
No this probabaly will not work as I am tyring to get all the records at once.i have tried with Rowcount but that doesnot work as the Id is created as rowcount Id and i dont fully understand the row count approach.
October 3, 2018 at 3:09 pm
Have you considered just using an identity column? (i.e. an auto-incrementing integer?)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 4, 2018 at 1:42 pm
I wish i could but this is what i have to work with.
October 4, 2018 at 9:38 pm
SQLTestUser - Wednesday, October 3, 2018 9:05 AMWhen an insert happens i am trying to get the result as below, so i am trying to copy the records form SomeID 1 into 2 but i want the records on someId 2 to be inserted with a +1 on somethinglelseID everytime it is inserted. I(2, N'type', 1, N'SomeID2A')
(2, N'type', 2, N'SomeID2A')
(2, N'type', 3, N'SomeID2A')
(2, N'type', 4, N'SomeID1A')
(2, N'type', 5, N'SomeID1A')
(2, N'type', 6, N'SomeID1A')
This doesn't come close to matching what you have in your original post. Can you explain using the original data from your original post please? I think this will be a cake walk but we need to understand what to do when.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 5, 2018 at 2:27 am
Try this:
WITH CTE (SomeId, SomeType, SomeDesc) AS
(
SELECT 1, 'type', 'testing'
UNION ALL
SELECT 1, 'type', 'testing'
UNION ALL
SELECT 1, 'type', 'testing'
UNION ALL
SELECT 2, 'type', 'testing2'
UNION ALL
SELECT 2, 'type', 'testing2'
UNION ALL
SELECT 4, 'type', 'testing2'
UNION ALL
SELECT 4, 'type', 'testing2'
)
INSERT INTO [dbo].[SomeCause] ([SomeID], [SomeType], [SomeThingElseID], [SomeDesc])
SELECT SomeID,
SomeType,
(SELECT ISNULL(MAX([SomeThingElseID]),0) FROM [dbo].[SomeCause] WHERE SomeID = CTE.SomeID AND SomeType = CTE.SomeType) +
ROW_NUMBER() OVER (PARTITION BY SomeID, SomeType ORDER BY (SELECT (1))),
SomeDesc
FROM CTE;
The CTE is just to create a series of data to insert.
It adds ROW_NUMBER() to the existing MAX(SomeThingElseID) for SomeID & SomeType.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply