SQL autonumbering problem
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.
Software/Hardware used:
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.
Software/Hardware used:
ASKED:
August 26, 2009 2:45 PM
UPDATED:
August 27, 2009 8:20 PM
Answer Wiki:
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.
To see all answers submitted to the Answer Wiki: View Answer History.
Thanks, I’ll try that.
Now that it looks like I’m stuck with SQL, I’ll have to do some training.
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?
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.