Other options to ALTER TABLE command

[TomR100]

Hello,
I am trying to automate a procedure and the user would like to have the RowID column numbered 1 thru 300 each time this procedure is run.

Right now I have the procedure in test and I perform the commands
ALTER TABLE tmpGeneralLedger DROP COLUMN RowID
ALTER TABLE tmpGeneralLedger ADD RowID INT IDENTITY(1,1)

It works the way I want but the DBA has the production database tighten down and he said that the Alert Table command may not work.

Is there something else I can do to get the proper numbering of the RowID column?

Thank you,
TomR100

 

[rac2]

Here is a thought. The IDENTITY column is part of the database plumbing, not part of the meaningful content of the database.

Define a separate column for the sequence numbers that the user requires. INSERT rows, then UPDATE the values in the sequence column with calculated values.

Or, since the requirement is that the rows be numbered 1 to 300 it may be that the same rows contain data about the same thing every time the procedure runs. Therefore UPDATE the rows instead of INSERTing.

I realize that we do not always have the flexibility or control to enforce the kind of distinction I suggest; once a user gets their hands on a RowID column it can be difficult if not impossible to pry it loose.

 

[ESquared]

rac2 is right on target! General database design rule #97: Don't use ID columns as meaningful data, and don't use meaningful data as primary keys.

-------------------------------------
A sacrifice is harder when no one knows you've made it.