More sql fun ...
I need to copy a row in a table, including a primary key, and it can get to be tedious with a large number of columns. I have to deal with one that has over 100 of them in the present case. This is a ridiculous amount, sure, but I am stuck with it as the dependency list on this table throughout the project is gawdawful. Worse, the situation has not entered a steady-state. There could very well be additional columns added to the table sometime in the future. So, as you might imagine, I am disinclined to enumerate every column name twice for the insert table and then worry about maintaining it .... really, screw that.
Google To The Rescue
So, a quick search of "copy row in sql" gets me to Steven Smith's "COPY One Table Row in SQL". After reading through the article and then the comments, I know I had found an answer.
The first comment says that "This goes against any concept of database normalization, and is likely to be extremely unportable. Why would you need to do this anyways? Unless you were writing a tool to administer one single type of database?" And, he's right, Steven's solution is bound to a specified table and primary key name. However, like he mentioned at the end, it can be modified to be more flexible. I have done as much. So, for my reference and your potential utility, generic copying of a table row with primary key.
CREATE PROCEDURE [dbo].[pr_copyRow] (@targetTable varchar(255),@targetKey varchar(255), @targetID int)
declare @columns varchar(5000)
select @columns = case when @columns is null then column_name else @columns + ',' + column_name end
where table_name = @targetTable and column_name <> @targetKey
declare @query varchar(8000)
set @query = ''
select @query = 'INSERT ' +@targetTable+ ' (' + @columns + ') SELECT ' + @columns + ' FROM ' +@targetTable+ ' WHERE '+@targetKey +' = ' + convert(varchar(10), @targetID)
Nothing magic here. Compared to the original, I have added two parameters; allowing you to specify the target table and identify the identity column. And, just because I am cool (and because it didn't make sense in the context of my changes), I renamed the original parameter, identifying which row to copy, to targetKey.
It works for me, hopefully it will for you too.
If you want to receive the new identity value as a return, the following ought to do the trick