Data migration to Azure with foreign key referencing an identity column

I wish to migrate my on-premise database to azure. However, my on-premise DB has a table Table1 with column as

ID int identity(1,1) Not 开发者_JAVA百科Null,

and a column 'column1' in table 'Table2' has a foreign key constraint on column Table1.ID

I am facing a problem when my Table1.ID column does not have consecutive values.

For example, Table1.ID has values (1, 2, 4, 5, 6...)

If I insert normally into the Azure table, my Table1.ID on azure will be (1, 2, 3, 4, 5...) and jumble up my foreign key relationships. I want to prevent this jumbling up of foreign key relationships.

Any ideas on how to do this?

Before doing your inserts, enable identity-inserting for that table:

SET IDENTITY_INSERT Table1 ON

And then you can include the ID column in your inserts, so that the ID values stay the same. Later, when you insert the rows that have FK references for Table2, the ID values will be the same.

Just make sure when you are done to turn identity-insert back off when you are done:

SET IDENTITY_INSERT Table1 OFF

Consider using the SQL Azure Migration Wizard, which will take care of migrating your data for you.

The root of your specific problem, though, as @Mike Mooney suggested, is you need to use IDENTITY_INSERT.