Solved

Create an identity in sql2005

Posted on 2007-04-10
9
780 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

816 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

8 Experts available now in Live!

Get 1:1 Help Now