Setting Identity insert to ON

[Marcel]

Hello,

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)

AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL varchar(max)

    SET @SQL = 'SET IDENTITY_INSERT ' + @TableName + ' ' + @OnOff
    EXEC (@SQL)
END
GO

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.

Regards,

Marcel