0

I have a primary key and identity issues when migrating databases and tables from SQL Server 2000 to SQL Server 2008.

In SQL Server 2000, some tables have primary key ID and I set Identity yes. When I export to SQL Server 2008 a few tables already do not have a PRIMARY KEY and Identity No.

I've tried the Edit Mapping in Export with check list export identity, but the result is the same, NO PRIMARY KEY and Identity NO

How to solve this problem?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Irfani Firdausy
  • 41
  • 4
  • 1
    It sounds like you're exporting/importing verbatim data between two tables with different structures. Shouldn't the tables be the same if you're "migrating" from A -> B? – ta.speot.is Jan 11 '12 at 04:44
  • What tools are you using to perform the migration? – Chris Shain Jan 11 '12 at 04:47
  • 1
    Do the destination tables have identity fields? if so, you may need to use SET IDENTITY_INSERT ON; to ensure that it temporarily allows inserting of explicit keys – John Jan 11 '12 at 04:51
  • I just export a table from sql server 2000 to sql server 2008, table names and structures are not changed. I am doing in SQL Management Studio 2008 is right click on database -> tasks -> Export Data. where can I set the SET IDENTITY_INSERT ON;? ( to John ) – Irfani Firdausy Jan 11 '12 at 06:33

1 Answers1

0

I think you did not create table on SQL Server 2008 server.

  1. Create the table schema script on SQL Server 2000 and create the same table in SQL Server 2008.

  2. Load the table with Keep Identity option in Import/Export wizard.

Hope it helps. Thanks.

GMaster9
  • 197
  • 1
  • 6