I’m currently moving my MySql database to SQL Server and for that reason i need to preserve the identity columns which i already have in the tables. Here’s how it’s done:
SET IDENTITY_INSERT tablename ON
SET IDENTITY_INSERT tablename OFF
So to insert the row ID=12, Name=Alfred, Age=23 in tblUsers, i would use the syntax below:
SET IDENTITY_INSERT tablename ON
INSERT INTO tblUsers (ID, Name, Age) VALUES (12, ‘Alfred’, 23)
SET IDENTITY_INSERT tablename OFF
You will need to turn the identity insert off so that SQL Server automatically generates the unique id for you afterwards.