Unanswered: join tables in same instance differant databases
Hi again experts, I have a problem in that a database I have created required splitting into three databases. The records were similar but reporting and maintenance differed enough that splitting the data seemed the best option. So now I have three databases that are needing to be updated from one file and I am not sure how to do that. See the illustration below. The Machine table is a one to many to the Job table, the Job table is a one to one with the Run Data. The first idea was to query the first database if the record was not found go to the next. If no match was found in any table drop it in a reject bucket. Then what about perhaps a lookup table with starting and ending Job ranges. I would query it to find the correct database then do another query to update the record. But then I could have some type of joining table. Not sure how that would look across databases. The tables I am updating or adding new records to has the same fields, unique key across all the databases. I could make one big table which gets rid of the query to find the correct database to update but I still would have to connect this back to the Job table which brings me back to a join table or index table. I have all these ideas and not sure if one of these is good or if there is a better way. Can someone help me out? Thanks William
Last edited by wjburke2; 09-28-15 at 15:02.
Reason: Wrong illistration
Sorry I guess I haven't asked this question well, I have three databases and one update source. I need to find the best way to identify which database the update record belongs with. Guess this is a uncommon question. Thanks for looing at it anyway.
Thanks for the reply, I was really looking for a way to query all the table say with a union query, since Run# is unique and the run data is the same it would work. My concern was then could I update the records, with some query functions you loose the ability to update joined tables. The road I am currently heading down is, I have created a lookup table with beginning and ending ranges and the database type. Using a stored procedure to update a temporary update table with record type. Then when I am ready to update in my SSIS package I use a conditional split to determine with database the record goes with. I am sure there is a easier way but I am new to SSIS.
In your union, you can add an extra column that identifies the database, then return the matched records into a temp table. From there, you know the records that need to be updated, as well as which database to apply the updates.
insert into tmpTableResults
join (select * from
select 'A' DatabaseName, * from DatabaseA..Table
select 'B' DatabaseName, * from DatabaseB..Table
select 'C' DatabaseName, * from DatabaseC..Table
on NewRecords.Value = e.Value
Within SSIS, you can create three legs off of that into each of your three databases. In the legs, you can set up criteria. For instance, DatabaseName = 'A'. You get the gist.
The correct answer would be to not have your databases split in to 3.
Why were they split and why can't they be combined?
Thanks for your reply. I hear you and I tried that just didn't work out. The data looks similar but is treated differently. Database 1 an 2 have the same data layout. Data base 3 is a different layout from database 1 and 2. The primary key and data fields are different. The Run data is however the same layout for all three. Jobs in database 1 runs around 3.6 million records/job, typical jobs on database 2 and 3 run about 250 thousand. All three are reported on their own reports/pivot tables. Records in database 1 are removed after 90 days while database 2 and 3 are retained for 1 year. Run data that is used to update the run table is downloaded nightly from a single source for all three. This is big data 60 to 100 million records they were split because of the reasons above and because if you have ever tried run a pivot table on a 60 to 100 million record database you know you'll be there for awhile. 30 million is no walk in the park but its do able. Hope this clears up your question.