How can I copy data from one set of tables into another set of tables on a regular basis

In a sql server 2008 database, I have a set of tables that I want to save the history of on a regular basis. To do this I created another set of tables to store this history. Basically, the database structure looks like this:

+---------------+                +---------------+
| TableA        |                | TableAHistory |
+---------------+                +---------------+
| Key           |<--+            | Key           |<----+
| DataA         |   |            | Date          |     |
|               |   |            | DataA         |     |
+---------------+   |            +---------------+     |
                    |                                  |
+---------------+   |            +-----------------+   |
| TableB        |   |            | TableBHistory   |   |
+---------------+   |            +-----------------+   |
| Key           |   |            | Key             |   |
| TableAKey     |---+            | TableAHistoryKey|---+
| DataB         |                | DataB           |
+---------------+                +-----------------+    

With this structure, I'm having trouble creating a stored procedure that can copy the current data into the history tables. I can easily create the insert statement that copies the data from TableA into TableAHistory, but copying TableB's data is causing me problems. When copying data into TableBHistory, I don't see how I can find what the TableAHistoryKey should be. Here is what the insert statements look like now:

INSERT      TableAHistory([Date], DataA)
SELECT      CONVERT(DATE, DATEADD(DAY, 1 - DAY(GETDATE()), GETDATE())),
            DataA
FROM        TableA

INSERT      TableBHistory([TableAHistoryKey], DataB)
SELECT      ??? AS TableAHistoryKey,
            DataB
FROM        TableB

The only thing I can think of is to add a field on the history tables to temporarily store the corresponding table's key. I could then join TableA, TableAHistory and TableB in the second insert statement, getting me access to the correct TableAHistoryKey. I would rather not do this, since this added column would only be used for copying the data.

Is there another (common) way to do this?

Edit: Just to add a little clarity, the Key in TableAHistory is generated and not the same as the Key in TableA. The same goes for TableBHistory and TableB.

Edit: Currently, this history only needs to be saved on a monthly basis and we have a convention in place to store a "month/year" as a date on the first day of the month. Hence the weird CONVERT(DATE, DATEADD(DAY, 1 - DAY(GETDATE()), GETDATE())). Probably not the best way to do this开发者_如何学编程, but it works.

---

I would have your archive tables use the same schema as prod, but introduce an archiveID as part of the PK in each of the tables, including tableBHistory.

Then it should be easy to restore rows from a particular archive set.

---

I would have thought that for a history table you would need a date the data was copied. If this is the case then, instead of the (presumably generated) key for the two history tables, copy the original key, and then in the history tables use a compound primary key of key plus date.