Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Create an identity in sql2005

Posted on 2007-04-10
9
781 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:johnhardy
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 11

Expert Comment

by:Ved Prakash Agrawal
ID: 18881418
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
 
LVL 11

Expert Comment

by:Ved Prakash Agrawal
ID: 18881432

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
 

Author Comment

by:johnhardy
ID: 18881535
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 18881651
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
 
LVL 14

Expert Comment

by:puranik_p
ID: 18881668
alter table Table_1
add columnname int not null identity(1,1)
0
 

Author Comment

by:johnhardy
ID: 18881809
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
 
LVL 27

Accepted Solution

by:
ptjcb earned 500 total points
ID: 18881958
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
 

Author Comment

by:johnhardy
ID: 18882644
Thanks ptjch
I have it now
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 18967009
Cool.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question