Sql – Copy Data from a table in one Database to another separate database


Basically I have a two databases on SQL Server 2005.

I want to take the table data from one database and copy it to another database's table.

I tried this:

SELECT * INTO dbo.DB1.TempTable FROM dbo.DB2.TempTable

This didn't work.

I don't want to use a restore to avoid data loss…

Any ideas?

Best Solution

SELECT ... INTO creates a new table. You'll need to use INSERT. Also, you have the database and owner names reversed.

INSERT INTO DB1.dbo.TempTable
SELECT * FROM DB2.dbo.TempTable