Insert record from one db to another db

Discussion in 'ASP General' started by Simon Gare, Jan 12, 2007.

  1. Simon Gare

    Simon Gare Guest

    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
     
    Simon Gare, Jan 12, 2007
    #1
    1. Advertising

  2. Simon Gare

    Roland Hall Guest

    "Simon Gare" <> wrote in message
    news:%...
    > 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
     
    Roland Hall, Jan 12, 2007
    #2
    1. Advertising

  3. "Simon Gare" <> wrote in message
    news:%...
    > 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.dbo.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
    >
    >
     
    Mark J. McGinty, Jan 12, 2007
    #3
  4. Simon Gare

    Mike Brind Guest

    "Simon Gare" <> wrote in message
    news:%...
    > 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
     
    Mike Brind, Jan 12, 2007
    #4
  5. Simon Gare

    Simon Gare Guest

    Thanks Again Guys,


    "Mike Brind" <> wrote in message
    news:%...
    >
    > "Simon Gare" <> wrote in message
    > news:%...
    > > 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
    >
    >
     
    Simon Gare, Jan 12, 2007
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. loveNUNO
    Replies:
    2
    Views:
    935
    loveNUNO
    Nov 20, 2003
  2. Replies:
    1
    Views:
    1,799
    Albert Hopkins
    Dec 6, 2008
  3. Frits van Soldt

    How to insert a new record in detailsview when there are no record

    Frits van Soldt, Nov 16, 2005, in forum: ASP .Net Web Controls
    Replies:
    2
    Views:
    757
    Frits van Soldt
    Nov 17, 2005
  4. Robert Cohen
    Replies:
    3
    Views:
    284
    Andrew Durstewitz
    Jul 15, 2003
  5. Guest
    Replies:
    1
    Views:
    268
    Alex G
    Jan 9, 2004
Loading...

Share This Page