Post reply

import (replace data) in Table

  • September 30, 2004 at 6:03 am

    #86673

    I have a flat file that I need to import into an existing table and replace the data in the table.

    I don't have a problem importing the data into the table, but

    Since it's production, I wanted to make a copy of the table first. What I did was rename my existing table and then use import/export (DTS) to recreate the original table. seems to work fine but I lose my Primary Key setting!!?? not sure what I'm doing wrong? Can I just set the primary key again on the new table and be on my way? When I do that, it seems to reorganize the data. sorry for my ignorance and thank you for your patience.

  • September 30, 2004 at 6:19 am

    #524899

    When you renamed the table the priamry key will remain with the table and have the same name when it was created (either by the system or by you). Names of primary keys have to be unique in a database.

    You can add a new primary key when you create the table or drop it before renaming the table if you want the primary key on the new table to have the same name.

    If the primary key is CLUSTERED then the data is stored (or reorganised) in the order of the key.

    An alternative is to create the dest table, copy the data from the source table to the dest table and delete the data from the source table.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • September 30, 2004 at 7:51 am

    #524917

    soo... what I'm seeing as the Index Name is what has to be unique in the Database?

  • September 30, 2004 at 7:56 am

    #524918

    Yes Index Names have to be unique in a database and a Primary Key is an Index.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • October 5, 2004 at 3:06 am

    #525338

    Hi,

    Instead of renaming the table, why not create a second table to store the copied data.

    So you would copy the data first, truncate the original table and then let the DTS import into the empty table.

    Regards

    Graeme

  • October 5, 2004 at 4:49 am

    #525353

    so... export (dts) the table data to a new (temp) table and then truncate the original before importing (dts) my flat file into the original. I will try this on the DEV box in a little while... will this preserve my indexes in the original? Thank you for your help and patience.

  • October 5, 2004 at 5:14 am

    #525354

    quote export (dts) the table data to a new (temp) table and then truncate the original before importing (dts) my flat file into the original

    '(temp) table' Needs to be a permanent table if you want to retain the data

    quote

    will this preserve my indexes in the original?

    Yes.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • October 5, 2004 at 6:05 am

    #525362

    Thank you so much! why I didn't think to truncate the table to preserve my indexes I have no idea.. I'm pretty new at this. oh well, maybe time to throw in the towel and open that donut shop I've always dreamed of ... thanks again.

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

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