COPY One Table Row in SQL
Date Published: 18 January 2007
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.
Steve is an experienced software architect and trainer, focusing currently on ASP.NET Core and Domain-Driven Design.