June 20, 2005 at 7:07 pm
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.
June 20, 2005 at 7:45 pm
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
*/
June 20, 2005 at 8:25 pm
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
June 21, 2005 at 8:47 am
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
June 21, 2005 at 9:00 am
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
June 21, 2005 at 5:03 pm
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