SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: SQL query

  1. #1
    always learning . . .
    Join Date
    Nov 2003
    Location
    UK
    Posts
    821
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL query

    Guys have "tbl1" database on 1 server and "tbl2" database on another server. I have SQL enterprise manager so can map to both.

    In tbl1 there are different field names to field names in tbl2. But importatly 2 of them could make up 1 of them in tbl2. SO:

    firstname +_+ surname AS fullname

    So firstname and surname are field names in 1tbl where fullname is a field name in 2nd database table.

    What i wish is once a month to run a query automatically which will update tbl2 basically using tbl1 as the main source.

    Thanks

  2. #2
    <? james('rules'); ?>
    Join Date
    Jun 2004
    Location
    Wales, UK
    Posts
    788
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    // assign connection variables here then..
    $connect=mysql_connect($host$username$password);
    // then..
    $sql="SELECT FROM tbl1 WHERE blah='$blah' ...";
    $result=mysql_query($sql)or die...;
    mysql_close($connect); // need this because we are making a second connection

    // now start a new connection to second database then do querys..
    $query="UPDATE tbl2 SET blah=$result ...";
    mysql_query($query); 
    Think this would work. You may have to edit the update query a bit. Maybe use mysql_fetch_assoc($result) to get the value of the result or something, this should be the idea though.
    If knowledge is power - Why isn't our army librarians?!
    Statistics show that 63% of all statistics are fake.
    When i was little i broke my neck, and i havent looked back since .
    I completed the internet in 1 week. The end boss was pretty easy though .

  3. #3
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no need to get PHP involved. you can link sql servers together so that from server2 you can directly call server1

    you would do something like

    UPDATE table2 SET fullname = t1.firstname + ' ' + t1.lastname FROM server1.database1.dbo.table1 t1 WHERE table2.key = t1.key
    -- JIM BOLLA
    Wanna play Halo 2? My XBOX Live gamertag: crowdozer

  4. #4
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,235
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Or rather than trying to do this all in your programming, why not create a view in one database that works with the other server? This is probably the most maintainable approach in the long run. Let your DBA do the job he's supposed to do .

  5. #5
    <? james('rules'); ?>
    Join Date
    Jun 2004
    Location
    Wales, UK
    Posts
    788
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Heh, good thinking vgarcia .
    If knowledge is power - Why isn't our army librarians?!
    Statistics show that 63% of all statistics are fake.
    When i was little i broke my neck, and i havent looked back since .
    I completed the internet in 1 week. The end boss was pretty easy though .

  6. #6
    always learning . . .
    Join Date
    Nov 2003
    Location
    UK
    Posts
    821
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What you mean by create a view ? Is a SQL trigger on the server an option too ? I think I may be going this route then the SQL server looks after it self

    After a repsponse I have another question but shall post here instead of creating a new thread

    TJ


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •