ASP - Insert record from one db to another db

Author

Insert record from one db to another db

Simon Gare

2007-01-12, 3:56 am

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

Roland Hall

2007-01-12, 3:56 am

"Simon Gare" <sg@simongare.com> wrote in message
news:%23MLhMZgNHHA.3952@TK2MSFTNGP02.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

Mark J. McGinty

2007-01-12, 3:56 am

"Simon Gare" <sg@simongare.com> wrote in message
news:%23MLhMZgNHHA.3952@TK2MSFTNGP02.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.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
>
>

Mike Brind

2007-01-12, 3:56 am

"Simon Gare" <sg@simongare.com> wrote in message
news:%23MLhMZgNHHA.3952@TK2MSFTNGP02.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

Simon Gare

2007-01-12, 7:56 am

Thanks Again Guys,

"Mike Brind" <paxtonend@hotmail.com> wrote in message
news:%23XASlKiNHHA.3288@TK2MSFTNGP03.phx.gbl...
>
> "Simon Gare" <sg@simongare.com> wrote in message
> news:%23MLhMZgNHHA.3952@TK2MSFTNGP02.phx.gbl...
>
> 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
>
>

Sponsored Links