• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 700
  • Last Modified:

Can not make autogenerate column in Sql server 2008

Hello,

I am using SQL server.  I am trying to make one of the columns as an autogenerate one.  But when I go to do that (Is Indentity), it is not allowing me to put value to Yes. it's greyed out.  I am new to the development of SQ server 2008.  Is this method right?  What else I have to do to make it autogenerated.  
I am using Sql server 2008 with asp.net.  On the screen top left corner it says, Administrator. But then why cannot I change the previllges?

Thanks,
0
armbsu
Asked:
armbsu
  • 5
  • 5
  • 3
1 Solution
 
BrandonGalderisiCommented:
You can't change an existing column to an identity.  You can ADD an identity column, then use "set identity_insert YourTableName on" to update the value of the identity column, then run "set identity_insert YourTableName off".

Now.  I can't GUARANTEE that you can update an identity with identity insert, but it would seem to make sense that you could.  I have only ever used it to "INSERT" into the identity column.
0
 
armbsuAuthor Commented:
Thank you for your comment.
This table does not contain any data as of now.  So I can add a new column as you suggested but how do I specify isIdentity as true or yes.  I tried with one test col but  again I cannot mark that col as Yes.
Also, do you mean by set identity_insert YourTableName on" and set identity_insert YourTableName off?
We do not have any DBA here. So please can you explain in detail?
0
 
BrandonGalderisiCommented:
If the table is empty, then adding a new column through SSMS you should be able to add the identity property.  Alternatively, through the query tool you can add it by running the following query.
alter table [YourTableName]
Add [YourNewColumnName] int identity(1,1)

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Mark WillsTopic AdvisorCommented:
Are you talking about an automatically assigned value to a column for each insert ? then an identity column or a row GUID is the correct thing to do, but "autogenerate" is also a term often used in a datagridview in programming parlance, and is a different thing.

Brandon has given you the code for an Identity column that will start at 1 and increment by 1 for each new inserted row. For the other definition for autogenerate, please have a quick look at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=650217&SiteID=1   

and you might like to describe what you need in a bit more detail...
0
 
armbsuAuthor Commented:
Mark and Brandon,

I a, talking about SQL server autogenerate colums and not gridview autogenrate.

I tried what Bradon suggested-
alter table [person]
Add [person_id] int identity(1,1)

I already have person_id col in the table and table has no data.
It gives an error column name in each table must be unique. Col name person_id in table person is specified mora than once.

I just have one col call ed person_id in person.   What I am doing wrong?


sql-error.jpg
0
 
BrandonGalderisiCommented:
Then drop the table, then add it.  You will have to add any indexes or constraints back on.
0
 
BrandonGalderisiCommented:
also, you should do it with SQL Server Management Studio, not Vis Web Dev.
0
 
armbsuAuthor Commented:
Worked!!
0
 
armbsuAuthor Commented:
Brandon,

Your query worked!!
Thanks,
But now I have another problem.  My another webform inserts person_id columns from a text file. Since this column is autogenerated, that programe is failing saying that it has to be OFF.  Is there any way that I can work this up?
0
 
BrandonGalderisiCommented:
Say again.  Your other thing wants to insert the person_id.  You really shouldn't have it both ways.  You either do or don't want identity.
0
 
Mark WillsTopic AdvisorCommented:
>> Since this column is autogenerated, that programe is failing saying that it has to be OFF <<

does not sound like identity, sounds like autogenerate ;)

0
 
Mark WillsTopic AdvisorCommented:
Either that, or you are maintaining multiple tables, and have to apply the same identity type process to you "people" table as well...
0
 
armbsuAuthor Commented:
Thank you all for commenting
I am working with my business reqs and will come up with the different design.  I wanted that when text file gets inserted along with other table,  it will insert in Person table as well , that way I could query the Person table and see all the person id taken.  I know it's an autogenerate col, but did not know that I will not be able to add into this. If while inserting text file,  if there is any duplicate entry into person table, I wanted to throw an error.

Anyways thanks all.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 5
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now