none
Copy table from one database to another

    Question

  • Hi,

    i want to copy a table from one database to another on the same server. I know this
    works using the BCP command, but i'm looking for an easier way.

    Is it possible to do that with an T-SQL command?

    Tanks in advance

    Alex



    ------------------------------
    SQL Server 2000

    Thursday, November 29, 2007 1:52 PM

Answers

  • In SSMS or Enterprise manager you can use Import/Export Data under the Tasks sub menu

    (right click on a database and select Tasks or All tasks depending on which one you're using).

    Then follow the wizard to copy just the table(s) you want.

     

    Or in T-SQL you can:

     

    Code Block

     

    USE TargetDB

    GO

     

    CREATE TABLE dbo.TargetTable (...)

     

    INSERT INTO dbo.TargetTable (...)

    SELECT ...

    FROM SourceDB.dbo.SourceTable

     

    OR

     

     

    USE TargetDB

    GO

     

    SELECT *

    INTO dbo.TargetTable

    FROM SourceDB.dbo.SourceTable

     

     

     

     

    Thursday, November 29, 2007 1:59 PM
  • Here it is:

    SELECT * INTO targetDB.schema.table FROM sourceDB.schema.table;

    Then you need to build necessary index on target table.


    Thanks,
    Zuomin
    Thursday, November 29, 2007 2:03 PM
  • You can make use of

     

    SELECT * INTO <FULLY QUALIFIED TABLE NAME> FROM <FULLY QUALIFIED TABLE NAME>

     

    This will creates similar table schema and inserts data into that.  Typical issue with this approach is the user should have access to the both the databases.

     

    Else making use of BCP or DTS is the best option.

    Thursday, November 29, 2007 2:22 PM

All replies

  • In SSMS or Enterprise manager you can use Import/Export Data under the Tasks sub menu

    (right click on a database and select Tasks or All tasks depending on which one you're using).

    Then follow the wizard to copy just the table(s) you want.

     

    Or in T-SQL you can:

     

    Code Block

     

    USE TargetDB

    GO

     

    CREATE TABLE dbo.TargetTable (...)

     

    INSERT INTO dbo.TargetTable (...)

    SELECT ...

    FROM SourceDB.dbo.SourceTable

     

    OR

     

     

    USE TargetDB

    GO

     

    SELECT *

    INTO dbo.TargetTable

    FROM SourceDB.dbo.SourceTable

     

     

     

     

    Thursday, November 29, 2007 1:59 PM
  • Here it is:

    SELECT * INTO targetDB.schema.table FROM sourceDB.schema.table;

    Then you need to build necessary index on target table.


    Thanks,
    Zuomin
    Thursday, November 29, 2007 2:03 PM
  • You can make use of

     

    SELECT * INTO <FULLY QUALIFIED TABLE NAME> FROM <FULLY QUALIFIED TABLE NAME>

     

    This will creates similar table schema and inserts data into that.  Typical issue with this approach is the user should have access to the both the databases.

     

    Else making use of BCP or DTS is the best option.

    Thursday, November 29, 2007 2:22 PM
  • I want to personally thank you for this post. I was faced with a serious problem between the data in my active database and my backup database. The table structure is identical, the systems are identical, unfortunately I am unable to backup and restore due to inter-office  policies.

     

    Since I use SQL Express, I do not have the import/ export wizard. I used you code and everything worked like a charm... . You saved me hours of writing update statements and data entry!

    Friday, June 13, 2008 4:03 PM
  • But how to copy table schema using t-sql?

     

    thank you in advance,

    Alfred.

    Thursday, August 28, 2008 4:37 PM
  •  

    select * into dbname.dbo.newtable from dbname.dbo.tablename where 1=0

    Thursday, August 28, 2008 4:46 PM
  • You can write the Command as : -

     

    Select * into Target_DB.Dbo.Target_Table
        From (
                  Select * from Source_DB.Dbo.Source_Table
             )alias

    Monday, July 19, 2010 8:47 AM
  • The easiest way I found to copy table schema in sql server-

    1.in SSMS right click the database name from which you want to copy schema.

    2.go to "tasks"

    3.select "Generate Scripts"

    Follow the steps,select from which database you want to create scripts.Select whatever table or stored procedure you need to copy.You can generate a script in a file or in a new query editor window.Use that script to create same table structure or stored procedure in another database........

    After that if needed we can transfer the data also using Import/Export Data..from "tasks" submenu...

    • Edited by tanmoy2308 Tuesday, March 20, 2012 11:37 AM
    • Proposed as answer by tanmoy2308 Tuesday, March 20, 2012 11:37 AM
    Tuesday, March 20, 2012 10:40 AM
  • Select * INTO dbname(TARGET db)..FA40200 from dbname(SOURCEdb)..FA40200

    I tried the above script but received

    msg 2714, Level 16, State 6, Line 1

    There is already an object name 'FA40200' in the database

    Wednesday, April 24, 2013 5:00 PM