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     Licensing       EULA       Sitemap      
© Data & Object Factory, LLC.
Made with    in Austin, Texas.      Vsn 1.3.0