I have two tables that are ALMOST identical; one is primary data, the other is archive.

The difference between the two:
1- Primary has PROJ_ID that is an identity; Archive has PROJ_ID that is not.
2- Archive has one extra column (archive_date) that Primary does not.

I'd like to copy data from one table to the other. Unfortunately, I can't use SELECT INTO, because the table already exists; and I can't use INSERT INTO tablename SELECT * because of the one column difference.

I've been trying for an hour. Is there a way to copy the data? I've tried declaring a variable, selecting the column names of Primary into it, and using that as the column names for the insert, but I keep getting error messages.

Any suggestions?