SQL IDENTITY_INSERT

SQL IDENTITY_INSERT is allow values to be inserted into the identity column of a table.
Identity column creates a numeric sequence value for you automatic.

It only alow one table in a session can have the IDENTITY_INSERT property set to ON.
If you need to change the table, you need to set the existing table IDENTITY_INSERT back to OFF.

SET IDENTITY_INSERT TableName OFF

If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

SET IDENTITY_INSERT TableName ON

SQL Get Rows Number

Below SQL Statement return rows number for each row in a partition.

select R1.CategoryName,
rank = (select count(distinct R2.CategoryName) from categories R2 where R1.CategoryName <= R2.CategoryName) from categories R1 Order by Rank

Below is the result after use the row number sql statement: