INSERT from one database to another?

18 Nov 2005 9:24 PM

Vagabond Software

INSERT INTO db2.dbo.myTable (id, person) SELECT id, person FROM
db1.dbo.myTable WHERE id = 1

I'm trying to do this using an SqlTransaction. I am assigning the above SQL
statement to the CommandText property of the SqlCommand object associated
with the transaction. Do I need to open two DbConnection objects to the
instance of SQL Server where these databases reside? Do I only need one
open DbConnection? Is there a better way this needs to be done?

Any help is greatly appreciated.

Carl

18 Nov 2005 9:27 PM

Marina

You can reference a table in another database on the same by using:
databasename..tablename.

So something like:

SELECT * FROM myotherdatabase..sometable.

Show quote

"Vagabond Software" <vagabondsw***@-X-gmail.com> wrote in message
news:O6n$3ZI7FHA.2676@TK2MSFTNGP15.phx.gbl...
> INSERT INTO db2.dbo.myTable (id, person) SELECT id, person FROM
> db1.dbo.myTable WHERE id = 1
>
> I'm trying to do this using an SqlTransaction. I am assigning the above
> SQL statement to the CommandText property of the SqlCommand object
> associated with the transaction. Do I need to open two DbConnection
> objects to the instance of SQL Server where these databases reside? Do I
> only need one open DbConnection? Is there a better way this needs to be
> done?
>
> Any help is greatly appreciated.
>
> Carl
>

19 Nov 2005 2:57 AM

Vagabond Software

"Marina" <someone@nospam.com> wrote in message
news:eyXwnbI7FHA.3388@TK2MSFTNGP11.phx.gbl...
> You can reference a table in another database on the same by using:
> databasename..tablename.
>
> So something like:
>
> SELECT * FROM myotherdatabase..sometable.
>

Thanks. I actually ended up using [myotherdatabase].dbo.sometable and it
worked. I appreciate the help.

Carl