Generate and run "insert into ... select ... from" statement for a table.

spDuplicateEntryWithIdentityValue.sql

CREATE PROCEDURE [dbo].[spDuplicateEntryWithIdentityValue]
    @TableName NVARCHAR(128),
    @IdentityColumnName NVARCHAR(128), -- the name of auto-increasing column
    @CurrentIdentityValue INT,
    @NewIdentityValue INT OUTPUT
AS
DECLARE @ColumnNames NVARCHAR(MAX)

SELECT @ColumnNames = STRING_AGG(N'[' + COLUMN_NAME + N']', N', ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
    AND COLUMN_NAME <> @IdentityColumnName

DECLARE @InsertStmt NVARCHAR(MAX)

SET @InsertStmt = N'INSERT INTO ' + @TableName + N'
    (' + @ColumnNames + N')
SELECT ' + @ColumnNames + N'
FROM ' + @TableName + N'
WHERE ' + @IdentityColumnName + N' = @Id

SET @NewId = SCOPE_IDENTITY()'

PRINT @insertstmt

EXEC sp_executesql @InsertStmt, N'@Id INT, @NewId INT OUTPUT', @Id = @CurrentIdentityValue, @NewId = @NewIdentityValue OUTPUT
GO

/* Example:
DECLARE @MyTableId INT

EXEC spDuplicateEntryWithIdentityValue @TableName = N'MyTable', @IdentityColumnName = N'MyTableId', @CurrentIdentityValue = 123
    , @NewIdentityValue = @MyTableId OUTPUT
*/