output identity values

  • Hi

    I have a process to rollover prior quarter data to new quarter in a table.

    For example, i have a table with (col1, col2, year, qtr) with data like ( Note: col1 is identity(1,1) )

    1,'today',2014,1

    2,'tomorrow,2014,1

    3,'friday',2014,1

    Now when i run my process, above 3 records will be rolled over new quarter 2014 Q2 and the table will be like

    1,'today',2014,1

    2,'tomorrow,2014,1

    3,'friday',2014,1

    4,'today',2014,2

    5,'tomorrow,2014,2

    6,'friday',2014,2

    Row 1 with identity 1 has rolled over to new quarter row 4 with identity 4 ( qtr fields are changed )

    Row 2 with identity 2 has rolled over to new quarter row 5 with identity 5. Same with last row as well.

    Here, i have another table called "ident_map" with columns like (old identity, new identity ) and during rollover i am supposed to load ident_map table with old and new identity. So after rollover is complete, ident_map table should look like

    1,4

    2,5

    3,6

    I know using output clasue i can capture the new identity values. 4,5,6 in this case. But is there any way to capture both old identity and new identity during rollover so that i can load the ident_map table with old and new identity.

    I appreciate your responses.

  • This looks a lot like the question from yesterday. http://www.sqlservercentral.com/Forums/Topic1577193-3077-1.aspx

    We are however starting to see some details about what you want to do. Perhaps if you can post the tables structures, some sample data as inserts, the code you are using as rollover so we have an idea of your process we can help.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • True. This is related to the post you have pointed. But the old post do not have much details and it created a lot of confusion who wanted to help me out. To make it easy, i have created this new post with table structures, sample inserts.

    The code for rollover is simple.

    Insert into table (col2, year, qtr)

    select col2, year, qtr+1 as qtr

    from table.

    Table structures, sample data, before and after data are already provided in this post.

    Let me know if you need more details.

  • vasuarjula (6/5/2014)


    True. This is related to the post you have pointed. But the old post do not have much details and it created a lot of confusion who wanted to help me out. To make it easy, i have created this new post with table structures, sample inserts.

    The code for rollover is simple.

    Insert into table (col2, year, qtr)

    select col2, year, qtr+1 as qtr

    from table.

    Table structures, sample data, before and after data are already provided in this post.

    Let me know if you need more details.

    Those are not table structures, they are column names. The idea here is to post it in a consumable format so we can copy and paste in our db and run your script so we have tables and data to work with. This kind of thing provides many benefits. We don't have to guess on datatypes, we know if you have an identity defined on a column, primary keys etc... What you get in return is tested and accurate sql. Please take a few minutes and read the first article in my signature for best practices when posting questions.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Please find more details with clear structures and sample data.

    -- Table test

    create table test

    (col1 int identity(1,1),

    col2 varchar(20),

    year int,

    qtr int

    )

    -- Table ident_map

    create table ident_map

    (old_ident int,

    new_ident int

    )

    -- Insert sample records into table test

    insert into test(col2, year,qtr)

    values('today',2014,1);

    insert into test(col2, year,qtr)

    values('tomorrow',2014,1);

    insert into test(col2, year,qtr)

    values('friday',2014,1);

    -- Process to execute rollover

    Insert into test(col2, year,qtr)

    select col2, year, qtr+1 as qtr

    from test

    When i run above process above 3 records will be rolled over new quarter 2014 Q2 and the data in table will be like

    1,'today',2014,1

    2,'tomorrow,2014,1

    3,'friday',2014,1

    4,'today',2014,2

    5,'tomorrow,2014,2

    6,'friday',2014,2

    Row 1 with identity 1 has rolled over to new quarter row 4 with identity 4 ( qtr fields are changed )

    Row 2 with identity 2 has rolled over to new quarter row 5 with identity 5. Same with last row as well.

    Apart from rolling over, data needs to be inserted into ident_temp table with old and new identity values. Data in ident_temp should look like

    1,4

    2,5

    3,6

    I know using output clasue i can capture the new identity values. 4,5,6 in this case. But is there any way to capture both old identity and new identity during rollover so that i can load the ident_map table with old and new identity.

  • OK I finally understand enough of what you are doing here. There is no way from the code that you have posted to capture the current identity value. It is not in your select statement so it will not be available in OUTPUT.

    Are you able to modify the tables? The easiest way by FAR would be to add a new column to hold the OldID.

    Something like this.

    alter table test add OldID int null

    -- Process to execute rollover

    Insert into test(col2, year,qtr, OldID)

    select col2, year, qtr+1 as qtr, col1

    from test

    select * from TEST

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I agree with Sean that the easiest solution for you is to add the additional column to store the old ID.

    The OUTPUT clause can only access columns from the table that is actually being changed. Sample code often shows it being used to record old values versus new values, but in those cases the columns in the target table have actually changed values due to an UPDATE. For purposes of the OUTPUT clause, an UPDATE is equivalent to a DELETE of a row followed by an INSERT of a new row. That means you have access to both the Inserted and Deleted virtual tables like so:

    update test

    set col2 = 'Never'

    output deleted.col2 as oldCol2, inserted.col2 as newCol2

    where col1 = 1

    But you aren't doing an update, you're doing an insert. An insert creates no deleted virtual table from which OUTPUT can pull the old value. There are only new values in the Inserted virtual table.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Not a problem.

    I will try to get a work around or request to add a new field to table.

    Thanks very much for your responses. I appreciate it.

  • An even simpler solution is to grab the output into a table variable which then can be used to get the old-new values for the identity map table.

    😎

    DECLARE @INSVAL TABLE

    (

    col1 int NULL

    ,col2 varchar(20) NULL

    ,[year] int NULL

    ,qtr int NULL

    );

    -- Process to execute rollover

    Insert into dbo.test(col2, year,qtr)

    OUTPUT inserted.* INTO @INSVAL

    select col2, year, qtr+1 as qtr

    from test;

    INSERT INTO dbo.ident_map

    (

    old_ident

    ,new_ident

    )

    SELECT

    T.col1 AS old_ident

    ,I.col1 AS new_ident

    FROM @INSVAL I

    INNER JOIN dbo.test T

    ON I.[year] = T.[year]

    AND I.col2 = T.col2

    AND I.qtr = T.qtr + 1

  • That will work but it's pricey in performance compared to just adding the column.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • The Dixie Flatline (6/6/2014)


    That will work but it's pricey in performance compared to just adding the column.

    Agree on the costly part but exactly the same work then would be needed on the other side of the roll over.

    😎

  • Eirikur Eiriksson (6/6/2014)


    The Dixie Flatline (6/6/2014)


    That will work but it's pricey in performance compared to just adding the column.

    Agree on the costly part but exactly the same work then would be needed on the other side of the roll over.

    😎

    I'm not following you. Could you please elaborate on the "exactly the same work" you are referring to?

    The work I was referring to was the cost of storing output into a temporary variable and then doing a JOIN back to the original data to do the final insert. With the added column, the code can simply OUTPUT the inserted.oldcol1 and inserted.col1 columns into the ident_map table.

    By the way, thank you vasuarjula for setting up the code and sample data. It makes it so much easier to understand your objective and to test solutions when you provide that with your questions. Thanks again.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • The Dixie Flatline (6/6/2014)


    Eirikur Eiriksson (6/6/2014)


    The Dixie Flatline (6/6/2014)


    That will work but it's pricey in performance compared to just adding the column.

    Agree on the costly part but exactly the same work then would be needed on the other side of the roll over.

    😎

    I'm not following you. Could you please elaborate on the "exactly the same work" you are referring to?

    The work I was referring to was the cost of storing output into a temporary variable and then doing a JOIN back to the original data to do the final insert. With the added column, the code can simply OUTPUT the inserted.oldcol1 and inserted.col1 columns into the ident_map table.

    By the way, thank you vasuarjula for setting up the code and sample data. It makes it so much easier to understand your objective and to test solutions when you provide that with your questions. Thanks again.

    Bear with me if I'm on a different planet:alien:

    The table has an Identity column and a OldIdentity column, a row is inserted, a new Identity is generated, but where does the OldIdentity come from. It is not in the scope of inserted/deleted is it?

    It has to be search on (matching conditions) which looks to me as "exactly the same work":w00t:

    😎

  • Sorry to be so late in responding. 🙂

    Take a look at the following code and maybe it will clarify the situation.

    create table #test

    (col1 int identity(1,1),

    col2 varchar(20),

    year int,

    qtr int,

    oldCol1 int-- this is the new column we want to add

    )

    -- Table ident_map

    create table #ident_map

    (old_ident int,

    new_ident int

    )

    -- Insert sample records into table test

    insert into #test(col2, year,qtr)

    values('today',2014,1);

    insert into #test(col2, year,qtr)

    values('tomorrow',2014,1);

    insert into #test(col2, year,qtr)

    values('friday',2014,1);

    select * from #test

    select * from #ident_map

    select 'A MIRACLE HAPPENS !!' as [And then...]

    -- Process to execute rollover

    insert into #test(col2, year,qtr, oldCol1)

    output inserted.oldCol1, inserted.col1

    into #ident_map

    select col2, year, qtr+1 as qtr, COL1-- rollover now includes inserting original value of col1 into oldcol1

    from #test

    select * from #Test

    select * from #ident_map

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Thanks for the explanation:-)

    There is not much difference in the actual work though, the matching "old" values must be selected from the existing set, based on which ever matching criteria. More like a quick hand than magic.

    😎

Viewing 15 posts - 1 through 15 (of 27 total)

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