How Can I Programmatically Remove Identity from a Table?

I am copying a view to another location as a table. SQL 2000 is automaticall
y
setting the tables ID as an Indentity column. I do not want this to happen.
It may be because the original table from whence the view works off has
identity turned on. However the copy of the table I do not wish to have the
indentity turned on as I cannot do blanket inserts with this option on.
I already know what to do from the SQL Manager wizard however I require to
do the same thing using an ALTER TABLE ALTER COLUMN approach. I find all
kinds examples on how to create an indentity insert column but not one
example on how to remove the indentity insert from a table.I dont find remove ID property.
But I traced using Profiler, SQL Server copy a table, create a table remove
ID
,Insert data using INSERT SELECT and last sp_rename.
"Jamie Carper"?? ??? ??:

> I am copying a view to another location as a table. SQL 2000 is automatica
lly
> setting the tables ID as an Indentity column. I do not want this to happen
.
> It may be because the original table from whence the view works off has
> identity turned on. However the copy of the table I do not wish to have th
e
> indentity turned on as I cannot do blanket inserts with this option on.
> I already know what to do from the SQL Manager wizard however I require to
> do the same thing using an ALTER TABLE ALTER COLUMN approach. I find all
> kinds examples on how to create an indentity insert column but not one
> example on how to remove the indentity insert from a table.|||You might consider changing your view to CAST the IDENTITY column so that
the IDENTITY property won't propagate to new tables:
ALTER VIEW View1
AS
SELECT
CAST(Col1 AS int) AS Col1
FROM MyTable
GO
You can't remove IDENTITY from an existing column or can add IDENTITY to an
existing one. If you don't want to recreate the table, you can use a script
like the one below to migrate data to a new non-identity column.
ALTER TABLE MyTable
ADD Col1_new int NOT NULL
--default constraint needed of NOT NULL
CONSTRAINT DF_MyTable_col1 DEFAULT 0
UPDATE MyTable
SET Col1_new = Col1
ALTER TABLE MyTable
DROP COLUMN Col1
EXEC sp_rename 'MyTable.Col1_new', 'Col1'
Hope this helps.
Dan Guzman
SQL Server MVP
"Jamie Carper" <JamieCarper@.discussions.microsoft.com> wrote in message
news:196AC5D9-C450-4BB7-B057-E1F1C6B8628D@.microsoft.com...
>I am copying a view to another location as a table. SQL 2000 is
>automatically
> setting the tables ID as an Indentity column. I do not want this to
> happen.
> It may be because the original table from whence the view works off has
> identity turned on. However the copy of the table I do not wish to have
> the
> indentity turned on as I cannot do blanket inserts with this option on.
> I already know what to do from the SQL Manager wizard however I require to
> do the same thing using an ALTER TABLE ALTER COLUMN approach. I find all
> kinds examples on how to create an indentity insert column but not one
> example on how to remove the indentity insert from a table.|||if I understood correctly, and you want to remove identity insert
then you should be using this switch.
SET ENABLE_IDENTITY_INSERT tbl_name,1
--don't remember the syntax exactly.
or if I have completely misunderstood the question, forgive me :)|||Hey Dan,
Thanks for the tip!
CASTing the Identity column did the trick.
"Dan Guzman" wrote:

> You might consider changing your view to CAST the IDENTITY column so that
> the IDENTITY property won't propagate to new tables:
>
Thanks again,
Jamie|||wrong syntax.. this is the one :)
SET IDENTITY_INSERT table1 ON
"Omnibuzz" wrote:

> if I understood correctly, and you want to remove identity insert
> then you should be using this switch.
> SET ENABLE_IDENTITY_INSERT tbl_name,1
> --don't remember the syntax exactly.
> or if I have completely misunderstood the question, forgive me :)