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

Hi,
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.

Thanks!

Lauren Kruglak, Nov 17, 2014

Reply 1

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;";

db.Execute(sql);

Hope this helps.
Jack

Jack Poorte, Nov 21, 2014