copy entire row (without knowing field names)
- by Todd Webb
Using SQL Server 2008, I would like to duplicate one row of a table, without knowing the field names.  My key issue: as the table grows and mutates over time, I would like this copy-script to keep working, without me having to write out 30+ ever-changing fields, ugh.
Also at issue, of course, is IDENTITY fields cannot be copied.
My code below does work, but I wonder if there's a more appropriate method than my thrown-together text string SQL statement?
So thank you in advance.  Here's my (yes, working) code - I welcome suggestions on improving it.
Todd
alter procedure spEventCopy
   @EventID int
as
begin
   -- VARS...
   declare @SQL varchar(8000)
   -- LIST ALL FIELDS (*EXCLUDE* IDENTITY FIELDS).
   -- USE [BRACKETS] FOR ANY SILLY FIELD-NAMES WITH SPACES, OR RESERVED WORDS...
   select @SQL = coalesce(@SQL + ', ', '') + '[' + column_name + ']'
   from INFORMATION_SCHEMA.COLUMNS
   where TABLE_NAME = 'EventsTable'
     and COLUMNPROPERTY(OBJECT_ID('EventsTable'), COLUMN_NAME, 'IsIdentity') = 0
   -- FINISH SQL COPY STATEMENT...
   set @SQL = 'insert into EventsTable '
            + ' select ' + @SQL
            + ' from EventsTable '
            + ' where EventID = ' + ltrim(str(@EventID))
   -- COPY ROW...
   exec(@SQL)
   -- REMEMBER NEW ID...
   set @EventID = @@IDENTITY
   -- (do other stuff here)
   -- DONE...
   -- JUST FOR KICKS, RETURN THE SQL STATEMENT SO I CAN REVIEW IT IF I WISH...
   select EventID = @EventID, SQL = @SQL
end