Tech Off Thread

7 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Selecting data from one SQLServer DB into another DB on same server

Back to Forum: Tech Off
  • User profile image
    Dr Herbie

    I have a database server with two databases : a live database and a test database.  The test database is essentially an old version of the live database.

    The live database has been updated with some live data on some records.  Only one column has been updated.

    I want to copy the new data into the test database, only updating the single updated field in the relevant rows.

    I can't remember the syntax for this, nor can I find an answer on Google (I must be using the wrong term for this or something).


    Any help?


    Herbie


    EDIT: I'm hoping for something like:

    UPDATE [Test].Table set field = (select field from [Live].Table where [Live].Table.Field = [Test].Table.Field)

  • User profile image
    NuTcAsE

    Dr Herbie wrote:
    I have a database server with two databases : a live database and a test database.  The test database is essentially an old version of the live database.

    The live database has been updated with some live data on some records.  Only one column has been updated.

    I want to copy the new data into the test database, only updating the single updated field in the relevant rows.

    I can't remember the syntax for this, nor can I find an answer on Google (I must be using the wrong term for this or something).


    Any help?


    Herbie


    EDIT: I'm hoping for something like:

    UPDATE [Test].Table set field = (select field from [Live].Table where [Live].Table.Field = [Test].Table.Field)



    UPDATE
        [Test].dbo.[Table1]
    SET
        [Test].dbo.[Table1].field = P.field
    FROM
        [Production.dbo.[Table1] P
    WHERE
        [Test].dbo.[Table1].IDField = P.IDField  AND
        [Test].dbo.[Table1].field <> P.field

  • User profile image
    PerfectPhase

    As I'm a lazy sod at times* I just use http://www.sqldelta.com/ has a good data compare and update tool.

    * Why write SQL when a tool will do it for you...

  • User profile image
    Dr Herbie

    NuTcAsE wrote:
    
    Dr Herbie wrote:
    I have a database server with two databases : a live database and a test database.  The test database is essentially an old version of the live database.

    The live database has been updated with some live data on some records.  Only one column has been updated.

    I want to copy the new data into the test database, only updating the single updated field in the relevant rows.

    I can't remember the syntax for this, nor can I find an answer on Google (I must be using the wrong term for this or something).


    Any help?


    Herbie


    EDIT: I'm hoping for something like:

    UPDATE [Test].Table set field = (select field from [Live].Table where [Live].Table.Field = [Test].Table.Field)



    UPDATE
        [Test].dbo.[Table1]
    SET
        [Test].dbo.[Table1].field = P.field
    FROM
        [Production.dbo.[Table1] P
    WHERE
        [Test].dbo.[Table1].IDField = P.IDField  AND
        [Test].dbo.[Table1].field <> P.field



    Thanks!  It was the dbo part of the fully qualified name I was missing!


    Herbie.

  • User profile image
    jmarston

    I'm interested in the thread I found regarding the following syntax adn edited almost completely, but not sure what the P means...can you advise?

    The two databases are
    every16_joom1  - database
    jos_contest - table
    id - field

    and
    every16_web - datbase
    news - table
    article_id - field


    UPDATE
        [every16_joom1].dbo.[jos_contest]
    SET
        [every16_joom1].dbo.[jos_contest].id = P.article_id
    FROM
        [every16_web.dbo.[news.article_id] P
    WHERE
        [every16_joom1].dbo.[jos_contest].id= P.IDField  AND
        [every16_joom1].dbo.[jos_contest].field <> P.field

  • User profile image
    PerfectPhase

    You can write it more verbose with an AS

    FROM
        [every16_web.dbo.[news.article_id] AS P

    [AS] table_alias
    Is an alias for table_source that can be used either for convenience or to distinguish a table or view in a self-join or subquery. An alias is frequently a shortened table name used to refer to specific columns of the tables in a join. If the same column name exists in more than one table in the join, SQL Server requires that the column name be qualified by a table name, view name, or alias. The table name cannot be used if an alias is defined.

    http://technet.microsoft.com/en-gb/library/ms177634.aspx


  • User profile image
    Matthew van Eerde

    Another way to do it is to use an updateable VIEW:

    CREATE VIEW
        TestAndLive
    AS
        SELECT
           L.ValueField AS "Live",
           T.ValueField AS "Test",
           L.KeyField
        FROM
           [Live]..Table AS L INNER JOIN
           [Test]..Table AS T ON
              L.KeyField = T.KeyField

    Now you can do

    UPDATE
        TestAndLive
    SET
        Test = Live

    or even

    SELECT
        *
    FROM
        TestAndLive

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.