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

Question

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

User · Answer

Try this  INSERT INTO dbo DB1 TempTable      COLUMNS      SELECT COLUMNS IN SAME ORDER FROM dbo DB2 TempTable   This will only fail if an item in dbo DB2 TempTable is in already in dbo DB1 TempTable

User · Answer

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

User · Answer

It s db1 dbo TempTable and db2 dbo TempTable  The four-part naming scheme goes   ServerName DatabaseName Schema Object

User · Answer

This works successfully   INSERT INTO DestinationDB dbo DestinationTable  col1 col1   SELECT Src-col1 Src-col2 FROM SourceDB dbo SourceTable

User · Answer

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

User · Answer

Don t forget to insert SET IDENTITY INSERT MobileApplication1 ON to the top  else you will get an error  This is for SQL Server  SET IDENTITY INSERT MOB MobileApplication1 ON INSERT INTO  SERVER1  DB MOB MobileApplication1 m        m MobileApplicationDetailId         m MobilePlatformId  SELECT ma MobileApplicationId         ma MobilePlatformId  FROM  SERVER2  DB MOB MobileApplication2 ma

User · Answer

INSERT INTO DB1 dbo TempTable SELECT   FROM DB2 dbo TempTable   If we use this query it will return Primary key error     So better to choose which columns need to be moved   like  INSERT INTO db1 dbo TempTable     List of columns here  SELECT  Same list of columns here  FROM db2 dbo TempTable

User · Answer

Im prefer this one   INSERT INTO  DB NAME    SELECT   from  DB NAME DB LINK   MINUS   SELECT   FROM  DB NAME      Which means will insert whatsoever that not included on DB NAME but included at DB NAME DB LINK  Hope this help

User · Answer

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

User · Answer

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

[sql] Copy Data from a table in one Database to another separate database

Examples related to sql

Examples related to sql-server