import (replace data) in Table

Question

import (replace data) in Table

potucekj

Default port

Points: 1414
More actions
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.

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

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

David Burrows SSC Guru Points: 65143 More actions · Answer 1

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.

potucekj Default port Points: 1414 More actions · Answer 2

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

David Burrows SSC Guru Points: 65143 More actions · Answer 3

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.

Graeme100 SSCrazy Eights Points: 8054 More actions · Answer 4

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

potucekj Default port Points: 1414 More actions · Answer 5

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.

David Burrows SSC Guru Points: 65143 More actions · Answer 6

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

will this preserve my indexes in the original?

Yes.

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

potucekj Default port Points: 1414 More actions · Answer 7

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.