Back to list
Views:   5.4K
Replies:  1

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.

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


Hope this helps.
Jack Poorte, Nov 21, 2014
Stay Inspired!
Join other developers and designers who have already signed up for our mailing list.
Terms     Privacy     Cookies       Do Not Sell       Licensing      
Made with    in Austin, Texas.  - vsn 44.0.0
© Data & Object Factory, LLC.