ALTER COLUMN

I have an access database that I'm splitting its back-end to be located in
SQL server. One column in one of the access tables was autonumber. For
maitenance purposes, some of the rows in this column have been deleted. When
I converted the back-end to SQL, this column is defined as INT and I can't
define it as INT IDFENTITY (1,1) because of the rows that have been taken
out. I need to define this column as identity, so everytime the user adds a
new record, this column will generate an auto number. I tried the following
syntax with no luck. Any ideas?
alter table tbl_Reservation
with nocheck
ALTER COLUMN [reservation #] int IDENTITY (1,1)
constraint PK_ReservationNo primary key clustered([reservation #])
TSYou can't add or drop the IDENTITY column. You can add a new column with
the IDENTITY() property.
Or you can let Enterprise Manager do it, though I don't recommend this if
the table is of any consequential size (see http://www.aspfaq.com/2528 for
an example of the kind of thing Enterprise Manager does behind your back).
A
"TS" <TS@.discussions.microsoft.com> wrote in message
news:E29E76A2-4058-4099-A458-E3D600484499@.microsoft.com...
>I have an access database that I'm splitting its back-end to be located in
> SQL server. One column in one of the access tables was autonumber. For
> maitenance purposes, some of the rows in this column have been deleted.
> When
> I converted the back-end to SQL, this column is defined as INT and I can't
> define it as INT IDFENTITY (1,1) because of the rows that have been taken
> out. I need to define this column as identity, so everytime the user adds
> a
> new record, this column will generate an auto number. I tried the
> following
> syntax with no luck. Any ideas?
> alter table tbl_Reservation
> with nocheck
> ALTER COLUMN [reservation #] int IDENTITY (1,1)
> constraint PK_ReservationNo primary key clustered([reservation #])
>
> --
> TS|||I have come across the same problem. I cannot get the syntax right to create
an
IDENTITY (1,1) property on an existing INT column.
If it can be done in Enterprise Manager through the GUI, then there HAS to
be a way to do it in T-SQL.
Todd
"TS" wrote:

> I have an access database that I'm splitting its back-end to be located in
> SQL server. One column in one of the access tables was autonumber. For
> maitenance purposes, some of the rows in this column have been deleted. Wh
en
> I converted the back-end to SQL, this column is defined as INT and I can't
> define it as INT IDFENTITY (1,1) because of the rows that have been taken
> out. I need to define this column as identity, so everytime the user adds
a
> new record, this column will generate an auto number. I tried the followin
g
> syntax with no luck. Any ideas?
> alter table tbl_Reservation
> with nocheck
> ALTER COLUMN [reservation #] int IDENTITY (1,1)
> constraint PK_ReservationNo primary key clustered([reservation #])
>
> --
> TS|||> If it can be done in Enterprise Manager through the GUI, then there HAS to
> be a way to do it in T-SQL.
Yes, there is. Run profiler while you do it in EM, and prepare to be
amazed. Memorize the script. Rinse. Repeat. Good luck.
And FWIW, there are a lot of things that can be done through the EM GUI.
Not all of them are good, and not all of them are done the best/right way.
Be careful where you learn from. :-)|||I dropped the identity column with no problems, created another one with the
same name and since this column serves only as unique identifier, this
solution didn't hurt in any way. The problem is the identity column is not
generated in the front-end when adding a new record !! Any idea'
--
TS
"Aaron Bertrand [SQL Server MVP]" wrote:

> You can't add or drop the IDENTITY column. You can add a new column with
> the IDENTITY() property.
> Or you can let Enterprise Manager do it, though I don't recommend this if
> the table is of any consequential size (see http://www.aspfaq.com/2528 for
> an example of the kind of thing Enterprise Manager does behind your back).
> A
>
> "TS" <TS@.discussions.microsoft.com> wrote in message
> news:E29E76A2-4058-4099-A458-E3D600484499@.microsoft.com...
>
>|||On Fri, 5 Aug 2005 12:15:05 -0700, TS wrote:

>I have an access database that I'm splitting its back-end to be located in
>SQL server. One column in one of the access tables was autonumber. For
>maitenance purposes, some of the rows in this column have been deleted. Whe
n
>I converted the back-end to SQL, this column is defined as INT and I can't
>define it as INT IDFENTITY (1,1) because of the rows that have been taken
>out. I need to define this column as identity, so everytime the user adds a
>new record, this column will generate an auto number. I tried the following
>syntax with no luck. Any ideas?
Hi TS,
Create the table with IDENTITY column. Use the SET IDENTITY_INSERT
command to allow specification of the values in the IDENTITY column,
then port your data from Access to SQL Server. Now reset the
IDENTITY_INSERT operation to make SQL Server generate new identity
values for future inserts.
I think that will do the trick.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The identity column will be set appropriately even if not set in the
front-end. Actually, setting it would generate an error.
ML|||What do you mean "generated"? It is supposed to be generated on the
backend. What front-end and can you be more specific?
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"TS" <TS@.discussions.microsoft.com> wrote in message
news:DF38835E-69F8-4E35-ADBB-D339A33E910D@.microsoft.com...
>I dropped the identity column with no problems, created another one with
>the
> same name and since this column serves only as unique identifier, this
> solution didn't hurt in any way. The problem is the identity column is not
> generated in the front-end when adding a new record !! Any idea'
> --
> TS
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>

Alphanumeric Autonumber Primary Key

Hi there,
The age old question of creating a unique alphanumeric value automatically like ABC0001, ABC0002

Is it possible to do this automatically? That is, without having to update it which will slow the db down horribly?the only sane way of doing it is to have an ordinary integer identity column, then produce the alphanumeric value in a view

create view myview as
select 'ABC'+right(cast(pkey as varchar(9)),4) as myalnumkey ...

Allocate Range of AutoNumber ID's to each user

I have a non standard requirement that about 5 different users from differen
t
departments require sequential numbering for there records, however I want
all records to be in the one central table. This comes about as each
departments records ID's have a 4 letter acronym before the AutoNumber ID.
Is this possible or should I create seperate tables and then join them using
a view with union queries?
Any suggestions would be greatly appreciated.
ThanksHi
You can use INSTEAD OF TRIGGER to achieve this business logic
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"MarkCapo" wrote:

> I have a non standard requirement that about 5 different users from differ
ent
> departments require sequential numbering for there records, however I want
> all records to be in the one central table. This comes about as each
> departments records ID's have a 4 letter acronym before the AutoNumber ID.
> Is this possible or should I create seperate tables and then join them usi
ng
> a view with union queries?
> Any suggestions would be greatly appreciated.
> Thanks|||Hi,
Not sure how to use this, do you have a link or sample I could work from.
Much appreciated.
"Chandra" wrote:
> Hi
> You can use INSTEAD OF TRIGGER to achieve this business logic
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "MarkCapo" wrote:
>|||Hi,
You can try this way
CREATE TRIGGER <TRIGGER_NAME>
ON <TABLE>
INSTEAD OF INSERT
AS
INSERT INTO <TABLE>
SELECT <your logic>, required columns
FROM INSERTED
WHERE INSERTED.Key = Key
GO
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"MarkCapo" wrote:
> Hi,
> Not sure how to use this, do you have a link or sample I could work from.
> Much appreciated.
> "Chandra" wrote:
>|||Thanks.
So best to set up my 5 tables which fulfills the business requirement and
then uses 'Instead of Triggers' to generate a master table with all results.
The only other query I have is how does the key part of the query work.
Thanks
"Chandra" wrote:
> Hi,
> You can try this way
> CREATE TRIGGER <TRIGGER_NAME>
> ON <TABLE>
> INSTEAD OF INSERT
> AS
> INSERT INTO <TABLE>
> SELECT <your logic>, required columns
> FROM INSERTED
> WHERE INSERTED.Key = Key
> GO
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "MarkCapo" wrote:
>|||Key is the primary key value in the table. If you are sure that you will be
inserting only one row at a time, then you can avoiding the key.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"MarkCapo" wrote:
> Thanks.
> So best to set up my 5 tables which fulfills the business requirement and
> then uses 'Instead of Triggers' to generate a master table with all result
s.
> The only other query I have is how does the key part of the query work.
> Thanks
> "Chandra" wrote:
>|||First, do not go with Chandra's advice of an INSTEAD OF TRIGGER -- that make
s
no sense whatsoever. This is NOT a "non standard" requirement and is is quit
e
common -- hiding the business logic in an INSTEAD OF TRIGGER will create a
mess of a system. This requirement is known as pre-allocated numbers. Most
banks I know do this.
I don't know what the numbers are for, so I'll assume Orders. Here's how
it's typically done:
TABLE Orders ( Order_Num, Cust_Num, Order_Date, ... )
TABLE Avaiable_Order_Numbers ( Order_Num, Dept_Id )
PROCEDURE Create_New_Order (@.Dept_Id,...) {
BEGIN TRANSACTION
@.Ord_Num =
SELECT Order_Num
FROM Avaiable_Order_Numbers
WHERE Dept_Id = @.Dept_Id
INSERT INTO Orders (@.Ord_Num, ...)
DELETE FROM Avaiable_Order_Numbers
WERE Order_Num = @.Order_Num
COMMIT
}
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MarkCapo" wrote:

> I have a non standard requirement that about 5 different users from differ
ent
> departments require sequential numbering for there records, however I want
> all records to be in the one central table. This comes about as each
> departments records ID's have a 4 letter acronym before the AutoNumber ID.
> Is this possible or should I create seperate tables and then join them usi
ng
> a view with union queries?
> Any suggestions would be greatly appreciated.
> Thanks|||Thanks for your assistance Alex. Looked at the Instead of Trigger option an
d
it seemed messy!
Basically if you use 5 tables one for each department with there own
sequential identity value incrementing concatenated with the Dept. acronym
and then put a trigger on Insert, Update (There is no delete facility) to
build a consolidated/master table for analysis, this will provide a robust
solution.
Any comments appreciated!
"Alex Papadimoulis" wrote:
> First, do not go with Chandra's advice of an INSTEAD OF TRIGGER -- that ma
kes
> no sense whatsoever. This is NOT a "non standard" requirement and is is qu
ite
> common -- hiding the business logic in an INSTEAD OF TRIGGER will create a
> mess of a system. This requirement is known as pre-allocated numbers. Most
> banks I know do this.
> I don't know what the numbers are for, so I'll assume Orders. Here's how
> it's typically done:
> TABLE Orders ( Order_Num, Cust_Num, Order_Date, ... )
> TABLE Avaiable_Order_Numbers ( Order_Num, Dept_Id )
> PROCEDURE Create_New_Order (@.Dept_Id,...) {
> BEGIN TRANSACTION
> @.Ord_Num =
> SELECT Order_Num
> FROM Avaiable_Order_Numbers
> WHERE Dept_Id = @.Dept_Id
> INSERT INTO Orders (@.Ord_Num, ...)
> DELETE FROM Avaiable_Order_Numbers
> WERE Order_Num = @.Order_Num
> COMMIT
> }
>
> --
> Alex Papadimoulis
> http://weblogs.asp.net/Alex_Papadimoulis
>
> "MarkCapo" wrote:
>