how to move table between databases

Posted on 2006-05-19
Last Modified: 2008-02-01

I have database1 , which contains two table ... table1 and table2..
I created database2 on same server and I want to move table2 in database2. how can I do that? Any script or DTS is the only way?
Please guide me.

Question by:pinaldave
    LVL 142

    Accepted Solution

    you can copy a table by getting the script generated for the structure, with which you create the table on the second database.
    to copy the data:

    INSERT INTO database2.dbo.table2
    SELECT * FROM database1.dbo.table2

    if you want to do a move, drop the table once you done the copy

    in DTS, you can use the transfer objects scripts
    LVL 5

    Assisted Solution

    would this statement work?  I've never tried this solution and don't have access to a SQL server right now.  My guess is it would work but the columns may not be the same datatype between the two tables.

    SELECT * INTO database2.dbo.table2
    FROM database1.dbo.table1
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    yes it would work, the data types will be copied
    LVL 28

    Assisted Solution

    hi  pinaldave,

    you can use this

    Select * into from Database1.dbo.table2
    LVL 22

    Author Comment

    Thank you all. I end up using DTS :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now