Home  /  Questions  /  Question

62   95.7
Nov 17, 2014

Insert for Id (Primary key) column without keeping it as identity

Using the SPARK framework , I need to specify custom int value during insert, for an Id column of a table, which is a primary key.

The need for this is, because Id is generated in different database and referred in multiple tables of another db , value of which is required to be kept consistent. This Id column in a database has a reference to multiple tables hence can not be created as an additional column to store the value. 

Please suggest how to.


508   99.9
Nov 21, 2014
Hello Lauren:

Yes, it is possible to forcibly insert a particular identity value.
However, I am not sure how your table is setup and how you avoid interference with other identity values.

You simple build out your SQL statement and surround it by two SET IDENTITY_INSERT settings. 
Below we are adding a student with Id = 812.

var sql = @"INSERT INTO Student(Id, FirstName, LastName) VALUES (812, 'John', 'Smith')";

sql = @"SET IDENTITY_INSERT [Document] ON;" + sql + @";SET IDENTITY_INSERT [Document] OFF;";


Hope this helps.