Stored Procedure To Copy Arbitrary Table Row

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)

AS
BEGIN

declare @columns varchar(5000)
select @columns = case when @columns is null then column_name else @columns + ',' + column_name end
from information_schema.columns
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)

exec (@query)

END

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.

[ADDENDUM] If you want to receive the new identity value as a return, the following ought to do the trick



CREATE PROCEDURE [dbo].[pr_copyRow] (@targetTable varchar(255),@targetKey varchar(255), @targetID int)

AS
BEGIN

declare @columns varchar(5000)
select @columns = case when @columns is null then column_name else @columns + ',' + column_name end
from information_schema.columns
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)

declare @returnQuery varchar(8000)
set @returnQuery = ''
select @returnQuery = 'select @@identity as ' + @targetKey
exec(@query)
exec(@returnQuery)
END
Might not be the cleanest, but it does the trick.

[ADDENDUM The Next (21 January 2009)] Ok, so a bit of a brain fart when going for the output previously. And to bring it back to one of the core themes of the site, it was Coldfusion that caused me to realize that I didn't quite produce what I wanted.

Since I was using the <cfstoredproc>, the expectation is that my result should come as an output variable and not as a result set - Doh! So, the new and improved, and actually simpler version, using an output parameter:


ALTER PROCEDURE [dbo].[pr_copyRow] (
@targetTable varchar(255),
@targetKey varchar(255),
@targetID int,
@newID int OUTPUT)

AS
BEGIN

declare @columns varchar(5000)
select @columns = case when @columns is null then column_name else @columns + ',' + column_name end
from information_schema.columns
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)


exec(@query)
select @newID = @@identity
END

And the Coldfusion usage would go something like:


<cfstoredproc procedure="pr_copyRow" datasource="#application.datasource#">
<cfprocparam cfsqltype="cf_sql_varchar" value="myTable">
<cfprocparam cfsqltype="cf_sql_varchar" value="myKey">
<cfprocparam cfsqltype="cf_sql_numeric" value="#variableContainingRowOfInterest#">
<cfprocparam cfsqltype="cf_sql_numeric" type="out" variable="newRowKeyValue">        
</cfstoredproc>

<cfoutput>#newRowKeyValue#</cfoutput>

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
BlogCFC was created by Raymond Camden. This blog is running version 5.9.1.001. Contact Blog Owner