SQL autonumbering problem
Home > IT Answers > SQL Server > SQL autonumbering problem
Chippy088
330 pts.
0
Q:
SQL autonumbering problem
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.

ASKED: Aug 26 2009  2:45 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Mrdenny
46735 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
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,
                    etc...
 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.
Last Answered: Aug 26 2009  3:26 PM GMT by Mrdenny   46735 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Chippy088   330 pts.  |   Aug 27 2009  7:39AM GMT

Thanks, I’ll try that.
Now that it looks like I’m stuck with SQL, I’ll have to do some training.

 

Randym   1410 pts.  |   Aug 27 2009  2:53PM GMT

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?

 

mrdenny   46735 pts.  |   Aug 27 2009  8:20PM GMT

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.

 
0