SQL Server Express
I found the solution with:
INSERT INTO dbName.ScemaName.TableName (column1, column2....)
SELECT column1, column2...
I just accomplished this on SQL Server 2000. I figure the SQL should be the same.
Given two databases named: SourceDB and TargetDB
SourceDB has a table SourceTable
TargetDB has a table TargetTable
The tables have some compatible columns you want to copy.
USE SourceDB -- not needed if already in the source database's context
INSERT INTO TargetDB.dbo.TargetTable (ColumnID, SomeColumn1, SomeColumn2)
SELECT ColumnID, SomeColumn1, SomeColumn2
I figure it might be possible to use SELECT * but if the number of columns don't match, I got an error. It was easy enough to explicitly specify what I wanted.
It's quite possible the schema 'dbo' doesn't work in all circumstances, but I figure if people are asking (like I was), we must be fairly novice and haven't set up complex databases, yet. (Hope this helps).