Insert record from one db to another db

S

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
 
R

Roland Hall

Simon Gare said:
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?
 
M

Mark J. McGinty

Simon Gare said:
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
 
M

Mike Brind

Simon Gare said:
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
 
S

Simon Gare

Thanks Again Guys,


Mike Brind said:
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
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,898
Latest member
BlairH7607

Latest Threads

Top