Solved

Create an identity in sql2005

Posted on 2007-04-10
9
779 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL Inner Join - Multiple Join Parameters 2 23
TSQL DateADD update Question 4 30
SQL server is using more virtual memory. 5 68
Extract string portion 2 14
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

867 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now