Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Create an identity in sql2005

Posted on 2007-04-10
9
Medium Priority
?
793 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
[X]
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
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 2000 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.

715 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