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?

Solution 1:

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

Solution 2:

SELECT * INTO requires that the destination table not exist.

Try this.

INSERT INTO db1.dbo.TempTable
 (List of columns here)
SELECT (Same list of columns here)
FROM db2.dbo.TempTable

Solution 3:

It's db1.dbo.TempTable and db2.dbo.TempTable

The four-part naming scheme goes:

ServerName.DatabaseName.Schema.Object

Solution 4:

Hard to say without any idea what you mean by "it didn't work." There are a whole lot of things that can go wrong and any advice we give in troubleshooting one of those paths may lead you further and further from finding a solution, which may be really simple.

Here's a something I would look for though,

Identity Insert must be on on the table you are importing into if that table contains an identity field and you are manually supplying it. Identity Insert can also only be enabled for 1 table at a time in a database, so you must remember to enable it for the table, then disable it immediately after you are done importing.

Also, try listing out all your fields

INSERT INTO db1.user.MyTable (Col1, Col2, Col3)
SELECT Col1, COl2, Col3 FROM db2.user.MyTable

Solution 5:

We can three part naming like database_name..object_name

The below query will create the table into our database(with out constraints)

SELECT * 
INTO DestinationDB..MyDestinationTable 
FROM SourceDB..MySourceTable 

Alternatively you could:

INSERT INTO DestinationDB..MyDestinationTable 
SELECT * FROM SourceDB..MySourceTable

If your destination table exists and is empty.