Inserting recrods fromt the same table with an incremental value on every insert

  • 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

  • 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?

  • 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

  • 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')

  • 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;

  • 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.

  • Have you considered just using an identity column?  (i.e. an auto-incrementing integer?)

    https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-2017

    ----------------------------------------------------------------------------------
    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?

  • I wish i could but this is what i have to work with.

  • SQLTestUser - Wednesday, October 3, 2018 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')

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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