Identity function with SELECT INTO - Need to change seed to dynamically get the last existing value-CodePudding

I have a table which has two columns as key (i_number c_id), and I need to generate a new table from its values, but generating a new i_number so that the new table's first i_number for a certain c_id will be max(i_number) 1 for that c_id in the original table. Also, I will generate a new c_id for the second table, but this is not a problem. I post it as image as the data is provided in the fiddle at the end of the question. So, this is the original table:

Given a certain c_id, let's say 001, I will be provided with a new c_id (let's say, 901), and the desired result its a new table with rows from the original table having c_id = 001, but generating a new i_number (in this case, it would be 3 1 = 4):

I'm trying to accomplish this with a SELECT... INTO clause and using the Identity function, but it's not possible to use Identity providing a non-fixed value, neither a parameter...

SELECT 
    IDENTITY(INT, 
        (SELECT MAX(i_number)   1 FROM table_fake WHERE c_id = '001'),
        1) AS i_number,
    '901' AS c_ooo, 
    m_value
INTO [table_fake_new]
FROM table_fake 
WHERE c_id = '001'

And I'd rather prefer not to use sp_executesql to do this. Any suggestion will be apreciated

Fiddle here

CodePudding user response：

I believe this is what you are trying to achieve. I just used window functions for MAX and ROW_NUMBER() to get the incrementing values:

SELECT MAX(i_number) OVER(ORDER BY (SELECT NULL)) 
              ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS i_number  
,'901' AS c_ooo, 
m_value
INTO table_fake_new
FROM table_fake WHERE c_id = '001'

CodePudding user response：

Perhaps this help you.

ROW_NUMBER()

https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-ver16