Identity column not exporting from 200 to 2008

lobos
lobos used Ask the Experts™
on
I did an export of data from 2000 to 2008, all the tables and data copied
over with the exception of the Identity attribute.  (which is the auto number
feature).
This is not good, I tried to manually do it and it says that I need to drop
and recreate table. This poses an issue given all data I have in the table
and that needs to be imported over.

Within SQL 2008 SQL Server Management Studio, I did a right click on the
database and Tasks, then either Import or Export works to bring the stuff
over...but the Identity attribute does not carry over.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
you have to manually assign identity column the error you are facing could be resolved by checking of one thing, see attached image.

that dilog box could be open from tools->option from your SSMS

ee.JPG
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
When you export data from table with identity column, you need to Enable Identity Insert (it's a check box in Edit Mappings option when in Export Wizard you are select tables to export).

Author

Commented:
Thats great, I didnt know about the setting, I tried it...but it doesnt seem to have worked?

c.gif
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Gave you error during export?

Author

Commented:
I got no error, just didn't do it I guess?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
If no error then should did it.
You verified data after?

Author

Commented:
the data carried over, the properties of the column for identity is set to no?
and when I tried to add a record (and not put a value in that column), it says I can't add a null to that column
basically when you export table from 2000 to 2008, it doesn't transfer identity so you have to do it manually but the setting I saw prevent restructure of table and we have to check it off and than it should work.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
lobos, you need to set identity if the new table.

Author

Commented:
what you do you mean I need to set the identity of the new table? isnt that what the checkbox if for?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Sorry, maybe I didn't explained well.
That checkbox is for disable identity in the target table so it can accept values in identity column. Now you need to enable it again. Try SET IDENTITY_INSERT table_name OFF or use SSMS to view table definition and column properties.

Author

Commented:
I'm still a bit confused, at what point and where do you do the...
Try SET IDENTITY_INSERT table_name OFF or use SSMS to view table definition and column properties.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
SSMS it's SQL Server Management Studio.
You can run there the statement SET IDENTITY_INSERT table_name OFF (New Query button and choose the database) or go directly to database / table, right-click and choose Design option.

Author

Commented:
Ok, i can do this, but are you saying during the actual wizard process, do I check, or leave it cleared (the checkbox).

Then once that process is done, on the destination table I run this command you posted above?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Yes, only after process ends.

Author

Commented:
You only answered one part of the question...

>>>Ok, i can do this, but are you saying during the actual wizard process, do I check, or leave it cleared (the checkbox).
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Sorry. You should check it every time you want to export data to an identity column.

Author

Commented:
I tried as you suggested and got the following error...

Msg 8106, Level 16, State 1, Line 1
Table 'vacinations_form' does not have the identity property. Cannot perform SET operation.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Now it's me that is confused!
If you don't have a identity property in the table then you don't have any issue.

Author

Commented:
Your confused?
The source has identity
The destination does not.
Even after running the export wizard checking the checkbox as you said, then after the export the destination table  STILL DOES NOT have the idenity on.

Am I not explaining something correctly?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
But if destination doesn't have any identity column of course won't have identity on. That's only applies for tables with a identity column.

Author

Commented:
That's my point (the reason for this post), the when I do the export everything is fine EXCEPT the identity column does not transfer over to the destination table.
IT Engineer
Distinguished Expert 2017
Commented:
Sorry lobos, I missunderstood your question.
For that you need to click in "Edit SQL..." button and add the text "IDENTITY(1,1)" after the column name you want to be identity.

Cheers

Author

Commented:
Phew....It works...thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial