Insert record from one db to another db

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Simon Gare
    #1

    Insert record from one db to another db

    Hi all,

    need to retrieve a record from a table (tblBookingForm ) in one database and
    insert it into a table (tblNetServ) in another database on the same server,
    leaving the original record in place. Cant get my head round it though,
    doesn't seem to want to connect to 2 databases at the same time.

    Anyone help?

    Regards
    Simon

    --
    Simon Gare
    The Gare Group Limited

    website: www.thegaregroup.co.uk
    website: www.privatehiresolutions.co.uk


    Tags: None
    • Roland Hall
      #2
      Re: Insert record from one db to another db

      "Simon Gare" <sg@simongare.c omwrote in message
      news:%23MLhMZgN HHA.3952@TK2MSF TNGP02.phx.gbl. ..
      need to retrieve a record from a table (tblBookingForm ) in one database
      and
      insert it into a table (tblNetServ) in another database on the same
      server,
      leaving the original record in place. Cant get my head round it though,
      doesn't seem to want to connect to 2 databases at the same time.
      Which database and what approach are you using?

      --

      Roland Hall


        Comment

        • Mark J. McGinty
          #3
          Re: Insert record from one db to another db


          "Simon Gare" <sg@simongare.c omwrote in message
          news:%23MLhMZgN HHA.3952@TK2MSF TNGP02.phx.gbl. ..
          Hi all,
          >
          need to retrieve a record from a table (tblBookingForm ) in one database
          and
          insert it into a table (tblNetServ) in another database on the same
          server,
          leaving the original record in place. Cant get my head round it though,
          doesn't seem to want to connect to 2 databases at the same time.
          You only need to connect to one database, reference the table (or any other
          object) in a database other than the connection's current database using
          3-part names: database.schema .objectname. (If schema is dbo, it can be
          omitted.) So something like this:

          INSERT INTO otherdatabase.d bo.tblNetServer (field1, field2[, ...])
          SELECT field1, field2[, ...] FROM tblBookingForm [WHERE ...]

          (My example assumes you are using SQL Server.)


          -Mark



          Anyone help?
          >
          Regards
          Simon
          >
          --
          Simon Gare
          The Gare Group Limited
          >
          website: www.thegaregroup.co.uk
          website: www.privatehiresolutions.co.uk
          >
          >

            Comment

            • Mike Brind
              #4
              Re: Insert record from one db to another db


              "Simon Gare" <sg@simongare.c omwrote in message
              news:%23MLhMZgN HHA.3952@TK2MSF TNGP02.phx.gbl. ..
              Hi all,
              >
              need to retrieve a record from a table (tblBookingForm ) in one database
              and
              insert it into a table (tblNetServ) in another database on the same
              server,
              leaving the original record in place. Cant get my head round it though,
              doesn't seem to want to connect to 2 databases at the same time.
              >
              Connect to one after the other.

              conn1.open
              set rs1 = conn1.execute(" Select field1, field2, field3 FROM table1")
              'Retrieve record from db1
              'assign values to variables
              tempfield1 = rs("field1")
              tempfield2 = rs("field2")
              tempfield3 = rs("field3")
              rs.close : set rs= nothing : conn1.close : set conn1 = nothing

              conn2.open
              'Insert variable values into db2
              conn2.execute(" Insert into table2 (field1, field2, field3) Values (" &
              tempfield1 & "," & tempfield2 & "," & tempfield3 & ")
              conn2.close : set conn2 = nothing

              --
              Mike Brind


                Comment

                • Simon Gare
                  #5
                  Re: Insert record from one db to another db

                  Thanks Again Guys,


                  "Mike Brind" <paxtonend@hotm ail.comwrote in message
                  news:%23XASlKiN HHA.3288@TK2MSF TNGP03.phx.gbl. ..
                  >
                  "Simon Gare" <sg@simongare.c omwrote in message
                  news:%23MLhMZgN HHA.3952@TK2MSF TNGP02.phx.gbl. ..
                  Hi all,

                  need to retrieve a record from a table (tblBookingForm ) in one database
                  and
                  insert it into a table (tblNetServ) in another database on the same
                  server,
                  leaving the original record in place. Cant get my head round it though,
                  doesn't seem to want to connect to 2 databases at the same time.
                  >
                  Connect to one after the other.
                  >
                  conn1.open
                  set rs1 = conn1.execute(" Select field1, field2, field3 FROM table1")
                  'Retrieve record from db1
                  'assign values to variables
                  tempfield1 = rs("field1")
                  tempfield2 = rs("field2")
                  tempfield3 = rs("field3")
                  rs.close : set rs= nothing : conn1.close : set conn1 = nothing
                  >
                  conn2.open
                  'Insert variable values into db2
                  conn2.execute(" Insert into table2 (field1, field2, field3) Values (" &
                  tempfield1 & "," & tempfield2 & "," & tempfield3 & ")
                  conn2.close : set conn2 = nothing
                  >
                  --
                  Mike Brind
                  >
                  >

                    Comment

                    Previous template Next
                    Working...