Copy rows and increase Version-Column without Cursor / Loop-CodePudding

I have a table with this structure:

ID    Version    Content 
-------------------------------------------------------
1     1          sometext
1     2          anothertext
1     3          someverydifferenttext

So all rows have the same ID but a different Version. I want to copy these rows (Insert Statement) and increase the Version-Column to the next free number.

Expected Result:

ID    Version    Content 
-------------------------------------------------------
1     1          sometext
1     2          anothertext
1     3          someverydifferenttext
1     4          sometext
1     5          anothertext
1     6          someverydifferenttext

Is there way to do this in a single Select-Insert Statement?

I tried with...

Insert Into MyTable
SELECT ID
, MAX([Version])OVER(PARTITION BY ID ORDER BY [Version] DESC)   1
,Content
FROM MyTable

But this does not work because MAX() would have to be evaluated again after each individual insert of a row. And the only option I currently see is a loop.

I use T-SQL.

CodePudding user response：

Seems you could achieve this with ROW_NUMBER and a windowed MAX:

INSERT INTO dbo.YourTable
SELECT ID,
       ROW_NUMBER() OVER (ORDER BY Version)   MAX(Version) OVER () AS Version,
       Content
FROM dbo.YourTable WITH (UPDLOCK, HOLDLOCK);

db<>fiddle