locked
Updating Tables from other Tables RRS feed

  • Question

  • Hi I'm new SQL so please help if you can.

    We currently have two CAD software packages that output their own DB's. We have combined both outputs into a SQL DB

    so lots of tables from CAD App 1 and lots of other Tables from CAD App 2.

    we would like to use some of the information from CAD App '1's' Tables in CAD App '2's' Tables & vice versa:-

    For example if a user puts info into CAD App 1 then this same info is displayed in CAD App 2, because its being written to both sets of tables. is this possible (surely it is)??????

    Possible issues we may have

    The unique ID's from each package differs for the same represenative component placed within each CAD application.

    e.g if I place a block within 'CAD App 1' Called Jelly & another block in 'CAD App 2' Called Jelly they both generate their own separate unique id, Jelly 1 unique id = 001, Jelly 2 unique id = 526524.

    Is this an issue we need to address??? if so would you please suggest best approach.

    going back to the Jelly example to finish off.

    We have Jelly 1 in [CAD App 1 Table] which has fields Ice_Cream, & Flakes

    & Jelly 2 which also has fields Ice_Cream & Flakes in [CAD APP 2 Table]

    if jelly 1 has ice_cream then jelly 2 should also have ice cream

    if Jelly 2 has Flakes then Jelly 1 should also have Flakes

    Hope this make sense and thank very much for taking the time to read.

    Regards

    MP

    • Changed type MJP123 Thursday, March 24, 2016 2:53 PM Wrong place
    Thursday, March 24, 2016 1:51 PM

Answers

  • Hi MJP123,

    Sorry for the slow response but are you looking for something like:

    (#-tables are temporal objects for your session, its easiest to use for debugging and seeing how things work. Also its fairly safe to run it on an database for testing)

    Create table #valve
    (
    Tag_No int,
    E_Stop varchar(10)
    )
    CREATE TABLE #PipeAccessories
    (
    Tag_no int,
    E_STOP varchar(10)
    )
    
    insert into #valve values(1, 'yes')
    insert into #valve values(2, 'yes')
    insert into #valve values(3, 'yes')
    
    insert into #PipeAccessories values(1, 'yes')
    insert into #PipeAccessories values(2, 'yes')
    insert into #PipeAccessories values(3, 'no')
    
    /* if you don't have an else-case, use this version */
    update V
    set V.E_Stop ='no'
    from #valve as V
    inner join #PipeAccessories as PA
    on V.Tag_No = PA.Tag_no
    where PA.E_STOP = 'yes'
    
    /* if you don't have an else-case, don't use this version */
    update V
    set V.E_Stop = case when PA.E_STOP = 'yes' then 'no' else 'yes' end
    from #valve as V
    inner join #PipeAccessories as PA
    on V.Tag_No = PA.Tag_no
    where PA.E_STOP = 'yes'
    
    select * from #valve

    Thursday, March 31, 2016 10:42 AM

All replies

  • HiMJP123,

    Thank you for your question.

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. Thank you for your understanding and support.

    Thanks,
    Ice Fan


    Ice Fan
    TechNet Community Support


    Monday, March 28, 2016 10:31 AM
  • Greetings,

    This is pure speculation however as I have not done a synchronization of CAT-products before but to start it off:

    - Yes technically its possible to synchronize databases and the data they contain, it might even be fairly easy...

    based on your example I suspect you get two different projects:
    e.g if I place a block within 'CAD App 1' Called Jelly & another block in 'CAD App 2' Called Jelly they both generate their own separate unique id, Jelly 1 unique id = 001, Jelly 2 unique id = 526524.

    if the project in the database is "ID 29017" for "CAD App 1" and "ID20198" for "CAD App 2" and both are called "Bean", you will get two projects called "Bean" both with the respected information from either "CAD App 2" or "CAD App 1".

    If you're unlucky and id's aren't unique all over the database you will add partial informations and end up with a bunch of data and get an unpredictable Frankenstein-like result.

    This all said; you can (by SSIS, linked server etc.) synchronize data but your result might not be what you wish.
    So the real question is:
    "Why are you approaching this from a database perspective?"

    I mean when I hear CAD I relate it to AutoCAD and I can't imagine they have no source-control solutions that allow you to work on one project with multiple engineers. (if not, I found myself a new business!).

    with kind regards,

    Sebastian 

    Monday, March 28, 2016 3:03 PM
  • Both thanks for the reply, much appreciated.

    "Why are you approaching this from a database perspective?"

    Within our Industry we have something called BIM (Building Information Modelling).

    BIM uses 3D models that have design parameters assigned to each component or object added to a project, these design parameters are what we are sending to our Database & we'd like to manipulate & represent in various Reports & Schedules.

    We have two rival companies CAD software outputting to one database, set up on MS SQL Server Management Studio.

    One CAD Package generates a schematic version of our projects & the other the 3d aspects.

    There is info within the Schematics that is generated early on in a project lifecycle that will be very useful later on when the 3D modelling starts. (We can import this info direct into the 3D CAD app once the corresponding component has been placed with same Tag_No)

    We have set up the two packages to output to the DB as follows;

    A Database Called 'Bean' is created on MS SQL Server.

    CAD_APP_1 (Schematic) Create a project Database Called 'Bean' that outputs to SQL Bean DB

    CAD_APP_2 (3d Model) Create a project Database Called 'Bean' that outputs to SQL Bean DB

    This is all working fine.

    All records at the moment are stored within the SQL Bean DB but they are specific to the individual CAD_Apps.

    We have a work around for the Unique ID.

    When we create the schematic instance of 'Jelly' we call it J001 (Tag_No).

    When we create the 3D version of Jelly we also give it the same tag J001 (Tag_No).

    This is what I've come up with so far as an attempt to create query, might be a million miles off but is the best I can do with my limited knowledge of SQL & DB's.

    INSERT

    INTOdbo.Valve.E_STOP (Valve is from the Schmatic DB)

    Values

    dbo.PipeAccessories.E_STOP (PipeAcessories is from the 3D DB)

    FROM

    dbo.PipeAccessories

    WHERE

    (Valve.Tag_No =PipeAccessories.Tag_No)and(Valve.E_STOP ISNULL);

    E_Stop is the value I wish to populate from one CAD_App to the next

    Tag_No is the Unique Id

    Many Thanks

    Martyn


    • Edited by MJP123 Tuesday, March 29, 2016 11:57 AM Mistake
    Tuesday, March 29, 2016 11:54 AM
  • So this is actually doing something (but not exactly what I want) it is updating the records from one table to the next! but I think

    "set dbo.valve.E_Stop = case when dbo.PipeAccessories.E_STOP = 'yes' then 'no'"

    is changing the yes value from Table1 to No when inserted into Table 2!? can anyone help I just want to insert the value from table 1 E_Stop into Table 2 E_stop

    Surely this is easy

    Sorry for PINK text don't know how to make it black :-)

    Update dbo.valve

    set dbo.valve.E_Stop = case when dbo.PipeAccessories.E_STOP = 'yes' then 'no'

    from dbo.valve inner join dbo.PipeAccessories.E_STOP

    on dbo.valve.Tag_No = dbo.PipeAccessories.Tag_no


    Wednesday, March 30, 2016 10:22 AM
  • Hi MJP123,

    Sorry for the slow response but are you looking for something like:

    (#-tables are temporal objects for your session, its easiest to use for debugging and seeing how things work. Also its fairly safe to run it on an database for testing)

    Create table #valve
    (
    Tag_No int,
    E_Stop varchar(10)
    )
    CREATE TABLE #PipeAccessories
    (
    Tag_no int,
    E_STOP varchar(10)
    )
    
    insert into #valve values(1, 'yes')
    insert into #valve values(2, 'yes')
    insert into #valve values(3, 'yes')
    
    insert into #PipeAccessories values(1, 'yes')
    insert into #PipeAccessories values(2, 'yes')
    insert into #PipeAccessories values(3, 'no')
    
    /* if you don't have an else-case, use this version */
    update V
    set V.E_Stop ='no'
    from #valve as V
    inner join #PipeAccessories as PA
    on V.Tag_No = PA.Tag_no
    where PA.E_STOP = 'yes'
    
    /* if you don't have an else-case, don't use this version */
    update V
    set V.E_Stop = case when PA.E_STOP = 'yes' then 'no' else 'yes' end
    from #valve as V
    inner join #PipeAccessories as PA
    on V.Tag_No = PA.Tag_no
    where PA.E_STOP = 'yes'
    
    select * from #valve

    Thursday, March 31, 2016 10:42 AM