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
>
>.
>

How do I get the value of a column in the last row of my table?

With out using @.@.identity or count(*) how do i retrieve a value in a column in the last row of my table.

here is the situation. ASP.net project has several sessions open. a user needs to get the value of a column in the last row inserted in a particular table.

Select CallID from Calls where 'it is the last row inserted'

thanks in advance.

you can email me at !cbmorton!@.!gmail.com!

Chris Morton

Hard to get the last row in a relational database. Last means many things: last inserted, last in clustered index, last in another sort, last in a particular index, last in the data page.

Does your table have an Identity column, timestamp, CreatedDate that defaults to getDate()? You need something.

|||

In relational databases the data is by definition unordered, therefore the last data row entered is functionally the same as the first row entered.

That being said, if you have an inserted date/time column you could select from the table where the column equals the max(datetime) value in the table.

How do I get the value of a column in the last row of a table?

With out using @.@.identity or count(*) how do i retrieve a value in a column in the last row of my table.

here is the situation. ASP.net project has several sessions open. a user needs to get the value of a column in the last row inserted in a particular table.

Select CallID from Calls where 'it is the last row inserted'

thanks in advance.

you can email me at !cbmorton!@.!gmail.com!

Chris Morton

Define last row?

maybe this will do what you want but it is kind of dangerous because you might pick up the inserted row from another connection

Select Max(CallID) from Calls

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||i do want to pick up the last inserted row from another connection|||

There is no "last" row because SQL Server stores data as it comes in within its own storage schema. There is only a last row if you do a order of the query executed. There could be also a last *physical* row if the table includes a clustered index, which is physically ordered.

But if you want to have the last row in a resultsset, you have to order it backwards and get the TOP 1

e.g. SELECT TOP 1 SomeColumn From SomeTable Order by SomeOtherorthesamecolumn DESC

BTW, this is a public newsgroups, as long as you have MSN Alerts activated you will get a notice everytime a new answer arrives. Private communication should only be done if the thread is extended immensly due to details asking and answering back and forth, but also then the answer and the solution should be posted back here, to help other which might be in the same situation with a similar question.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

How do I find IDENTITY columns on Table using T-SQL

Is there a query I can write against an INFORMATION_SCHEMA or against the system tables to determine if a column is an identity column?

Found it, a little obscure:

SELECT obj.[name], col.[name], col.[colstat], col.*
FROM [syscolumns] col
JOIN [sysobjects] obj
ON obj.[id] = col.[id]
WHERE obj.type = 'U'
AND col.[status] = 0x80
ORDER BY obj.[name]

Does anyone know a way of doing this using an INFORMATIO_SCHEMA view?

|||I posted that sometime ago:

SELECT IsIdentity=COLUMNPROPERTY(id, name, 'IsIdentity')
FROM syscolumns WHERE OBJECT_NAME(id) = sometable_test'

Mit Information_schema views from
http://weblogs.asp.net/psteele/archive/2003/12/03/41051.aspx

select TABLE_NAME + '.' + COLUMN_NAME, TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') =
1
order by TABLE_NAME

HTH, Jens Suessmeyer.

http://www.sqlserver2005.,de

|||

Here is some more ( in technicolor ;-) )

USE northwind
GO

DECLARE @.tableName VARCHAR(50)
SELECT @.tableName = 'orders'

--Use COLUMNPROPERTY and the syscolumns system table
SELECT COUNT(name) AS HasIdentity
FROM syscolumns
WHERE OBJECT_NAME(id) = @.tableName
AND COLUMNPROPERTY(id, name, 'IsIdentity') = 1
GO

DECLARE @.intObjectID INT
SELECT @.intObjectID =OBJECT_ID('orders')

--Use OBJECTPROPERTY and the TableHasIdentity property name
SELECT COALESCE(OBJECTPROPERTY(@.intObjectID, 'TableHasIdentity'),0) AS HasIdentity

Denis the SQL Menace

http://sqlservercode.blogspot.com/