Post reply

*****Table Structure and Data ******

  • September 13, 2010 at 3:04 pm

    #238061

    Hi All,

    I have a XYZ Database (50 Tables init and lots of data ) on 01 Server

    and

    I need to create a XYZ_MOCK Database on same Server 01 with 20 tables of XYZ Similarly with the exact data ....

    (((for example :

    table 'tyu' having FK with 'pot'

    so i need to have table 'tyu 'and 'pot' and also Structure should be same ))))

    I need to get data so i used

    Select * into XYZ..Destination table from XYZ_MOCK..Source Table

    (Note:not to use import export ,or DTS import/Export because we need Scripts )

    I got the Structure,data similar and exactly same as in XYZ to XYZ_MOCK

    ....But,

    PK and FK are missing (SP,Views,Triggers also)...doesnt Show up

    I thought of running an alter table and adding PK and FK and FK related tables..and just copy and paste SP's ,Triggers ...but i donno to do this way or not ....

    can you guys please help me on it ...

    And is there any Script to say that table 'qwe' is having FK relatinoship to or with table 'yu'...

    So that i can directly add a FK related table in XYZ_MOCK DB...

    Thanks

  • September 13, 2010 at 3:18 pm

    #1220323

    Hi Nari

    I'm not really sure what your asking, but I think you might just need to back up XYZ and restore as XYZ_MOCK

    MCITP SQL Server 2005/2008 DBA/DBD

  • September 13, 2010 at 3:23 pm

    #1220327

    If it were me, I'd script it all out. From what I've read, I'd suggest you use the Import and Export Wizzard to copy the required tables. You have the option to include data or not, and there will be options asking what related objects like indexes, keys, or triggers you want to copy. Prior to clicking the Finish button, you can save the configured export task to an SSIS package, so it can be re-run again at a later time if needed.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • September 13, 2010 at 3:33 pm

    #1220334

    Thanks for the answers .I do appreciate that

    But If i do backup and restore ...i will be having all 50 tables ...

    but i need only 20 tables ..Structure Shud be same and also the data in selected 20 tables ...

    and i tried using Export ..i having problems with Readonly columns ..So i must use Scripts i guess

  • September 13, 2010 at 3:40 pm

    #1220338

    What do you mean by ReadOnly columns?

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • September 13, 2010 at 4:59 pm

    #1220369

    I am Sorry ......

    my requirement changed

    They asked me to create a XYZ_MOCK database so creatde that and I did Copied Scripts From XYZ and ran it in XYZ_MOCK database...now tables Structues are same for 20 selected tables...

    Now there are 20 tables with lots of lots of Records in it ......

    All i want is now I need to move all the records from XYZ DB to XYZ_MOCK. DB (20 Selected Tables)..

    for ex:

    From

    Server01-XYZ Database -table abc (20columns and 1000rows)

    TO

    Server01 - XYZ_MOCK Database table abc(20 columns and 100rows)....

    How can i Insert all records for 20 tables so that my Selected tables in Xyz and XYZ_MOCK lokks same

    Thanks

  • September 13, 2010 at 5:45 pm

    #1220377

    There are a couple of ways you can do this.

    1. BCP the data out of one system into the other

    2. Use Microsoft's import tool (which will let you work across instances)

    3. Roll your own scripts along the lines of

    INSERT <TableA_Mock>

    SELECT * FROM <Production>..<TableA>

  • September 16, 2010 at 5:34 am

    #1222078

    Hi

    U can also Use

    select * from XYZ into XYZ_MOCK

    Ali
    MCTS SQL Server2k8

Viewing 8 posts - 1 through 7 (of 7 total)

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