Setting Identity insert to ON

Thread is closed for posting
1 posts, 0 answers
  1. Marcel
    Marcel avatar
    42 posts
    Member since:
    Jan 2014

    Posted 27 Dec 2016 Link to this post


    I am creating a application that will migrate data from database 1 to database 2.
    The code for migrating the data works, but because all my tables have an autogenerated column I need to set the Identity insert to false, so all ID’s in the foreign keys match between the different tables.
    I created a stored procedure that I am calling before a particular table is migrated and that sets the identity insert to true and afterwards to false.

    I tried migrating the data, but a generated ID was still created. So I looked in the different settings and changed the BackendCalculated property of the field in the model to false. This gave me another error which lead to the change of the property Identity Mechanism to “Default” .

    But when trying to migrate now, I receive an error stating that IDENTIY_INSERT is set to off?

    I am using the following stored procedure, which I include in my model and let it create a domain function for it. I call it with the table name and either “ON” or “OFF”.

    CREATE PROCEDURE SwitchOnOffIdentityInsert
        @TableName varchar(max),
        @OnOff varchar(2)
        DECLARE @SQL varchar(max)
        SET @SQL = 'SET IDENTITY_INSERT ' + @TableName + ' ' + @OnOff
        EXEC (@SQL)

    I also tried the example supplied on this link using the App.config but this doesn’t work either?

    How can I set the indentity insert to ON so I can migrate the table data?
    I am using SQL Server 2008 R2.



Back to Top