March 11, 2009 at 10:54 am
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
March 11, 2009 at 11:18 am
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
March 11, 2009 at 11:36 am
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...
March 11, 2009 at 12:04 pm
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
March 11, 2009 at 2:01 pm
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..
March 11, 2009 at 2:45 pm
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
March 11, 2009 at 3:15 pm
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
January 7, 2015 at 12:31 am
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
June 9, 2016 at 1:15 pm
Hi,
i have a similar problem. can you let me know how you resolved this issue.
June 9, 2016 at 1:36 pm
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply