How can I avoid a timestamp insert error when copying data from one table to another?

Question

I'm trying to cull the data in a list of tables (~30) based on a primary key.

My approach has been to: 1.Create an intermediate table & load it with the desired data for each table
2.Truncate the original table
3.Insert the data from the intermediate table back into the original table.

Here's the code I'm using thus far:

declare @table nvarchar(max) 
open tab
fetch next from tab into @table
while(@@FETCH_STATUS = 0)
       begin
              print @table
             exec ('select * into ' +@table+'_intermediate from '+@table+' where P_ID in( select P_ID from pc_table )')
             exec ('truncate table '+@table)
             exec ('insert into '+@table+' select * from '+@table+'_intermediate')
             exec ('drop table '+@table+'_intermediate') 
            fetch next from tab into @table
       end
close tab
deallocate tab

I'm running into an error:

Cannot insert an explicit value into a timestamp column. 
Use INSERT with a column list to exclude the timestamp column, 
or insert a DEFAULT into the timestamp column.

So, that error is telling me that I can't insert anything into a timestamp column.

In order to avoid selecting the timestamp, I need to avoid selecting it (i.e. using select *).

Is there a simple way of selecting all columns except of type timestamp, or will I need to go into the information schema and build a dynamic select statement for each table?

(or the implicit question, is there a better way of doing what I'm trying to do?)

Thanks

Answer 1

The short answer is that you need to put 'null' in any spot where there's a timestamp column.

I made this little script to create a list of the columns, so that I put that list into the DML statement :

         declare @sel_statement nvarchar(max)=''
         declare @col nvarchar(100) =''
         declare @num_rows int =0
         declare @dat_type nvarchar(30)

         declare cols cursor for
         select column_name, data_type 
         from information_schema.COLUMNS
         where TABLE_NAME = @table  --uses table fetched from tab cursor

         open cols

         fetch next from cols into @col, @dat_type
         while(@@FETCH_STATUS = 0)
                begin
                set @num_rows +=1
                if @dat_type = 'timestamp'
                     set @sel_statement += 'null'
                else  
                      set @sel_statement += @col 
                fetch next from cols into @col, @dat_type
                if @@FETCH_STATUS=0
                      set @sel_statement += ','
                end
         close cols
         deallocate cols 

It's not the prettiest thing ever, but it worked.

Hopefully this can give someone else a hand if they run into this problem.