Post reply

Linked databases and update table

  • September 23, 2009 at 9:25 am

    #138338

    I have two databases that I linked. I need to script? (the databases are updated to the lastest versions frequently...thats why I thought a script would be best?) I need to script a select statement from database 'A' table 'Aa' and relavent data columns and compare against database 'B' Table 'Bb' and insert accordingly (The tables are not the same). I also need the script to run at a given time...say 3am. An explanation of the answer(s) and insight into the related architecture would be greatly appricated.

    Thanks 🙂

  • September 23, 2009 at 12:20 pm

    #1057693

    blawrence (9/23/2009)

    I have two databases that I linked.

    How did you link the database?

    I need to script?

    You need to script the entire database?

    (the databases are updated to the lastest versions frequently...thats why I thought a script would be best?)

    data gets updated or database itself gets created frequently?

    I need to script a select statement from database 'A' table 'Aa' and relavent data columns and compare against database 'B' Table 'Bb' and insert accordingly (The tables are not the same).

    Accessing the different database can be done something like, db.schema.tablename. You can do insert into your Bb table from the Aa table using db.schema.tablename.See if your database is on some other server, then you must be using linked server.

    I also need the script to run at a given time...say 3am. An explanation of the answer(s) and insight into the related architecture would be greatly appricated.

    You can use jobs to do this.

    Thanks 🙂

    Thanks 🙂

    I attempted to answer your query in parts(as your requirement was not clearly laid out), I would appreciate if you can provide some more information. thanks.

    ---------------------------------------------------------------------------------

  • September 23, 2009 at 1:49 pm

    #1057744

    Using MS SQL 2005.

    I linked Database 'HR' to Database 'Production' via SQL Server Object Explorer> Objects > Linked Servers.

    The script in need I mentioned is a required activity that will select 'new' employee data from a table in the 'HR' database and insert it into a table in the Production database on a nightly bases.

    The tables and column names from origin to source are not identical.

    I thought a script was a good idea because the database version is updated frequently.

    I know about the four part naming schema for linked servers such as linkedDataBaseName.Catalog.DataBase.Tablename.

    I noticed you mentioned Jobs how would this work and why would this a preference?

    Thanks for the response. 🙂

  • September 25, 2009 at 1:53 am

    #1058369

    I linked Database 'HR' to Database 'Production' via SQL Server Object Explorer> Objects > Linked Servers.

    You would typically use linked servers to access databases across 'servers'

    The script in need I mentioned is a required activity that will select 'new' employee data from a table in the 'HR' database and insert it into a table in the Production database

    You can write a query or a stored procedure for this.

    on a nightly bases.

    I noticed you mentioned Jobs how would this work and why would this a preference?

    This is where a scheduled job would help which would call the query/sp which you will write.

    I am sure there are lot of tutorials on sql jobs but please have a go at it. http://msdn.microsoft.com/en-us/library/ms187880(SQL.90).aspx

    ---------------------------------------------------------------------------------

Viewing 4 posts - 1 through 3 (of 3 total)

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