Insert or Change Identity

How can I insert or drop identity for a column from the query analyser?I don't think you can...

You can create a new column, insert the data from it, drop the column, add a new column with the old name, and move the data back...don't know if you can rename a coulmn..

Gotta look more closley at ALTER..

anyway, I was hacking around with this

USE Northwind
GO

CREATE TABLE myTable99 (Col1 int IDENTITY, Col2 Char(1))
GO

INSERT INTO myTable99 (Col2) SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C'
GO

ALTER TABLE myTable99 ALTER Column Col1 int
GO

INSERT INTO myTable99 (Col2) SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C'
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||Well, I gotta take that back...you can change it in Enterprise manager...so you should be able to do an ALTER...just couldn't see it...|||Originally posted by Brett Kaiser
Well, I gotta take that back...you can change it in Enterprise manager...so you should be able to do an ALTER...just couldn't see it...

I know it is possible in Enterprise Manager but I like to do it through Query Analyser.But the question is how?|||You can add a new column which has the identity property.
You can drop the column.

Don't think you can remove the identity property though.
e-m will probably create a new table and copy the data - it tends to do that even if it doesn't need to.

As Brett says you can create a new column, copy the data and drop the old.