Thursday, March 8, 2012

Alter of text type field

Can I change the datatype for a particular field which was previously set to text?If possible then how?Secondly can I change the datatype of a filed of int type to identity after inserting data?Can I remove identity property from a field?USE Northwind
GO

CREATE TABLE myTable98 (Col1 int, Col2 text)
GO

INSERT INTO myTable98 (Col1, Col2)
SELECT 1, REPLICATE('X',8001) UNION ALL
SELECT 2, 'Hi! How the hell are you' UNION ALL
SELECT 3, 'X'
GO

ALTER TABLE myTable98 ALTER Column Col2 varchar(8000)
GO
-- No Good
ALTER TABLE myTable98 ADD Col3 varchar(8000)
GO

UPDATE myTable98 SET Col3 = Col2

SELECT Col1, LEN(Col3), Col3 FROM myTable98

ALTER TABLE myTable98 DROP Column Col2
GO

SELECT * FROM myTable98
GO

DROP TABLE myTable98
GO

Look up ALTER in Books Online for more....

No comments:

Post a Comment