How to copy identity column in Enterprise Manager

Question

Post reply

How to copy identity column in Enterprise Manager

Tariq Changgez

Old Hand

Points: 399
More actions
December 19, 2004 at 8:29 am

#163396

Whenever I export a table to my local database using Enterprise Manager it removes the Identity from the column and shows it as a normal column. I always have to go into Design mode and change it to an identity column again.
Is there a way to force Enterprise Manger to export the table along with its identities.

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

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

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

When you say a 'local' database, do you mean SQL Server, or something else (eg Access)?

How are you performing the export?

I tried this using the DTS export table wizard and found the same thing as you. To get round it, I did the following:

1) First, ensure that the table design is created correctly on your local system (ie with all identity and primary key constraints in place). If you are repeatedly exporting the same table, you should need to do this only once.

2) Perform a truncate on your local table if required, to remove existing records before importing new ones.

3) In the DTS package which is performing the export, ensure that Enable identity insert is checked (under the Options tab).

Regards

Phil

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Tariq Changgez Old Hand Points: 399 More actions · Answer 2

I was never truncating a table rather used to delete it with the understanding that since Export DTS Wizard creates a new table why should I bother to truncate it.

Now upon your suggestion I truncated it rather than deleting it, and then exported a table. It worked fine

Thank you Phil.

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

Here's what you can do if you ever want DTS to create a table before importing:

After selecting the source table, click the Transform elipse to get to Column Mappings and Transformations screen. Check 'Create destination table' then click the 'Edit SQL' button to see the CREATE TABLE script. Add the identity property to the correct column and you're in business! You still have to ensure that 'Enable identity insert' is checked.

Greg

Tariq Changgez Old Hand Points: 399 More actions · Answer 4

I tried as suggested by you and it worked. Frankly speaking, the Transform elipse as mentioned in your reply, is normally hidden too far right on the form and I never clicked at it. It contains wealth of customization for export purpose.

Thanks for your help