Post reply

What tool (DTS) is best for running multiple queries with multiple datasources

  • November 5, 2004 at 4:43 pm

    #60645

    I am a newbie and unsure how to best approach the following task,  Any suggestions is greatly appreciated.

    I have 2 datasources 1 SQL and 1 Oracle.  I have created UDL's to reference this datasources.  From what I have seen in DTS you can only reference one datasource within a query(how do I get around this)

    I need to compare a table on the SQL with a table on the Oracle Server, if the Oracle data is different (new records) then Update the Sql data.

    I have created the followng queries, unfortunately, I am unsure how to use in DTS (or is there a better way, ie. store procedure etc.)  I have a group of these types of queries to update on a nightly basis.  I also need to generate and error msg(email) if the update Fails.

    Please be as specific as possibles.

    INSERT INTO dbo.ProjectTypes ( ProjectTypeCode, ProjectTypeDescrip,

    ProjectCatagory, ProjectClass, CapitalProjectNum, DateCreated, UserCreated,

    DateModified, UserModified )

    SELECT Q1.ID, Q1.DESCR, Q1.PROJ_DESCR_CATEGORY, Q1.PROJ_CLASS, Q1.CPN,

    CURRENT_TIMESTAMP  AS DateCreated, 'Import' AS UserCreated,

    CURRENT_TIMESTAMP AS DateModified, 'Import' AS UserModified

    FROM SiteManager1..APPPCT.PROJECT_TYPE as Q1

    LEFT JOIN ProjectTypes ON Q1.ID = ProjectTypes.ProjectTypeCode

    WHERE (((ProjectTypes.ProjectTypeCode) Is Null));

    Thanks,

    Karen

     

  • November 6, 2004 at 2:57 am

    #529185

    hi i am jus trying out..

    we can pull all the datda from Oracle to SQL server and run a store procedure to compare and update accordingly...

    i am also trying for the same thing but its other way around ..like i am trying to update oracle DB corresponding to changes in Sql server...using DTS

    i think it may not help u but its jus a contribution

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

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