Post reply

select * into Mydb1.schema1.table1 from mydb.schema2.table2

  • January 17, 2011 at 3:03 pm

    #232969

    Hi,

    I'm performing the below to load table1 of schema1from one database to table2 of schema2 in another database

    select * into Mydb1.schema1.table1 from Mydb2.schema2.table2

    In Table2, we have 2 columns and the 1st column has Primary key. When I use the above query, all the data is copied to table1 but the column1 is NOT showing the Primary key

    Please advice what to retain all the settings same as table1 in table2

  • January 17, 2011 at 3:09 pm

    #1274399

    SELECT ... INTO just creates the table. Nothing more. If you want constraints, indexes, defaults or anything like that you'll have to add them afterwards, or explicitly create the table then insert.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 17, 2011 at 4:03 pm

    #1274414

    SELECT ... INTO just creates the table. Nothing more. If you want constraints, indexes, defaults or anything like that you'll have to add them afterwards, or explicitly create the table then insert.

    Its also copying all the data.

    Is there a way to script out a table in one database with all settings (constraints, indexes, defaults or anything like that) intact and then insert the data in a table in another database?

    Thanks

  • January 17, 2011 at 10:45 pm

    #1274481

    There's no command to do that. Script out the pk and the other stuff using management studio, then use CREATE TABLE and INSERT INTO rather than SELECT INTO.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 20, 2011 at 11:42 am

    #1276167

    There's no command to do that. Script out the pk and the other stuff using management studio, then use CREATE TABLE and INSERT INTO rather than SELECT INTO.

    Thanks Gail,

    I have taken the table script from Table1 in Mydb1 and ran that script in database Mydb2 and the table2 got created (with all Primary key, index etc).

    But I need transfer all the data from Table1 of database mydb1 in Table2 of Mydb2.

    Please advice

  • January 20, 2011 at 11:47 am

    #1276171

    INSERT INTO <Destination Table> (<Column List>)

    SELECT <Column List>

    FROM <Source Table>

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply