Copying Rows On The Same Table and updating Only some fields (TSQL)

We will analyze different scenarios on which we need to copy or clone rows from one table to the same table, only changing the value of one know field, which in this case is the Id field, Using Microsoft SQL Server.

Supposing we are provided the OldId (original Id from were we want to copy the rows, in this case this Id can be on several rows or just one) and a NewId ( the Id we want to put on the row we are going to copy/clone)


If we know the columns and table names before hand:

The first scenario would be a table on which we know before hand the name of the table and all the fields involved, so we can easily write the following query.

DECLARE @OldId int
DECLARE @NewId int
SET @OldId =13456 -- i.e
SET @NewId =45687 -- i.e

INSERT INTO MyTable(Id,ColumnA,ColumnB,ColumnC)
SELECT @NewId,ColumnA,ColumnB,ColumnC
FROM MyTable WHERE Id=@OldId



If we know the table name, but no the column names:

In this case we are forced to use a temp table to be able to update the Id before copying the rows, this is the only way I found since the columns are unknown to us:

DECLARE @OldId int
DECLARE @NewId int
SET @OldId =13456 -- i.e
SET @NewId =45687 -- i.e

--Copy the rows we want to the temp table
SELECT * INTO #Temp FROM MyTable WHERE Id=@OldId

--Update the Id on the temp table    
UPDATE #Temp SET Id = @NewId

--Copy the rows with the new Id back to MyTable
INSERT INTO MyTable SELECT * FROM #Temp

--Drop the temp table     
if object_id(N'tempdb..#Temp', N'U') is not null  DROP TABLE #Temp
     
     
If we ignore both the table name and the columns names:

This scenario forces us to use a dynamic query to build the query string adding the table name and our first attempt would be something like this (and it will FAIL)

DECLARE @TableName varchar(32)
DECLARE @OldId int
DECLARE @NewId int

SET @TableName ='MyTable' --i.e could arrive as a parameter
SET @OldId =13456 -- i.e
SET @NewId =45687 -- i.e

SET @v_SQL = 'SELECT * INTO #Temp FROM ' + @TableName + ' WHERE Id='+  CAST(@OldId as varchar) 
EXEC(@v_SQL)
    
SET @v_SQL = 'UPDATE #Temp SET Id = '+  CAST(@NewId as varchar) 
EXEC(@v_SQL)

SET @v_SQL = 'INSERT INTO ' + @TableName + ' SELECT * FROM #Temp'
EXEC(@v_SQL)

--Drop the temp table     
SET @v_SQL = 'if object_id(N''tempdb..#Temp'', N''U'') is not null  DROP TABLE #Temp'
EXEC(@v_SQL)

     
It fails with error because the Exec() command has its own little scope (that's selfish!) so the temp table is not available for the next Exec statements.

The solution is to use a global temp table "##"  so it will be available for the scope of the rest of the Exec commands. 
This creates another problem on multi user systems, because two users could be running the process at the same time and mix their temp table or get an error. So we have to add something to the temp table name so it will be unique to our process, In this case we use the new Id to differentiate the temp table from other users that may be running the process, but you could use your own (more robust) id generator for that code if you wish.

So the final solution was the following:

DECLARE @TableName varchar(32)
DECLARE @OldId int
DECLARE @NewId int

SET @TableName ='MyTable' --i.e could arrive as a parameter
SET @OldId =13456 -- i.e
SET @NewId =45687 -- i.e

DECLARE @v_SQL varchar(1024)

SET @v_SQL = 'SELECT * INTO ##Temp'+ CAST(@NewId as varchar) +' FROM ' + @TableName + ' WHERE Id = '+  CAST(@OldId as varchar) 
EXEC(@v_SQL)
    
SET @v_SQL = 'UPDATE ##Temp'+ CAST(@NewId as varchar) +' SET Id = '+  CAST(@NewId as varchar) 
EXEC(@v_SQL)

SET @v_SQL = 'INSERT INTO ' + @TableName + ' SELECT * FROM ##Temp'+ CAST(@NewId as varchar)
EXEC(@v_SQL)
     
SET @v_SQL = 'if object_id(N''tempdb..##Temp'+ CAST(@NewId as varchar) +''', N''U'') is not null  DROP TABLE ##Temp'+ CAST(@NewId as varchar)  
EXEC(@v_SQL)
GO


Lets put that as a Store procedure:

CREATE PROCEDURE dbo.CopyWithNewId
(
  @TableName varchar(32),
  @NewId int,
  @OldId int
)
AS
  SET NOCOUNT ON
BEGIN
DECLARE @v_SQL varchar(1024)

SET @v_SQL = 'SELECT * INTO ##Temp'+ CAST(@NewId as varchar) +' FROM ' + @TableName + ' WHERE Id = '+  CAST(@OldId as varchar) 
EXEC(@v_SQL)
    
SET @v_SQL = 'UPDATE ##Temp'+ CAST(@NewId as varchar) +' SET Id = '+  CAST(@NewId as varchar) 
EXEC(@v_SQL)

SET @v_SQL = 'INSERT INTO ' + @TableName + ' SELECT * FROM ##Temp'+ CAST(@NewId as varchar)
EXEC(@v_SQL)
     
SET @v_SQL = 'if object_id(N''tempdb..##Temp'+ CAST(@NewId as varchar) +''', N''U'') is not null  DROP TABLE ##Temp'+ CAST(@NewId as varchar)  
EXEC(@v_SQL)
     
END
GO

And that, my Friend, It's all!