SQL autonumbering problem

4625 pts.
Tags:
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 db's, so any help, or pointers where to look for the instructions will be helpfull.

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.

<pre>UPDATE MainTable
SET FirstName = StagingTable.FirstName,
LastName = StagingTable.LastName,
etc…
FROM StagingTable
WHERE MainTable.IdField = StagingTable.IdField</pre>

An <a href=”http://itknowledgeexchange.techtarget.com/sql-server/back-to-basics-the-update-statement/”>UPDATE</a> 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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • 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:
    report
  • 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:
    report
  • 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.
    66,130 pointsBadges:
    report

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following