Insert record from one db to another db

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

Re: Insert record from one db to another db

"Simon Gare" 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

Re: Insert record from one db to another db

"Simon Gare" 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
>
>

Re: Insert record from one db to another db

"Simon Gare" 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

Re: Insert record from one db to another db

Thanks Again Guys,


"Mike Brind" wrote in message
news:%23XASlKiNHHA.3288@TK2MSFTNGP03.phx.gbl...
>
> "Simon Gare" 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
>
>