Create an identity in sql2005

I am new to sql 2005.

I want to get to get an identifer column into a table (similar to access autonumber) but cant see how this is done.

I have an ID column set to int
I tried right clicking on the table to Modify but cannot get  Identity into the datatype.
I tried a right click Script table as > Update to > New query editor window
UPDATE [testDB].[dbo].[Inventory]
   SET [ID] = <ID, int,>
      ,[Name] = <Name, varchar(20),>
      ,[Type] = <Type, varchar(30),>
 WHERE <Search Conditions,,>
I tried Identity and $Identity after int but it creates an error message.

Can anyone please advise how to do this?
Thanks
John
johnhardyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ved Prakash AgrawalDatabase Consultant/Performance ArchitectCommented:
you can do one thing you can place all your data into temp table and then drop the table and recreate the table with identity column and the push your data into this table.
0
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectCommented:

SELECT * into #temp from Inventory

drop table Inventory

create table Inventory
(ID int Identity(1,1),
[Name] varchar(20),
[Type], varchar(30)
)

INSERT INTO Inventory
([Name],[Type])
SELECT [Name],[Type] From #temp
0
johnhardyAuthor Commented:
Thanks
I am at this stage just trying to create or alter a table to get the identifier number (similar to autonumber).

There is no data involved for this exercise
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

LowfatspreadCommented:
like this ... read up onn alter table in Books online,..
(Update is only for updating the tables data rows not modifying the table structure)

alter table yourtable
  add column newcolumnname int identity(1,1) not null
go

alter table yourtable
  drop column yourexistingIDcolumn
go

alter table yourtable
  add primary key NOT CLUSTERED (yournewcolumnname)
go

 hth
0
puranik_pCommented:
alter table Table_1
add columnname int not null identity(1,1)
0
johnhardyAuthor Commented:
Thanks re:
Alter Table
If I try to alter table
Rt Click table
Scrit table as ALTER To is greyed out
Am I doing something wrong ?
0
ptjcbCommented:
I have an ID column set to int
I tried right clicking on the table to Modify but cannot get  Identity into the datatype.

Assuming that you are using SQL Server Management Studio

Select Modify. Find the ID column.

Identity is a property of a column, not a data type. In the ID column with the data type Int, look at the column properties. Scroll down and you should see Identity Specification.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnhardyAuthor Commented:
Thanks ptjch
I have it now
0
ptjcbCommented:
Cool.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.