locked
Move Records from one table to an Archive table RRS feed

  • Question

  • I have several tables that are getting large, therefore, getting slower.  I want to move old inactive patient records from one table to an Archive table (exact duplicate) when the patient have been off service for 365 days or more.  I know I can create a recordset of qualifying records and perform an INSERT INTO and then a DELETE. 

    Is there a simple single step way to accomplish this task?

    Any feedback or assistance is appreciated.

    Thank you in advance

    BrianS

    Thursday, September 21, 2017 1:21 AM

All replies

  • Hi Brian. Just curious, how many records does your table have? I don't think there is a single step to move the data from one table to another but you can always automate multiple steps using a single call. Just my 2 cents...
    • Proposed as answer by BSirko Thursday, September 21, 2017 8:32 PM
    Thursday, September 21, 2017 1:53 AM
  • Well, you can execute an 'append' query restricted to 'WHERE NOT Active' where Active is a Boolean (Yes/No) column in the table, followed by a 'delete' query restricted in the same way.  It would be advisable to execute both within a single Transaction.

    However, it is rarely necessary to do this in a well designed database.  By using queries restricted to 'WHERE Active' in the day to day operation of the database only those rows will be returned.  If performance is an issue, this would suggest that the tables are not adequately indexed.

    Ken Sheridan, Stafford, England

    • Proposed as answer by BSirko Thursday, September 21, 2017 8:32 PM
    Thursday, September 21, 2017 4:51 PM
  • Hi BrianS,

    Did the suggestion from .theDBguy and Ken work for you?

    >> Is there a simple single step way to accomplish this task?

    I am afraid there is no simple way to accomplish this without insert into and delete query.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 26, 2017 8:25 AM
  • Yes they both simply stated there was no simple way, yes, either and or both are answers and the question can be closed.

    Thanks

    BrianS

    Tuesday, September 26, 2017 10:26 PM
  • Hi Brian. Although there isn't a single step to move the data, executing two steps to do it shouldn't be too much of a trouble. Right? Are you not able to create a function to execute the two steps one after the other? Then, in your application, it would seem you only did it with one step, by executing your custom function. Just my 2 cents...
    Tuesday, September 26, 2017 10:48 PM