locked
Sql 2008 R2 - copy db1.tableA to db2.TableA RRS feed

  • Question

  • Hi,

    I´m new with sql I´m here to ask the experts. I created another DB with some identical tables from another (another project from the begin of last year).

    Is there a way to copy the contents from DB1.TABLEA to DB2.TABLEB ? Or should I export to .CSV from old db/table and then import to the new db??



    tks, Renato P

    • Moved by Tom Phillips Monday, April 2, 2012 2:20 PM TSQL question (From:SQL Server Database Engine)
    Saturday, March 31, 2012 6:32 PM

Answers

  • Hi Manish,

    tks for quick reply. I solved after provide the field names, now worked fine!

    USE DB1
    SELECT * FROM tbl_Main_Country
    
    USE DB2
    SET IDENTITY_INSERT DB2.DBO.tbl_Main_Country ON 
    INSERT INTO tbl_Main_Country (id,Country_Name) SELECT * FROM DB1.DBO.tbl_Main_Country
    SET IDENTITY_INSERT DB2.DBO.tbl_Main_Country off


    tks, Renato P


    • Edited by jr3151006 Saturday, March 31, 2012 7:09 PM CORRECTION
    • Marked as answer by jr3151006 Saturday, March 31, 2012 7:18 PM
    Saturday, March 31, 2012 7:08 PM

All replies

  • Try 

       
    CREATE DATABASE A
    GO
    USE A
    GO
    CREATE TABLE ATABLE(A INT)
    INSERT INTO ATABLE VALUES(8),(9)
    GO
    SELECT * FROM ATABLE
    
    CREATE DATABASE B
    GO
    USE B
    CREATE TABLE BTABLE(A INT)
    SELECT * FROM BTABLE
    GO
    INSERT INTO BTABLE SELECT * FROM A.DBO.ATABLE


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Saturday, March 31, 2012 6:41 PM
  • Hi Manish,

    since I already have the tables, I tried to adapt your script to the following

    USE DB1
    SELECT * FROM tbl_Main_Country
    
    USE DB2
    INSERT INTO tbl_Main_Country SELECT * FROM DB1.DBO.tbl_Main_Country

    but no success since appear the following msg:

    Msg 8101, Level 16, State 1, Line 5
    An explicit value for the identity column in table 'tbl_Main_Country' can only be specified when a column list is used and IDENTITY_INSERT is ON.

    What happened??


    tks, Renato P

    Saturday, March 31, 2012 6:54 PM
  • Hi Manish,

    tks for quick reply. I solved after provide the field names, now worked fine!

    USE DB1
    SELECT * FROM tbl_Main_Country
    
    USE DB2
    SET IDENTITY_INSERT DB2.DBO.tbl_Main_Country ON 
    INSERT INTO tbl_Main_Country (id,Country_Name) SELECT * FROM DB1.DBO.tbl_Main_Country
    SET IDENTITY_INSERT DB2.DBO.tbl_Main_Country off


    tks, Renato P


    • Edited by jr3151006 Saturday, March 31, 2012 7:09 PM CORRECTION
    • Marked as answer by jr3151006 Saturday, March 31, 2012 7:18 PM
    Saturday, March 31, 2012 7:08 PM