I have two tables in SQL Server 2008 R2. I have a regular table (tblData) with a timestamp field and a corresponding archive table (tblArchive). tblArchive is identical to tblData except it has a unique identity field (RecordID) at the beginning. Periodically I need to "archive" selected rows by inserting a new row into the archive table. I would like to use the following syntax...
INSERT INTO tblArchive
SELECT tblData.*
FROM tblData
WHERE tblData.ArchiveDate=#1/13/2013#
However, SQL Server won't let me copy a timestamp field into a new field. It tells me I need to name all the fields to INSERT excluding the timestamp field or assign a default value to timestamp. I don't want to specify all the fields because anytime I add a new field, I have to update the SQL statement. I could not find a way to assign a default value to the timestamp field.
Can anyone suggest a way I can structure the SELECT statement so I don't have to name all the columns explicitly?
Thanks, Eddie