attach IDENTITY property to an existing column

Hi All,
Can any body tell me that how we can attach IDENTITY property to an existing int columnyou can not.

you have to...

1. rename the existing table
2. drop the foriegn keys referencing it.
3. drop the constraints on the renamed table.
4. define a new table with the identity property with the orginal table name
--the next 2 can go in any order based on what you decide is best
5. then reapply all constraints on the table, usually doing the primary key first.
6. insert data from the renamed table to the new table. you may want to do a INSERT IDENTITY MyTable ON to insert your existing keys. Just do not forget to turn it off when you are done.
7. drop the renamed table (optional)

you may want to wrap all or part of this in an explicit transaction with appropriate error handlers and print messages and you may not want to drop renamed table until you have committed the transaction without error and your row count after the insert matches the rowcount in the new table.

ASP.NET / SQL Server Stored Procedure Question

Hello,

I wrote a stored procedure that inserts data into one table, then inserts the value of the identity column into another table:

SET

NOCOUNTON;

INSERTINTO ContactUs_TBL

(FullName, Email, Phone, Message)

VALUES

(@.FullName, @.Email, @.Phone, @.Message)

SELECT@.@.IDENTITY

INSERTINTO ContactUsQuestions_TBL

(QuestionText, ContactId)

VALUES

(@.QuestionText,@.@.IDENTITY)

In the CodeFile in asp.net (c#.net), I'm not what to set the value property to below. Right now I just hardcoded a 2 to see how it would work. Could anyone help me out and tell me what I should put here? Each of the other statements I used were set to the value of a form control, but since this id isn't a form control, just an identity column, I'm not sure what to do:

comm.Parameters.Add(

"@.ContactId",SqlDbType.Int);

comm.Parameters["@.ContactId"].Value = 2;

-- rkeslar

Replace @.@.IDENTITY with SCOPE_IDENTITY(). This isn't related to your problem, but you should do it anyhow.

You don't add a parameter for ContactID. You don't pass it into the stored procedure, and you don't tell .NET about it.

|||

hmm. ContactID is a primary key in the ContactUs_TBL, so I see why you wouldn't pass it into the stored procedure or tell .net about it for that table. But it's also a foreign key in the ContactUsQuestions_TBL so how is it going to get inserted into that table if I don't pass it into the stored procedure and tell .net about it?

Thanks

|||Because the stored procedure picks up the value when it's running and passes it to the second insert statement.