working with multiple databases in SQL Server 2005

[rwb]

Does anybody know how to work with two (or more) databases in SQL Server 2005; or where that information can be obtained? I searched online, in BOL and asked in this forum but with no success.

Following advice on MSDN SQL Server forum results in invalid object name (source database) and/or database does not exist (destination database) errors :SICK: .

Of course, both databases exist and their names are valid. Both can be accessed individually thru SSMS and a VB app I am coding. The problem is when trying to work with both of them.

In this case, I am specifically trying to copy a table from one database to another. However, any information on the subject of working with multiple datatabases and/or links to said subject would be appreciated.

[Phil Weber]

You should be able to use the following SQL statements to copy data from one database to another on the same server:

-- Optional: Clear destination table
TRUNCATE TABLE destdb.owner.desttable

-- The following is required if desttable contains IDENTITY columns
SET IDENTITY_INSERT destdb.owner.desttable ON

INSERT INTO destdb.owner.desttable (col1, col2, etc)
SELECT col1, col2, etc
FROM srcdb.owner.srctable

SET IDENTITY_INSERT destdb.owner.desttable OFF

What happens when you execute those statements?

[Wiseman82]

For copying tables - you might want to take a look a SSIS. (New version of DTS)

You can query tables from other databases by fully qualifying the table name. (database.schema.object)
e.g.
SELECT * from myTable
to
SELECT * from myDatabase.dbo.myTable

INSERT INTO might do the job to copy the data. You might also be able to use SELECT INTO if you want the table to be created.

[Wiseman82]

Must improve my typing speed...

Phil's example should work fine.

[rwb]

Thanks for your quick replies. Unfortunately, I've tried these methods and am consistently stuck with the aforementioned error messages:

invalid object name (source database rawtf_1.dbo.TstTable_1) and database does not exist (destination database tf_1.dbo.TstTable_1)

I can run queries on either database with no trouble; running sp_databases lists both databases; both databases were set up by me (dbo). My problems start when I try to work with both databases as in the suggested queries.

[Wiseman82]

Are you running multiple instances? If this is the case you will need a linked server.

[rwb]

I'm just trying to run the query via SSMS. I've tried both SQL Server 2005 and the Express edition (with reboots in between). Both give the same results. I tried detaching and reattaching. I've tried deleting and recreating.

I guess a linked server is the only choice left. I've just never used it for pure SQL Server environment (just with mixed servers like Oracle, SQL Server, and MySql). There's a first time for everything :).