Transfer of Data from 2000 to 2005

Question

Transfer of Data from 2000 to 2005

Arfat Chougulay

SSC Enthusiast

Points: 178
More actions
July 17, 2006 at 8:26 am

#169113

Hi Folks,
I am having a database on SQL Server 2000,
I want to get this database on SQL Server 2005,
To do this we have our Create table scripts of our database, and Alter Table Script to apply the constraints like Foreign key and unique key which we run after creating the table.
I run the create table script which created the table on SQL 2005,
I use the import Export tool from SQL 2005 to copy the data from SQL 2000 to new SQL 2005.
I am having a table called Customer which is having CustID column with identity(1000,1)
the Table Customer on SQL 2000 is already having custID between 1-1000 which have been kept for internal customer.
but when i copy the data from 2000 to 2005 using Import Export the iD changes and new id are assigned starting from 1000 and so on.
I tried switiching on and off from enable identity while exporting.
Can any reply to this.
Best Of regards.
Arfat CHougulay

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

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

BeerBeerBeer SSCarpal Tunnel Points: 4021 More actions · Answer 1

You could try to import the customers to a working table - with no identity column just use an INT to store the existing id.

Then you write a query to insert them into the real customer table by selecting them in order of their existing customer id

insert customer

(columns without identity column)

select

(columns without identity column)

from working_customer

order by id

The only problme with this is if you have gaps in your data - that is if you have deleted customers.

You can however choose to fill those gaps with dummy data in your working table which you can then remove from the customer table.

Regards,

Steve

Life without beer is no life at all

All beer is good, some beers are just better than others

Arfat Chougulay SSC Enthusiast Points: 178 More actions · Answer 2

Thanks Steve,

But its not just customer table,

Even my orders,ordersdetail and more tables are having identity column.

and I want a better to way or any idea if identity column can be disabled while importing the data.

This problem does not persist in SQL 200 DTS,

I tried using SQL 2000 DTS to export the data and works fine without damaging my identity columns.

And Microsoft claims for DTS end of road on SQL 2000, with SQL 2005 Integration Service.

I am worried about when i get all my data on SQL 2005 and Dump away SQL 2000 then how the way out will be while importing and exporting data with not changing the identity columns value.

Is there a Direct way.

Thanks

Arfat CHougulay

Scott-361506 SSC Rookie Points: 31 More actions · Answer 3

Did you get a solution for this? What I have found is that when I export from 2000 to 2005 the fields and data all transfer, but the identity field of my tables doesn't copy as an identity field. (Auto Increment) I have to manually go into each table in 2005 and set it up to get the tables set properly. Ideally this should work during the export from 2000 to 2005. If I so an export from 2000 to 2000 everything transfers fine. Any help would be appreciated!

Thanks.

Scott

Arfat Chougulay SSC Enthusiast Points: 178 More actions · Answer 4

The only best solution which i found through few R & D is to make backup of my database in SQL 2000 and Restore it on SQL 2005. which does not change my identity column values in new one.

Your problems seems to be new, but what i guess in your case is that you are getting the original values but your column does not turns to identity filed.

What you could try is to create the table first with identity column and then try to export / import the data, then only pitfall would be the same problem which i face loss of original identity values.

TRY!!!!!!

Good Luck!!!!!

Let me know if it succedds.

Arfat....