Identity Insert

  • Hi,
    I am trying to do a identity insert on table [CnP].[WorkComment_test.
    DDL

    CREATE TABLE [CnP].[WorkComment_test](
        [WorkCommentId1] [int] NOT NULL,
        [WorkId] [int] NOT NULL,
        [Comments] [nvarchar](max) NOT NULL,
        [IsExternalViewable] [bit] NOT NULL,
        [IsAlert] [bit] NOT NULL,
        [CreatedBy] [nvarchar](256) NOT NULL,
        [CreatedDate] [datetime] NOT NULL,
        [UpdatedBy] [nvarchar](256) NOT NULL,
        [UpdatedDate] [datetime] NOT NULL,
        [RowVersion] [timestamp] NOT NULL,
        [IsDeleted] [bit] NOT NULL,
        [RegistrationId] [int] NULL,
    CONSTRAINT [PK_WorkComment1] PRIMARY KEY CLUSTERED
    (
        [WorkCommentId1] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    is identity & identity increment is not turn on this table. So I trying to get the current highest value and +1 to it for the next row.
    I have tried using the following while loop but the first column @newid is just the same number for each row instead of increment by 1.


    DECLARE @startInt INT
    DECLARE @ENDInt INT
    DECLARE @NEWID INT
    SET @startInt = 1
    SET @ENDINT = (SELECT DISTINCT COUNT(WINFkey) from apradb.dbo.winf w WHERE last_paid in    ('L17','L18','L19','L20','L21','L22','L23','L24','L25','L26','L27','L28','L29','L30','L31','L32'
                                                                                ,'L33','L34','L35','L35B','L36A','L36B','L36C','L37A'))

    WHILE (@startInt <= @ENDInt)

    BEGIN

        SET @NewID = (SELECT MAX([WorkCommentId]) FROM [schemap].[WorkComment])

        --INSERT INTO [schmeap].[WorkComment_test] ([WorkCommentId1],[WorkId],[Comments],[IsExternalViewable],[IsAlert],[CreatedBy],[CreatedDate],[UpdatedBy],[UpdatedDate],[IsDeleted],[RegistrationId])
     
       SELECT @newID
                ,SUBSTRING(w.WINFkey,3,LEN(w.WINFKEY))
        ,'Legacy Migration Information'
        ,1
        ,1
        ,'domain\testuser'
        ,GETDATE()
        ,SYSTEM_USER
        ,GETDATE()
        ,0
        ,0         
            FROM db.schemaI.winf w    
            WHERE last_paid in
            (
            'L17'
            ,'L18'
            ,'L19'
            ,'L20'
            ,'L21'
            ,'L22'
            ,'L23'
            ,'L24'
            ,'L25'
            ,'L26'
            ,'L27'
            ,'L28'
            ,'L29'
            ,'L30'
            ,'L31'
            ,'L32'
            ,'L33'
            ,'L34'
            ,'L35'
            ,'L35B'
            ,'L36A'
            ,'L36B'
            ,'L36C'
            ,'L37A'
            )

        SET @NEWID = @NewID +1
        Continue;
    END

    Result
    16880979    01118589 Legacy Migration Information
    16880979    01118822 Legacy Migration Information

    Desired
    16880979    01118589 Legacy Migration Information
    16880980    01118822 Legacy Migration Information

    Thanks

  • Firstly, an explanation of what what you're doing isn't working. Inside your While loop your returning your full dataset, in this case every row that your going to insert, every time you loop through. This means that each time the process runs @NewID is going to have the same value across every row, then you increment the value by one. Then you return the whole data set (again), and every row has the new value of @NewID.

    Using a While Loop or Cursor really isn't the idea here. When using SQL you need to stop thinking with iterative mind set, and think about it in a whole. I apologise that I can't remember who said this (probably Jeff Moden, but whoever it is, please feel free to cite them) but a good saying is "Don't think about what you want to do to a row; think about what you want to do with a column". Your thought process was you need to take the first row and set it the highest value plus one of the current ID. Then go to the nxt row and add one, then the next row and add one. Instead, a dataset solution would be to set the ID to the Row Number in that dataset, and add the highest value from your table to it.

    I've made a small assumption, as theres no sample data, but this should get you the answer you want:
    INSERT INTO [schmeap].[WorkComment_test] ([WorkCommentId1],[WorkId],[Comments],[IsExternalViewable],[IsAlert],[CreatedBy],[CreatedDate],[UpdatedBy],[UpdatedDate],[IsDeleted],[RegistrationId])
    SELECT ROW_NUMBER() OVER (ORDER BY SUBSTRING(w.WINFkey,3,LEN(w.WINFKEY)) + (SELECT MAX([WorkCommentId]) FROM [schemap].[WorkComment]) AS ID, --Total guess what the ORDER should be.
           --The above uses the ROW_NUMBER function, to give each row its own "ID". I then use your SQL to get the highest ID value from your existing table and add that it it.
           --So, the first row would be allocated ROW_NUMBER. If your existing max ID is 132465, then that equates to 132465 + 1 = 132466.
           SUBSTRING(w.WINFkey,3,LEN(w.WINFKEY)),
           'Legacy Migration Information',
           1,
           1,
           'domain\testuser',
           GETDATE(),
           SYSTEM_USER,
           GETDATE(),
           0,
           0  
    FROM db.schemaI.winf w 
    WHERE last_paid in ( 'L17','L18','L19','L20','L21','L22','L23','L24','L25','L26','L27','L28','L29','L30','L31','L32','L33','L34','L35','L35B','L36A','L36B','L36C','L37A');

    If you have any questions, please ask. The important part is you understand what the above SQL is doing, so you can learn from it. 🙂

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Thanks for the reply.
    Unfortunately your row_Number() returns the same as when I tried to use it, which is why I switched to trying using a while loop. The Row_Number() is only returning the row 1,2,3,4,5, when I need the max(workcommentid) + 1 each time through the loop.

  • I have written a Cursor, but the same issue is happening, columnid is not increasing by 1 each time through the loop. Any suggestions?

    DECLARE @ColumnID INT
    SET @ColumnID = (SELECT MAX([WorkCommentId]) FROM [abc].[WorkComment]) --CurrentID or Row Number
    DECLARE @ID INT --Number of Rows to Process
    DECLARE IDCursor CURSOR FOR
    SELECT DISTINCT COUNT(WINFkey) from dbdb.dbo.winf w WHERE last_paid in    ('L17','L18','L19','L20','L21','L22','L23','L24','L25','L26','L27','L28','L29','L30','L31','L32'
                                                                                ,'L33','L34','L35','L35B','L36A','L36B','L36C','L37A')
    OPEN IDCursor

    FETCH NEXT FROM IDCursor INTO @ID

    WHILE (@@FETCH_STATUS = 0)
        BEGIN
            --INSERT INTO [PPP].[WorkComment_test] ([WorkCommentId1],[WorkId],[Comments],[IsExternalViewable],[IsAlert],[CreatedBy],[CreatedDate],[UpdatedBy],[UpdatedDate],[IsDeleted],[RegistrationId])        
            SET @ColumnID = 1 + @ColumnID
      
            SELECT @ColumnID as 'ColumnId'
                ,SUBSTRING(w.WINFkey,3,LEN(w.WINFKEY))
        ,'Legacy Migration Information'
        ,1
        ,1
        ,'domain\testuser'
        ,GETDATE()
        ,SYSTEM_USER
        ,GETDATE()
        ,0
        ,0         
            FROM dbdb.dbo.winf w    
            WHERE last_paid in
            (
            'L17','L18','L19','L20','L21','L22','L23','L24','L25','L26','L27','L28','L29','L30','L31','L32','L33','L34','L35','L35B','L36A','L36B','L36C','L37A')        
        
        
        FETCH NEXT FROM IDCursor INTO @ID        
        
        END
    CLOSE IDCursor
    DEALLOCATE IDCursor

  • ringovski - Thursday, July 27, 2017 5:59 PM

    Thanks for the reply.
    Unfortunately your row_Number() returns the same as when I tried to use it, which is why I switched to trying using a while loop. The Row_Number() is only returning the row 1,2,3,4,5, when I need the max(workcommentid) + 1 each time through the loop.

    This implies that the problem is your subquery. That's not something i can test, as i can't see your data. Can you supply sample DLM, as we can then test our answers.

    P.S. Please don't use a cursor,., They are slow and not required for this operation.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • if you can't use IDENTITY, can you use a SEQUENCE?  That would allow you to get new values after each run, and you not having to worry about querying MAX(WorkCommentID) value each time.  Here's an article that describes their usage:
    https://docs.microsoft.com/en-us/sql/relational-databases/sequence-numbers/sequence-numbers

    initial setup would be like this (unfortunately you can't just pass in @StartID to the CREATE SEQUENCE statement) :
    DECLARE @StartID int, @sqlcmd nvarchar(500);
    SELECT @StartID = MAX(WorkCommentID) + 1 FROM [schemap].[WorkComment];

    SET @sqlcmd = N'CREATE SEQUENCE dbo.WorkCommentSequence AS int START WITH ' + CAST(@StartID AS nvarchar(10)) + N' INCREMENT BY 1;';
    EXEC sp_executesql @sqlcmd;

    Then in Thom's code, instead of:
    SELECT ROW_NUMBER() OVER (ORDER BY SUBSTRING(w.WINFkey,3,LEN(w.WINFKEY)) + (SELECT MAX([WorkCommentId]) FROM [schemap].[WorkComment]) AS ID,
    you can use:
    SELECT NEXT VALUE FOR dbo.WorkCommentSequence AS ID,

  • MAX([WorkCommentId]) must be not in SELECT part, but rather in FROM section, as a derived table.

    Subqueries within SELECT are executed for each returned row.

    INSERT INTO [schmeap].[WorkComment_test] ([WorkCommentId1],[WorkId],[Comments],[IsExternalViewable],[IsAlert],[CreatedBy],[CreatedDate],[UpdatedBy],[UpdatedDate],[IsDeleted],[RegistrationId])

    SELECT ROW_NUMBER() OVER (ORDER BY SUBSTRING(w.WINFkey,3,LEN(w.WINFKEY)) + isnull(MaxID, 0), --Total guess what the ORDER should be.

    --The above uses the ROW_NUMBER function, to give each row its own "ID". I then use your SQL to get the highest ID value from your existing table and add that it it.

    --So, the first row would be allocated ROW_NUMBER. If your existing max ID is 132465, then that equates to 132465 + 1 = 132466.

    SUBSTRING(w.WINFkey,3,LEN(w.WINFKEY)),

    'Legacy Migration Information',

    1,

    1,

    'domain\testuser',

    GETDATE(),

    SYSTEM_USER,

    GETDATE(),

    0,

    0

    FROM db.schemaI.winf w

    Left join (SELECT MAX([WorkCommentId]) MaxID FROM [schemap].[WorkComment]) AS M on WINFkey > ''

    WHERE last_paid in ( 'L17','L18','L19','L20','L21','L22','L23','L24','L25','L26','L27','L28','L29','L30','L31','L32','L33','L34','L35','L35B','L36A','L36B','L36C','L37A');

    _____________
    Code for TallyGenerator

Viewing 7 posts - 1 through 6 (of 6 total)

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