Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Transact-SQL (2000)  Archiving via SELECT INTO

Author  Topic  raoool Starting Member 3 Posts Posted - 2006-03-27 : 18:34:11 Have two tables a'la Jobs ---- ID Job Stuff MoreJobStuff JobHistory ---------- ID JID (key to Jobs.ID) Event Event Date Want to archive all fields from both tables (move and delete from Jobs and JobEvents) to identically sructured JobsArc and JobHistoryArc tables where specific Event is more than two weeks old. e.g. all Jobs have a DEL event so to identify the particular Jobs to be moved, Query Analyzer builds the following. SELECT *, JobHistory.Event AS Event, JobHistory.EventDate AS EventDate, JobHistory.JID AS JID FROM Jobs INNER JOIN JobHistory ON Jobs.ID = JobHistory.JID WHERE (JobHistory.Event = N'DEL') AND (JobHistory.EventDate < CONVERT(DATETIME, '2006-03-14 00:00:00', 102)) How do we get those Jobs to SELECT INTO the JobsArc table and also get all Events associated with those jobs and move into JobHistoryArc? (Hope that makes sense!)