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 wasthis. So, working withGreggon 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.

[categories: SQL]