Post reply

Exporting data and keeping the primary key

  • January 5, 2006 at 3:45 pm

    #167573

    Hi all,

    I am totally fresh to SQL2000, but when I export data from database A to database B and I look at the copied tables in database B, all the primary keys are gone and the field is now a "normal" field?

    How can I keep the primary key in the tables when exporting the whole database ?

     

    Help is appreciated,

    Ger.

  • January 5, 2006 at 3:53 pm

    #613064

    When you say export are you talking DTS Export wizard?

    When exporting does table b exist in destination database?

    If yes, then whatever properties are on that table are not modified.

    If no, and you have dts create the table. then it just creates the base ddl, No foreign keys, primary keys, or indexes.

    Your options are to Create ddl, and export data separately, (Recommended)

    or edit the table create script in dts export wizard.

    when you select the table, it shows you the table name for the destination database. Select the ellipse button, then Edit sql button. Here you can put the Primary key hint on the column you want. It is alot of extra work this way.

     

  • January 5, 2006 at 4:30 pm

    #613074

    Thanks Ray M for answering.

    I indeed use the DTS Export wizard.

    There are no tables in database B, so there is only a base ddl copied (according your explanation)

    I donot quite understand what you mean with " Your options are to Create ddl, and export data separately" Does this mean that there is another tool than the DTS wizard ?

    Editing the table create script is a lot of work indeed. It is the same as setting primary keys in the copied files !

    So I am stuck.... Ger.

  • January 5, 2006 at 6:38 pm

    #613085

    Not quite sure if there is an easier way, but if you are using the wizard, there is an option to edit the [Create Destination Table] SQL script, where you can edit the SQL statement to include the IDENTITY property. Then ensure the [Enable identity insert] check box is selected.

  • January 6, 2006 at 3:53 am

    #613144

    Thanks Paul,

    Your solution is about the solution of Ray and it is a lot of work too. I donot understand why I am unable to copy the table-structure maintaining the primary (and eventually foreign) key(s)....

     

    Ger.

  • January 6, 2006 at 1:16 pm

    #613381

    You should create the tables in a different step.

    In enterprise manager you can use generate Sql script to generate the tables you want.

    In enterprise manager, right click on the database that has objects you want to copy, select all tasks -> Generate sql script.

    Click the show all button.

    Go throught the list and select the table (s) stored procedures views etc you want to script out.  Make sure you go to the Options  tab and select Script indexes, Triggers, and the PrimaryFKDefault Constraint boxes.

    Hit okay, and it will create a sql script that creates the objects selected. then run that script in query analyzer against your destination database.

  • January 8, 2006 at 3:56 am

    #613429

    Thanks Ray !

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

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