Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2015
    Posts
    38
    Provided Answers: 1

    Unanswered: join tables in same instance differant databases

    Click image for larger version. 

Name:	DatabaseConfiguration.png 
Views:	1 
Size:	27.0 KB 
ID:	16568Hi 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
    Attached Thumbnails Attached Thumbnails DatabaseConfiguration.png  
    Last edited by wjburke2; 09-28-15 at 16:02. Reason: Wrong illistration

  2. #2
    Join Date
    Mar 2015
    Posts
    38
    Provided Answers: 1

    join tables in same instance differant databases

    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.
    Last edited by wjburke2; 09-29-15 at 14:56.

  3. #3
    Join Date
    Dec 2003
    Posts
    3
    To access tables in a different database, add the database name in front of the table name. For example, OtherDatabase..JobTable.

  4. #4
    Join Date
    Mar 2015
    Posts
    38
    Provided Answers: 1
    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.

  5. #5
    Join Date
    Dec 2003
    Posts
    3
    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.

    For example,

    insert into tmpTableResults
    select *
    from
    NewRecords
    join (select * from
    (
    select 'A' DatabaseName, * from DatabaseA..Table
    union
    select 'B' DatabaseName, * from DatabaseB..Table
    union
    select 'C' DatabaseName, * from DatabaseC..Table
    ) u
    ) e
    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.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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?
    George
    Home | Blog

  7. #7
    Join Date
    Mar 2015
    Posts
    38
    Provided Answers: 1
    Quote Originally Posted by gvee View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •