How do I impose the IDENTITY property on an existing column?

How do I impose the IDENTITY property on an existing
column with data in it already? Scenario:
Column "joe" is not an IDENTITY column
Data imported into table and "joe" now has data.
***I want to now assign "joe" the IDENTITY property
without dropping and re-adding the column.
How do I do this?
Thanks,
MikeActually, this method will work as long as "joe" is an integer, as the
identity must be anyway to increment.
This table (YourTable) consisted of joe and jim both integers and initially
loaded, say with these values.
1,2
5,3
3,4
6,3
7,2
We run this script:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_YourTable
(
joe int NOT NULL IDENTITY (1, 1),
jim int NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_YourTable ON
GO
IF EXISTS(SELECT * FROM dbo.YourTable)
EXEC('INSERT INTO dbo.Tmp_YourTable (joe, jim)
SELECT joe, jim FROM dbo.YourTable TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_YourTable OFF
GO
DROP TABLE dbo.YourTable
GO
EXECUTE sp_rename N'dbo.Tmp_YourTable', N'YourTable', 'OBJECT'
GO
COMMIT
Now YourTable has joe as the identity column and the next entry into
YourTable will enter 8 in joe
"Michael Berry" <anontaddler@.hotmail.com> wrote in message
news:2c3201c38e20$b3ed8010$3501280a@.phx.gbl...
> How do I impose the IDENTITY property on an existing
> column with data in it already? Scenario:
> Column "joe" is not an IDENTITY column
> Data imported into table and "joe" now has data.
> ***I want to now assign "joe" the IDENTITY property
> without dropping and re-adding the column.
> How do I do this?
> Thanks,
> Mike|||I cannot have any temporary tables created - I must work
within the confines of one table and the specific column I
have been given - I would like to "toggle" the identity
property as you can in the Enterprise Manager GUI
interface...
Michael
>--Original Message--
>Actually, this method will work as long as "joe" is an
integer, as the
>identity must be anyway to increment.
>This table (YourTable) consisted of joe and jim both
integers and initially
>loaded, say with these values.
>1,2
>5,3
>3,4
>6,3
>7,2
>We run this script:
>BEGIN TRANSACTION
>SET QUOTED_IDENTIFIER ON
>SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
>SET ARITHABORT ON
>SET NUMERIC_ROUNDABORT OFF
>SET CONCAT_NULL_YIELDS_NULL ON
>SET ANSI_NULLS ON
>SET ANSI_PADDING ON
>SET ANSI_WARNINGS ON
>COMMIT
>BEGIN TRANSACTION
>CREATE TABLE dbo.Tmp_YourTable
> (
> joe int NOT NULL IDENTITY (1, 1),
> jim int NOT NULL
> ) ON [PRIMARY]
>GO
>SET IDENTITY_INSERT dbo.Tmp_YourTable ON
>GO
>IF EXISTS(SELECT * FROM dbo.YourTable)
> EXEC('INSERT INTO dbo.Tmp_YourTable (joe, jim)
> SELECT joe, jim FROM dbo.YourTable TABLOCKX')
>GO
>SET IDENTITY_INSERT dbo.Tmp_YourTable OFF
>GO
>DROP TABLE dbo.YourTable
>GO
>EXECUTE sp_rename N'dbo.Tmp_YourTable',
N'YourTable', 'OBJECT'
>GO
>COMMIT
>Now YourTable has joe as the identity column and the next
entry into
>YourTable will enter 8 in joe
>
>"Michael Berry" <anontaddler@.hotmail.com> wrote in message
>news:2c3201c38e20$b3ed8010$3501280a@.phx.gbl...
>> How do I impose the IDENTITY property on an existing
>> column with data in it already? Scenario:
>> Column "joe" is not an IDENTITY column
>> Data imported into table and "joe" now has data.
>> ***I want to now assign "joe" the IDENTITY property
>> without dropping and re-adding the column.
>> How do I do this?
>> Thanks,
>> Mike
>
>.
>