How to Insert Identity Manually

How can I insert identity manually?

In it's simplest form, you need to temporarily allow the insertion of identity values

SET IDENTITY_INSERT masterTbl ON
INSERT INTO masterTbl (id, name) VALUES (1, 'MNO')
SET IDENTITY_INSERT masterTbl OFF

Insert values into identity column manually and automatically in one query

Some simulation identity insertion:

CREATE TABLE #Test (Id int IDENTITY(1,1) PRIMARY KEY, Value int)

INSERT INTO #Test 
VALUES
(100),
(200)

SELECT *
FROM #Test AS t

DECLARE @Temp TABLE (Id int, Value int, SetId bit)
INSERT INTO @Temp 
VALUES
(-1, 1,1),
(-2, 2,1),
(-1, 33,0),
(-1, 44,0)

SET IDENTITY_INSERT #Test ON

INSERT INTO #Test 
(Id, Value)
SELECT
    CASE(t.SetId)
        WHEN 1 THEN t.Id --just insert new Id
        WHEN 0 THEN 
            IDENT_CURRENT('#Test') --get last identity value
            + ROW_NUMBER() OVER(ORDER BY t.Id) --simulate identity +1
            - (SELECT COUNT(*) FROM @Temp AS t2 WHERE t2.SetId = 1) --skip inserted rows with Id
            + 1
    END,
    t.[Value]
FROM @Temp AS t

SET IDENTITY_INSERT #Test OFF

SELECT *
FROM #Test AS t

DROP TABLE #Test

What is the appropriate way to insert identity column manually?

Ah I see what you want now.

INSERT INTO TCustomer
    (UnitHolderIDNo,FullName,ExternalUnitHolder)
SELECT
    UnitHolderIDNo = STC.UnitHolderIDNo
    ,FullName = STC.FullName2
    ,ExternalUnitHolder = STC.ExternalUnitHolder
FROM
    SIAR.dbo.TCustomer STC

That's it. Just don't specify the auto incrementing column and let it create the ID for you. You can add ORDER BY UnitHolderIDNo if you need to.

How to turn IDENTITY_INSERT on and off using SQL Server 2008?

Via SQL as per MSDN

SET IDENTITY_INSERT sometableWithIdentity ON

INSERT INTO sometableWithIdentity 
    (IdentityColumn, col2, col3, ...)
VALUES 
    (AnIdentityValue, col2value, col3value, ...)

SET IDENTITY_INSERT sometableWithIdentity OFF

The complete error message tells you exactly what is wrong...

Cannot insert explicit value for identity column in table 'sometableWithIdentity' when IDENTITY_INSERT is set to OFF.

How do I MANUALLY set an Identity field in LINQ-To-SQL (IDENTITY INSERT)

Take a look here: http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/566e9540-911e-48b4-ac31-f69c0ab9f7fb/

Last reply here: http://forums.asp.net/t/1208607.aspx

How can I force entity framework to insert identity columns?

After careful consideration, I've decided that entity framework's refusal to insert identity columns is a feature, not a bug. :-) If I were to be inserting all entries in my database including their identity values, I'd also have to create an entity for every link table that entity framework had created automatically for me! It's just not the right approach.

So what I'm doing is setting up seeding classes that just use C# code and create EF entities, then use a DbContext to save the newly-created data. It takes a bit longer to take the dumped SQL and turn it into C# code, but there isn't (and shouldn't be) too much data just for "seeding" data - it should be a smallish amount of data which is representative of the kind of data that would be in a live DB that can quickly be put into a fresh DB for debugging/development purposes. This does mean that if I want to link entities together, I do have to do queries on what has already been inserted or my code wouldn't know their generated identity value, eg. This kind of thing will appear within the seeding code, after I have set up and done context.SaveChanges for MyRoles:

var roleBasic = context.MyRoles.Where(rl => rl.Name == "Basic").First();
var roleAdmin = context.MyRoles.Where(rl => rl.Name == "Admin").First();
var roleContentAuthor = context.MyRoles.Where(rl => rl.Name == "ContentAuthor").First();

MyUser thisUser = context.MyUsers.Add(new MyUser {
    Salutation = "Mrs", Firstname = "Novelette", Surname = "Aldred", Email = null, LoginUsername = "naldred", Password="c1c966821b68fdf129c46de949b9f7e0d03f6cad8ea404066f4f3a75e11514748ac9f68695c2128e520ca0275cd711df", IsEnabled = true, SpecialRequirements = null
});
thisUser.Roles.Add(roleBasic);

Doing it this way also makes it more likely I will update my seeding data when I change the schema, because I will likely break the seeding code when I change it (if I remove a field or entity, the existing seeding code that uses that field/entity will fail to compile). With a SQL script for doing seeding, that wouldn't be the case, and nor would the SQL script be database-agnostic.

So I think that if you're trying to set the identity fields of entities for doing DB seeding data, you've definitely taken the wrong approach.

If I were actually dragging a load of data from say SQL Server to PostgreSQL (a full live DB, not just some seeding data), I could do it via EF, but I'd want to have two contexts open at the same time, and write some code to grab all the various entities from the source context and put them into the destination context, then save changes.

Generally, the only time it's appropriate to insert identity values is when you're copying from one DB to another DB within the same DBMS (SQL Server -> SQL Server, PostgreSQL -> PostgreSQL, etc.), and then you'd do it in a SQL script and not EF code-first (the SQL script wouldn't be DB-agnostic, but it wouldn't need to be; you're not going between different DBMSs).

How to Insert into Identity column with c# code

So after some conversation (big shoutout to @SteveTodd) i managed to get what i wanted.

Explanation: I wanted to insert my own primary keys (for various reasons). I did know that you could turn off the Identity temporarily, but the solution is to basically turn it off entirely.

Another Question that helped me solve and understand this problem:

Entering keys manually with Entity Framework

In my case it now looks like this:

public class myObject
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.None)]
    int TokenId { get; set; }
}

TLDR: Add this between the [Key] and your Field:

[DatabaseGeneratedAttribute(DatabaseGeneratedOption.None)]

---

Related Topics