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]

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?

 

[Mark J. McGinty]

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

 

[Mike Brind]

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

 

[Simon Gare]

Thanks Again Guys,

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