Adding Identity to the existing table
-
Hi
I have a student table, with sno, sname, addr fields. For the sno i want to add the identity field thru the query. i tried by altering the table, but i'm not getting.
can u pls give the query to add the identity to the sno field.
Thanking you,
Thiru.
-
You can add new column to the existing table by this
alter table a add b int identity(1,1)
but if u want to do a change to an existing column it is not simple queary.
U have to create a temp table which will have the identity column and and copy the existing data to the temp table
then drop the irginal table and rename it
Bit of work, but no other way I guess
My Blog:
http://dineshasanka.spaces.live.com/ -
As Dinesh already mentioned, there is no easy way for this. See if this helps:
set nocount on
create table showme
(
sno int not null primary key
, c1 char default 'a'
)
insert into showme(sno,c1) values(1, 'a')
insert into showme(sno,c1) values(2, 'b')
go
create table showme_temp(
sno int identity not null primary key
, c1 char default 'a'
)
set identity_insert showme_temp on
insert into showme_temp (sno,c1) select sno, c1 from showme
set identity_insert showme_temp off
drop table showme
go
exec sp_rename 'showme_temp', 'showme'
go
insert into showme default values
select * from showme
drop table showme
set nocount off
Caution: Changing any part of an object name could break scripts and stored procedures.
The object was renamed to 'showme'.
sno c1
----------- ----
1 a
2 b
3 a
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I have a similar problem. I am converting forums from the older one with different structure to the new one. I'm not the greatest SQL dude, but in my script I cannot have the PK set to using the identity because after I create the table, I insert values from my old database. For instance here's my query:
if not exists (select 1 from sysobjects where id = object_id(N'yaf_Forum') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
create table dbo.yaf_Forum(
ForumIDint IDENTITY (1, 1) NOT NULL ,
...
)
GO
INSERT INTO yaf_Forum(ForumID,...)
SELECT Forum_ID, FROM OLDForums...oldForum
GO
Produces the error:
Cannot insert explicit value for identity column in table 'yaf_Forum' when IDENTITY_INSERT is set to OFF.
Epiphany!
Is there a way to just turn INDENTITY_INSERT to on?
*goes to search for this*
-
Yep, that seemed to be the solution to my problem. Just added:
SET IDENTITY_INSERT yaf_Forum ON
Before I started my insert statement.
- Like the msg says you cannot insert into an identity column.INSERT INTO yaf_Forum(col2,ol3,,...)
SELECT col2,col3 FROM OLDForums...oldForum
etc. your forumid value will automatically be filled with identity numbers.******************
Dinakar Nethi
Life is short. Enjoy it.
****************** -
The problem was though, that my new forum has to have the correct ForumID's in every related table ie: My first forum "Super Forum" has an ID value of 23 not 1. Admittedly I am not an expert in SQL so I'm sure that there might be a way to do this...but this is working so far for me. If I do have problems, I'll be back
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply