IDENTITY_INSERT IN CURSOR

  • Hi All,

    I am trying to populate a bunch of tables with some default values. Each table has an identity column. I want to loop through each table in a cursor, build up the strings to:

    a. SET IDENTITY_INSERT ON

    b. populate the table

    c. SET IDENTITY_INSERT OFF

    When running this, i get the following error:

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

    I am using the EXEC('string') to execute the queries I am building up. However, when I use PRINT('string') and then cut an paste this into QA, it works fine.

    Is there an issue with setting an IDENTITY_INSERT from within a cursor?

    An example of my print output is:

    SET IDENTITY_INSERT TABLEA ON

    INSERT INTO TABLEA(ColA, ColB, ColC) VALUES (10, -1, '!U')

    INSERT INTO TABLEA(ColA, ColB, ColC) VALUES (10, -2, '*NA')

    SET IDENTITY_INSERT TABLEA OFF

    Where ColB is the identity column, and all columns are specified in the INSERT.

    Any help would be greatly appreciated.

  • Hi could you post your table def. I am not sure what you are trying to do here. If you are just adding rows to a table that contain an identity col you do not need to set that value.

    HTH Mike

    if object_id('tempdb..test')>0

    drop table test

    create table test

    (

     col1 int,

     col2 int identity(-1,-1),

     col3 char(3)

    )

    go

    insert into test values(1,'u')

    insert into test values(-2,'*na')

    select * from test

    /*

    returns

    1 -1 u 

    -2 -2 *na

    */

  • Thanks Michael,

    I have nutted it out this far, try this example.

    DROP TABLE tabA

    CREATE TABLE tabA (

    colA smallint NOT NULL,

    colB INT IDENTITY(1,1)

    )

    EXEC('SET IDENTITY_INSERT tabA ON')

    EXEC('INSERT INTO tabA(colA, colB) VALUES (-2, -1)')

    EXEC('SET IDENTITY_INSERT tabA OFF')

    EXEC('SET IDENTITY_INSERT tabA ON

    INSERT INTO tabA(colA, colB) VALUES (-2, -1)

    SET IDENTITY_INSERT tabA OFF')

    select * from tabA

    DROP TABLE tabA

    In this case, the first set of EXEC's doesnt work, but the second one does. Any thoughts?

    Cheers

  • While I am not 100% on this I think it is a scoping problem in the first example. Three questions. First since the second example works is there a problem I am not seeing? Second in your example you are setting the identity to a negative number but have defined your table in a way that will add new rows with positive numbers in the identity col. I realize that this is just test data but I am confused as to why you would want to insert a row with a negative value when all the other rows have positive identity numbers. Third the purpose of setting IDENTITY_INSERT property to ON is to allow you to fill in missing values in your identity col. Is this your intention? If not then a simple insert will add new rows with the correct identity col value.

    HTH Mike

  • When you run the 1st Case  you actually CLOSED the connection before the second EXEC.

    In the 2nd instance ALL SQL is executed as mentioned above within the SAME SCOPE!

    hth

     


    * Noel

  • Thanks Noel, that has cleared up the problem for me.

    Michael, we have two rows (default values) that are in every table, we decided to give these an ID of -1 and -2 to easily identify them.

    Many thanks for your help

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply