May 18, 2007 at 4:30 am
Here's an odd one. I did a select...into statement to create a new table yesterday. Today I found a few more rows I wanted to insert, so I created an Insert statement. When I try and run the insert I get the following error:
Msg 544, Level 16, State 1, Line 4
Cannot insert explicit value for identity column in table 'SCR17434MasterPolicy_BadLenderIDs' when IDENTITY_INSERT is set to OFF.
HUH? This doesn't make sense to me since I never added any indexes, constraints, defaults or anything to the table I created. I've never run into this problem before. Below is the original Select..Into and the Insert stmt. Can someone help me figure out what the issue is? This is SQL 2005 SP1.
Select Distinct LID, PolID, PolNumber, BatchNumber, LastName, FirstName, ModifiedBy, ModifiedOn
into DB2.dbo.MyTable2
from MyTable1
where LID in (7520, 2996, 3329, 8832, 3033, 3740, 3132)
Order by LID
Insert into DB2.dbo.MyTable2(LID, PolID, PolNumber, BatchNumber, LastName, FirstName, ModifiedBy, ModifiedOn)
(Select Distinct LID, PolID, PolNumber, BatchNumber, LastName, FirstName, ModifiedBy, ModifiedOn
from MyTable1
where LID in (1300, 144422, 4793) )
May 18, 2007 at 6:41 am
Only way you should get that message is if one of the columns have identity on them, script out the table definitions and look for it.
Easily solved though, just turn indentiy insert on for the table you want to insert into.
May 18, 2007 at 7:09 am
Anders is correct, one of the columns in the original table your did the "select * into .." from had an identity column. The properties of that column were transfered to your new table. The following is an example, if you remove the "set identity_insert t2 on" statement you will reproduce your error, leave it in and the second insert works fine.
BEGIN
declare @t1 table (col1 int identity, col2 int)
insert into @t1 (col2) values (1)
insert into @t1 (col2) values (2)
insert into @t1 (col2) values (3)
if object_id('t2') is not null drop table t2
select *
into t2
from @t1
select * from t2
set identity_insert t2 on
insert into t2 (col1,col2) values (4,4)
set identity_insert t2 off
END
James.
May 18, 2007 at 7:09 am
Easily solved, yes, but that doesn't explain why I got the error message in the first place. All I did was a SELECT...INTO. Nothing else except using that table in JOINS for an update.
I was working with a slight different scenario this morning. This time I did a SELECT...INTO on a Temp table. Later on, went to insert into the Temp table and got the same error. After considering the problem and going back to MyTable2, I realized that one of the columns in was set to Not NULL. I didn't define the NOT NULL on that column. So, I went to modify the column, to make it nullable, and it told me it couldn't because this was an identity column.
Nevermind the fact that I never defined the MyTable2.column as an identity, it still set itself up as one. The column in question is the PolID column in my first query. So, if I'm pulling the values of this column from another table, how can it set itself in the new table as an identity?
It looks like, in both the Temp and MyTable2 cases, the SELECT...INTO is somehow adopting the properties of the original column in the original table. PolID is set identity in MyTable1 and it's pulling that definition over.
Now, I've done a lot of temp tables and SELECT...INTOs and a lot of them have used the MyTable1.PolID column. I've even inserted records into some of those created tables that have PolID (though several of them involved the results of multiple joined tables) and I have never, ever, ever seen this behavior before. Why would SQL Server start doing this if it had never done this before?
Can someone else do a test and see if they come up with the same thing? If you're unable to repeat my problem, let me know what version (service pack, etc.) of SQL you're using. I'd love to discover whether or not this is a very specific problem or if I'm the only one having this issue.
May 18, 2007 at 7:15 am
I just saw your followup post. As far as I know the "Select * into ..." has always copied the definition of the original columns to the new table. If you don't want that behavior you would have to define the new table yourself and use insert into [tablename] (column, column,....) from [oldtable]. I just went back and checked agains SS 2000 sp4 and again aginst SS 2005 SP2 with the same results.
HTH,
James.
May 18, 2007 at 7:27 am
Huh. That's odd. It's never done that to me before. Though most of the time, when I'm going back and doing inserts, it's on tables created by the results of multi-table join. I guess I have more testing to do.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply