How to move Multiple Related Tables Data from one Database to another..

  • gc_0620-1039885

    SSC-Addicted

    Points: 486

    Folks, up until now I have only inserted data into a single table. Now I have a situation to periodically Move older records from the Active database (based on tbl3_Row_Add_date if older than 3 years) into an Archive database. We would like run this routine once a Month.

    Both Active and Archive Database Are 100% Identical. Archive will be used for lookup only; no data manipulation will be done there except to run some reports.

    What I want to know is:

    1) How do you go about Moving data into multiple related tables that have constraints on them from one Database to Another Identical Database?

    2) Would you use a stored procedure in an instance like this?

    Any help would be greatly appreciated!

    Say for instance both my Active/Archive Databases have 4 related tables with the following Structure's.

    table1

    ------

    tbl1_id (Auto Generated Primary Key)

    tbl1_data1

    tbl1_data2

    table2

    ------

    tbl2_id (Auto Generated Primary Key)

    tbl1_id (Foreign Key related to table1 Primary Key tbl1_id)

    tbl2_data1

    tbl2_data2

    table3

    ------

    tbl3_id (Auto Generated Primary Key)

    tbl1_id (Foreign Key related to table1 Primary Key tbl1_id)

    tbl3_data1

    tbl3_data2

    tbl3_Row_Add_date (Recorded as Today's date in mm/dd/yyyy format for each new Row)

    table4

    ------

    tbl4_id (Auto Generated Primary Key)

    tbl3_id (Foreign Key related to table3 Primary Key tbl3_id)

    tbl4_data1

    tbl4_data2

  • Lowell

    SSC Guru

    Points: 323400

    i always move the data in FK Hierarchy order; it makes sense when you look at it today, and 6 months from now when you add other tables.

    stored procedure is fine, if the Archive database is on a different server,i would just make sure i had a linked server FROM archive connecting to Active.

    since you are just moving old data, it's a simple as:

    [font="Courier New"]

    SET XACT_ABORT ON

    BEGIN TRAN

    SET IDENTITY_INSERT Table1 ON --allow insert into the identity() column

    INSERT INTO Table1 --this is on Archive.dbo.Table1

    SELECT * FROM Active.dbo.Table1 ACTIVESERVER

    LEFT OUTER JOIN  Table1  ON ACTIVESERVER.tbl1_id = Table1.tbl1_id

    WHERE   Table1.tbl1_id IS NULL

    --forgot this part

    AND ACTIVESERVER.tbl_id IN(SELECT tbl1id FROM ACTIVESERVER.dbo.table3 WHERE tbl3_Row_Add_date > DATEADD(YEAR,-3,GETDATE()) )

    SET IDENTITY_INSERT Table1 OFF

    --repeat for each of the child tables

    COMMIT TRAN

    [/font]

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • gc_0620-1039885

    SSC-Addicted

    Points: 486

    Thanks Lowell for your quick response..

    I forgot to mention, I am using SQL 2005 and Both Active and Archive Databases are in the Same Server:

    Database Names:

    Active_Client_Data

    Archive_Client_Data

    And the Server Name is: Client_Data

    What needs to be change in your earlier code? Please help...

  • Lowell

    SSC Guru

    Points: 323400

    psuedocode sucks....i know...

    what are the REAL table names and theuir primary key columns....they aren't really named Table1 are they?

    can you post the CREATE TABLE for your 4 tables?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • gc_0620-1039885

    SSC-Addicted

    Points: 486

    Thanks Lowell for your quick responses..

    The Databases are: Active_Client_data and Archive_Client_data. Both are in same server. Structurally, they are 100% identical with table's, SP's and Views. except table data's are different.

    The Server Name: Client_Data

    table1

    ------

    SELECT [Client Id] -- (Auto Generated Primary Key )

    ,[Date]

    ,[FirstName]

    ,[MiddleName]

    ,[LastName]

    FROM [Active_Client_Data].[dbo].[Main Client]

    table2

    ------

    SELECT [AnchesterID] --(Auto Generated Primary Key)

    ,[Client Id] -- (Foreign Key related to [Main Client] Primary Key [Client Id])

    ,[Notes]

    FROM [Active_Client_Data].[dbo].[Anchestry]

    table3

    ------

    SELECT [encounterid] -- (Auto Generated Primary Key)

    ,[Client Id] -- (Foreign Key related to [Main Client] Primary Key [Client Id])

    ,[Employee ID]

    ,[Facility Name]

    ,[Date]

    ,[Record Add date] -- Recorded as Today's date in mm/dd/yyyy format for each new Row), it is Datetime Column Type...

    FROM [Active_Client_Data].[dbo].[Encounter]

    table4

    ------

    SELECT [Services_Detail_ID] -- (Auto Generated Primary Key)

    ,[Encounter ID] -- (Foreign Key related to [Encounter] Primary Key [encounterid])

    ,[Services]

    FROM [Active_Client_Data].[dbo].[services detail]

    ________________________

    All the Primary Key of all Tables have the exact same type Properties... That is they are identity and Identity increment by 1. Please see attachment.

    My other question is that how we are going to maintain the identity column values in Archive and Active Databases.. For example in [Main Client] Table in Archive database last [Client Id] is 100 after we moved Data from Active to Archive Database. And say, we add 2 new records in Active with [Client Id]'s 101 and 102 and both are over 3 year old. But before we move data again we deleted the [Client Id] 101 from [Main Client] in Active Database.

    Now if we move data again, The [Client Id] in [Main Client] in Archive Database will be 101 because it is Identity Column with Identity increment by 1. but it is actually 102 in [Main Client] in Active Database.. and that 102 is recorded in [Client Id] column in [Encounter] table as well..

    The same scenario can happen with the other dependent child tables in both Databases.. Correct me please if I am wrong.. Thanks again..

  • Lowell

    SSC Guru

    Points: 323400

    thanks for the better look at the schema; all follow up after testing.

    you might be confused as to how the Identity() property on a column works, and also what is going to be migrated;

    hopefully, I'll clear that up here:

    the identity() property keeps incrementing the automatically generated valueso that no number is repeated. add 100 rows, the next value is 101, right?

    delete all 100 rows, the next value is STILL 101..it keeps going right where it left off....next hundred is 101 to 201, next value is 202...even if you delete everything in the table

    so if you have child data that is realted to that identity, you have to delete(in this case, migrate...then delete) before you can do the same to the parent table.

    the audit table we will use is the exact same structure, and the only thing different is that we will use IDENTITY_INSERT YOURTABLE ON/OFF, so that we can insert the values without getting an automatic value for the identity columns.

    Very common situation, seen it lots of times. so say "bob" is client ID1 in the main table, and there are various records in the other tables relate to bob.

    Say in the Archive database, we have to insert 'bob', with his original ClientId, into the table, and then insert the child data, along with their PK ID's, into the matchign Archive tables.

    Then, in the reverse order, we delete the data in the child tables on your Active database tables, and then finally delete 'bob' from the parent table.

    That is what we will be doing, but with all records matching that date criteria, instead of just one record.

    As long as noone does a TRUNCATE TABLE command on the child tables(which really does reset the Identity() to 1) you will never have an isue with numbers repeating or needing to "figure out" andy of the identity values.

    gc_0620 (3/11/2009)


    Thanks Lowell for your quick responses..

    ________________________

    All the Primary Key of all Tables have the exact same type Properties... That is they are identity and Identity increment by 1. Please see attachment.

    My other question is that how we are going to maintain the identity column values in Archive and Active Databases.. For example in [Main Client] Table in Archive database last [Client Id] is 100 after we moved Data from Active to Archive Database. And say, we add 2 new records in Active with [Client Id]'s 101 and 102 and both are over 3 year old. But before we move data again we deleted the [Client Id] 101 from [Main Client] in Active Database.

    Now if we move data again, The [Client Id] in [Main Client] in Archive Database will be 101 because it is Identity Column with Identity increment by 1. but it is actually 102 in [Main Client] in Active Database.. and that 102 is recorded in [Client Id] column in [Encounter] table as well..

    The same scenario can happen with the other dependent child tables in both Databases.. Correct me please if I am wrong.. Thanks again..

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • gc_0620-1039885

    SSC-Addicted

    Points: 486

    Lowell,

    Now I understand about the Identity issue. So are you going to send me the codes how to update those 4 tables from Active to Archive database? I sent you the column names as parent child relationship between the tables.

    I appreciately for all your help.. Thanks

  • hk.ranjitha

    SSC Journeyman

    Points: 95

    Hi,

    Can you please post the entire code? Even my requirement is same so that i can refer the code.

    It will be helpful if you can post the code.

    Thanks,

    Ranjitha

  • jujusa2016

    SSC Veteran

    Points: 215

    Hi,

    i have a similar problem. can you let me know how you resolved this issue.

  • Lowell

    SSC Guru

    Points: 323400

    jujusa2016 (6/9/2016)


    Hi,

    i have a similar problem. can you let me know how you resolved this issue.

    post a new thread with details about what you are trying to do;

    you'll get the most help that way.

    the details are the key, your situation might be similar to otehr posts, but if you post specifics, we can offer details that are tested and work perfectly for your situation.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply