Defining Archive tables predicate

My task is to archive records from these original tables to the archive tables as shown below; however the original tables have foreign keys define and table 2 has no date to be used in the INSERT where clause predicate -12 week. what would be your approach here, can you give me the directions and script.  thanks,
 



      Original Tables

(1)
CREATE TABLE [dbo].[JSServicesCheckListLog](
         [ServiceLogID],
      FK-[CSC],
      FK-[ServiceTypeID],
         [Comments],
         [ChecklistCompletedDate],
         [ChecklistCompletedBy],
         [CleaningDate],
         [NoShow],

(2)
CREATE TABLE [dbo].[JSServiceChecklistDetailsLog](
      PK-[ServiceDetailsLogId],
      FK-[ServiceLogID],
      FK-[ServiceWorkAreaDetailsID],
      FK-[RatingID],
         [Comments],



      Archive Tables

(1)
CREATE TABLE [Archive].[JSServicesCheckListLog](
      [JSServicesCheckListLogID],
      [ServiceLogID],
      [CSC],
      [ServiceTypeID],
      [Comments],
      [ChecklistCompletedDate],
      [ChecklistCompletedBy],
      [CleaningDate],
      [NoShow],
      [LastUpdateTimestamp],


                         
(2)

CREATE TABLE [Archive].[JSServiceChecklistDetailsLog](
      [JSServiceChecklistDetailsLogID],
      [ServiceDetailsLogId],
      [ServiceLogID],
      [ServiceWorkAreaDetailsID],
      [RatingID],
      [Comments],
      [LastUpdateTimestamp],
faith victoryAsked:
Who is Participating?
 
Kyle AbrahamsConnect With a Mentor Senior .Net DeveloperCommented:
Hi Faith,

you can just do two insert statements
insert into archive.Header
select (...) from dbo.Header where ...
;

insert into archive.Details
select (...) from dbo.details d
join dbo.Header h on d.ForeignKey = h.PrimaryKey 
where <where clause from statemetn1>

Open in new window

0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
Since table 2 is based on table one you need to find the service logs within your 12 weeks and then join on the details table for anything you find.
0
 
faith victoryAuthor Commented:
The foreign keys in the two original tables are coming from different tables.  

Do I have to create foreign keys on the Archive Tables before joining to the original tables?
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Kyle AbrahamsSenior .Net DeveloperCommented:
You shouldn't join Archive and Original

You should copy the header files from original to archive via an insert

You should copy the detail files from original to archive via an
insert into Archive.JSServiceChecklistDetailsLog select * from dbo.JSServiceChecklistDetailsLog d join dbo.JSServicesCheckListLog h on d.ServiceLogId = h.ServiceLogId where <conditions>

Open in new window

0
 
faith victoryAuthor Commented:
Hi Kyle,

Can you give me a script for this......how do you copy head first tgen details?
0
 
faith victoryAuthor Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.