Questions

updating tables in DB from data being entered in another DB

+
0 Votes
Locked

updating tables in DB from data being entered in another DB

pierrehar
Hi Folks,
I'm new to SQL server and I have a project I'm working on.
I'm at the point where I'd like to setup a job that will run everyday to update certain tables in my DB by querying tables that are in another DB.
Can anyone point me to where I can find good info on writing a query for this?
Thanks.
  • +
    0 Votes
    david.fournier

    Unfortunately, there is a lot of detail missing in what you're asking. What platform is the source database? And the destination? If they're both Oracle use a db_link to bridge and a job to run the query. If one is MSSQL you could use DTS or SSIS. If it's MySQL to DB2, start looking at scheduler and third party tools.

    In regard to the query itself, just watch your 'p's and 'q's on the data format, especially dates. Best to pull them as strings from the source and translate them on the destination.

    Hope that helps.

    +
    0 Votes
    ralph.f.frederick

    If you are copying from MS SQL server db to another you need to create a link between the servers on the destination server then just update from source db. Use a 4 part quailfier for the source database ex: Update destination_table
    set dt.col = st.col
    from source_server.db.owner.table as st, dest_table as dt
    Where ...

    +
    0 Votes
    hlathiha

    need to define a linked server for each external data source.
    want to access and the configure the security context under which your distributed
    queries will run. After create a linked server, can use the Transact-SQL
    OPENQUERY function to execute your distributed queries.

  • +
    0 Votes
    david.fournier

    Unfortunately, there is a lot of detail missing in what you're asking. What platform is the source database? And the destination? If they're both Oracle use a db_link to bridge and a job to run the query. If one is MSSQL you could use DTS or SSIS. If it's MySQL to DB2, start looking at scheduler and third party tools.

    In regard to the query itself, just watch your 'p's and 'q's on the data format, especially dates. Best to pull them as strings from the source and translate them on the destination.

    Hope that helps.

    +
    0 Votes
    ralph.f.frederick

    If you are copying from MS SQL server db to another you need to create a link between the servers on the destination server then just update from source db. Use a 4 part quailfier for the source database ex: Update destination_table
    set dt.col = st.col
    from source_server.db.owner.table as st, dest_table as dt
    Where ...

    +
    0 Votes
    hlathiha

    need to define a linked server for each external data source.
    want to access and the configure the security context under which your distributed
    queries will run. After create a linked server, can use the Transact-SQL
    OPENQUERY function to execute your distributed queries.