I am currently developing a java application to migrate data of an Oracle database to an equivalent structured SQL Server database which I have migrated using liquibase. Now I could not figure out how to migrate the auto generated IDs as well since it is something which is not accessible so easy. I want them to have the exactly same values as in my source database and I want them to be ongoing so when the last auto generated ID was 177 für example I want the next generated on to be 178. I know that this is a vague question but currently I am just thinking about a concept to apply in my code so any suggestions are welcome. Thank you in advance!
The concept is to move data from Oracle to SQL Server as-is using SSIS/linked server/Polybase/bcp:
INSERT INTO SQL_Server_table(id, ...)
SELECT id, ...
FROM Oracle_table;
If table has IDENTITY property then you have to disable it first:
SET IDENTITY_INSERT SQL_Server_table ON
Once you have data moved you need to set identity to highest value:
DBCC CHECKIDENT ('SQL_Server_table', RESEED, <max_calculated_value>+1);
If the table does not have identity column defined you could use SEQUENCE:
CREATE SEQUENCE dbo.SQL_Server_table_seq
START WITH <max_calculated_value>+1
INCREMENT BY 1 ;
ALTER TABLE SQL_Server_table
ADD CONSTRAINT df_SQL_Server_table_seq
DEFAULT NEXT VALUE FOR dbo.SQL_Server_table_seq FOR [<identity_column>];