COPY One Table Row in SQL

Date Published: 18 January 2007

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 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



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)


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]

Steve Smith

About Ardalis

Software Architect

Steve is an experienced software architect and trainer, focusing on code quality and Domain-Driven Design with .NET.