COPY One Table Row in SQL
I have a table with a bunch of columns in it that I wanted to be able to duplicate so that I could just change a couple of columns and not have to re-enter all of the columns and their values. I did some searching and the closest I came to what I wanted was this. So, working with Gregg on IM we came up with a more flexible solution that doesn’t require typing in the 20–some column names one might have (twice):
ALTER
PROCEDURE [dbo].[aa_widget_Copy] (
@widget_id
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 = 'aa_widget'
and column_name <> 'widget_id'
declare @query varchar(8000)
set @query = ''
select @query = 'INSERT aa_widget (' + @columns + ') SELECT ' + @columns + ' FROM aa_widget WHERE widget_id = ' + convert(varchar(10), @widget_id)
exec (@query)
END
This could be modified to be even more flexible by testing the source table to see which columns (if any) were IDENTITY columns, and exclude them in the “and column_name <> … clause. Once this was done, the table name could be a parameter and this could be a general purpose CopyTableRow() method. As it stands now, it’s good enough that I can use cut-and-paste and just change the table name and key name and reuse it if I need it again.