Insert record from one db to another db

[Simon Gare]

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]

"Simon Gare" <[e-mail] in message
news:%2****************@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]

"Simon Gare" <[e-mail] in message
news:%2****************@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]

"Simon Gare" <[e-mail] in message
news:%2****************@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]

Thanks Again Guys,
"Mike Brind" <pa*******@hotmail.comwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...

>
"Simon Gare" <[e-mail] in message
news:%2****************@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