SQL autonumbering problem

4625 pts.
SQL Server
SQL Server administration
I have a sequel DB of users which is periodically updated by exporting to access, making changes, then re-importing amended table. After re-importing, the records are duplicated, and the autosequence jumps to over 10,000. I only have 682 records. (That is the highest index number used.) Why does the auto numbering go haywire? What is the correct way to over write the old records, with the amended ones which have the same index number, and removed records marked as deleted? Reason for doing things this way is the DB is always live, and I don't want to bar users while the changes are made. I am not very well versed in SQL DBs, so any help, or pointers where to look for the instructions will be helpful.

Answer Wiki

Thanks. We'll let you know when a new response is added.

The easiest way to handle this would simply be to make the changes in the SQL Server directly.

If you need to keep using the Access database you’ll want to upload the changed records to a staging table. Basically a new table with the same name, and without the autonumber (actually called IDENTITY in SQL Server) enabled.

Then use an update statement to update the values in the main table from the values in the staging table.

UPDATE MainTable
SET FirstName = StagingTable.FirstName,
LastName = StagingTable.LastName,
FROM StagingTable
WHERE MainTable.IdField = StagingTable.IdField

An UPDATE statement like that one will do the trick. You’ll just need to change the table and column names to your own, and list out all the columns.

Discuss This Question: 3  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Chippy088
    Thanks, I'll try that. Now that it looks like I'm stuck with SQL, I'll have to do some training.
    4,625 pointsBadges:
  • Randym
    Like MrDenny said, the easiest would be to update SQL Server directly. Why would the users be barred from using it while the update is being done?
    1,740 pointsBadges:
  • Denny Cherry
    Don't think of using SQL as being stuck with SQL. Once you get the hang of it, you'll find out that SQL Server is way more powerful of a data storage platform then Access can ever hope to be.
    69,005 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: