Autonum - SQL Identity problem

  • Thread starter Thread starter snooka9
  • Start date Start date
S

snooka9

~ Office XP & SQL Server 2005 ~

FYI: I'm fairly new to SQL programming.

I recently exported my Access tables to an SQL database and all went well.
I did a little tweaking to the tables with the SQL Management Studio to setup
the primary keys, indexes, defaults, etc... When the time comes to actually
use SQL as my backend and Access as the front end I'll need to update the
most recent data from my Access tables to the SQL tables and this is the
phase I'm currently testing.

I created a new Access mdb and linked all the tables from my Access BE and
SQL BE to be able to manipulate the data in one place. I've deleted all of
the data from all of the SQL tables and created Access append queries for
each table to copy the current Access data to SQL. ***The problem:*** on
tables that have an autonumber/identity column I'm getting an error that the
records won't append due to key violations.....so basically the identity is
trying to repeat. All of the tables w/o identity columns append perfectly.

I've tried using the "DBCC CHECKIDENT ('<TableName>', RESEED, 0);" to reset
the identities and if I manually add a record I can see that the identity is
in fact reset, but my append queries still fail.

Any ideas on how to copy my live data from Access to the corresponding SQL
table w/o actually re-exporting the table? I don't want to have to go
through the 100+ tables again and setup the primary keys and such.

Is it possible to turn the identity property off of a column, append the
data then turn the identity back on?

Any help would be appreciated.
Thanks.
..
 
Yes, that is very easy to do -- once you know the right T-SQL syntax,
anyway :-)

Run this statement on the table from Management Studio:

SET IDENTITY_INSERT TableName ON;

Then import your Access data. When you're done, make sure to turn it
off, or you won't get identity values for new rows. SQL Server will
pick up the numbering where Access left off.

SET IDENTITY_INSERT IdentityTable OFF;

--Mary
 
Oops, typo -- the Off statement should be the same table name:

SET IDENTITY_INSERT TableName OFF;

--Mary
 
Back
Top