September 24, 2009 at 3:06 pm
I am in the process of migrating a bunch of data from my old Access db to SQL. I have two tables which have Identity fields as their primary keys. I want to preserve the values of those fields in the records imported from Access, but have values in future records just incremented by 1.
I thought it was pretty clear how to do this: Turn off the Identity spec, migrate the data, and then turn the Identity spec back on. This seems to work. BUT....here's what has me puzzled:
In migrating the data in table A, I did NOT do this. I just ran an insert query to insert all the data from every field into the SQL table (including the PK) with the identity spec still turned on. It worked fine.
In trying to migrate the data in table B in the same fashion, however, I get a Key violation unless I first turn off the Identity spec.
So my question is: Why the difference between these two? I am surprised that the insert into table A works.
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
September 24, 2009 at 4:49 pm
Obviously, the data in tableA do have unique values in the primary key field, whereas tableB doesn't.
Try the following:
SELECT PrimKey, COUNT(*) FROM TableB
having COUNT(*) >1
GROUP BY PrimKey
This query should result in any duplicate PrimKeys. Each one of those will cause the error.
September 24, 2009 at 5:06 pm
No, I don't think that's the issue. If I simply turn off the IDENTITY specification on the PK, but leave it as a unique PK, I can do the insert just fine in table B.
My puzzlement is why I can do this insert just fine in table A WITHOUT turning off the IDENTITY specification.
I have now found that theres a setting
set identity_insert
ON
that allows me to insert into the field without turning off the IDENTITY specification.
But I still don't understand why I get away without doing this in table A.
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
September 24, 2009 at 6:54 pm
Are you sure that the "destination" version of Table A actually has an identifier column? Because you're correct - you cannot insert into the identity column without setting the identity_insert attribute for that table to ON first. That said - you can turn that setting on or off on a table, even if it doesn't have an idenity column set up...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 25, 2009 at 12:28 pm
It stays on until you turn it off or turn it on for another table, so did you test the inserts to tableA at some point in the past?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 25, 2009 at 1:31 pm
I don't recall turning identity_insert on for this table in the past, but there are lots of things I don't recall....:rolleyes:
That's probably what happened.
Is there a way to determine the state of Identity_insert on a given table?
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
September 27, 2009 at 5:18 am
None that I'm immediately aware of. I'd have to do a search for it.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply