Identity

Hello,
I have done a lot of searching on the internet to change an "ID [int] Not
Null" to an "ID [int] Identity (1,1) Not Null". Every example I have found
does not work. I get an error at 'Identity'. I need to send a single query
against the table (Alter Table Customers) to alter the Column ID to an
identity. I am using VB.NET and a Command Object to send the query - If I
can get it to work in SQL Server Express Manager, I can get it to work in my
program.
This is what Microsoft says but does not work:
ALTER TABLE table_name
{ [ ALTER COLUMN column_name
{DROP DEFAULT
| SET DEFAULT constant_expression
| IDENTITY [ ( seed , increment ) ]
}
* ALTER TABLE Customers
ALTER COLUMN ID IDENTITY(1,1) Not Null
Any assisance will be greatly appreciated.
Thanks,
ChuckHi Charles
Please always let us know what version you are using.
The reason you are not finding a way to do this is that it is not possible.
Where did you find that "Microsoft" shows ALTER COLUMN to support changing a
column to IDENTITY? You can add a new column with the IDENTITY property, but
you can't add this property to an existing column. If it's actually in the
BOL, it is a bug.
If you could change a column to have the IDENTITY property, what would
expect SQL Server to do with the existing values in that column? Let us
know, and we can probably come up with a workaround, but probably not a
single statement workaround.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:u%23LgqyGqHHA.588@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have done a lot of searching on the internet to change an "ID [int] Not
> Null" to an "ID [int] Identity (1,1) Not Null". Every example I have
> found
> does not work. I get an error at 'Identity'. I need to send a single
> query
> against the table (Alter Table Customers) to alter the Column ID to an
> identity. I am using VB.NET and a Command Object to send the query - If I
> can get it to work in SQL Server Express Manager, I can get it to work in
> my
> program.
> This is what Microsoft says but does not work:
> ALTER TABLE table_name
> { [ ALTER COLUMN column_name
> {DROP DEFAULT
> | SET DEFAULT constant_expression
> | IDENTITY [ ( seed , increment ) ]
> }
> * ALTER TABLE Customers
> ALTER COLUMN ID IDENTITY(1,1) Not Null
> Any assisance will be greatly appreciated.
> Thanks,
> Chuck
>|||Can you simply build a new table and move in the existing data? Use set
identity_insert ... on/off for this when inserting the data.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:u%23LgqyGqHHA.588@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have done a lot of searching on the internet to change an "ID [int] Not
> Null" to an "ID [int] Identity (1,1) Not Null". Every example I have
> found
> does not work. I get an error at 'Identity'. I need to send a single
> query
> against the table (Alter Table Customers) to alter the Column ID to an
> identity. I am using VB.NET and a Command Object to send the query - If I
> can get it to work in SQL Server Express Manager, I can get it to work in
> my
> program.
> This is what Microsoft says but does not work:
> ALTER TABLE table_name
> { [ ALTER COLUMN column_name
> {DROP DEFAULT
> | SET DEFAULT constant_expression
> | IDENTITY [ ( seed , increment ) ]
> }
> * ALTER TABLE Customers
> ALTER COLUMN ID IDENTITY(1,1) Not Null
> Any assisance will be greatly appreciated.
> Thanks,
> Chuck
>|||On 6 Jun, 20:18, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Hi Charles
> Please always let us know what version you are using.
> The reason you are not finding a way to do this is that it is not possible.
> Where did you find that "Microsoft" shows ALTER COLUMN to support changing a
> column to IDENTITY? You can add a new column with the IDENTITY property, but
> you can't add this property to an existing column. If it's actually in the
> BOL, it is a bug.
>
Kalen,
It is in BOL but it's not a mistake, it's just that it applies to
Compact Edition only. I'd guess that Charles is using some other
edition and didn't notice the title at the top of the page:
http://msdn2.microsoft.com/en-us/library/ms174123.aspx
http://msdn2.microsoft.com/en-us/library/ms190273.aspx
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hello,
This is the link to the web site:
http://msdn2.microsoft.com/en-us/library/ms174123.aspx
I am using SQL Express 2005. I guess I could just drop the column and
recreate it as an identity column. The data in the ID column can be
dropped, providing it is replaced with unique numbers.
If I drop the column and recreate it, it places the column at the end of the
table. I know this might seem like a stupid question, but can I add the
column and have it move to the first position?
Thanks,
Chuck
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:u%23LgqyGqHHA.588@.TK2MSFTNGP06.phx.gbl...
Hello,
I have done a lot of searching on the internet to change an "ID [int] Not
Null" to an "ID [int] Identity (1,1) Not Null". Every example I have found
does not work. I get an error at 'Identity'. I need to send a single query
against the table (Alter Table Customers) to alter the Column ID to an
identity. I am using VB.NET and a Command Object to send the query - If I
can get it to work in SQL Server Express Manager, I can get it to work in my
program.
This is what Microsoft says but does not work:
ALTER TABLE table_name
{ [ ALTER COLUMN column_name
{DROP DEFAULT
| SET DEFAULT constant_expression
| IDENTITY [ ( seed , increment ) ]
}
* ALTER TABLE Customers
ALTER COLUMN ID IDENTITY(1,1) Not Null
Any assisance will be greatly appreciated.
Thanks,
Chuck|||> This is the link to the web site:
> http://msdn2.microsoft.com/en-us/library/ms174123.aspx
And as David pointed out, you are looking in the wrong edition of Books
Online. Notice at the top of the page:
SQL Server 2005 Compact Edition Books Online
SQL Server Compact Edition Programming
SQL Reference (SQL Server Compact Edition)
SQL Server 2005 Compact Edition Books Online
ALTER TABLE (SQL Server Compact Edition)
It's quite unfortunate that the syntax for ALTER TABLE is different, but
it's also unfortunate that it would be hard to make it even more clear that
this topic is meant for compact edition only. :-(
> If I drop the column and recreate it, it places the column at the end of
> the
> table. I know this might seem like a stupid question, but can I add the
> column and have it move to the first position?
Column order should not really matter. If you really want it that way, then
drop the table and re-create it. (You can create a new table with a new
name and an IDENTITY column, populate the other columns from the existing
data, drop the old table, and re-name the new table.)
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||Thanks, David. I guess it's important for posters to tell us both version
and edition!
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1181158271.703889.92780@.z28g2000prd.googlegroups.com...
> On 6 Jun, 20:18, "Kalen Delaney" <replies@.public_newsgroups.com>
> wrote:
>> Hi Charles
>> Please always let us know what version you are using.
>> The reason you are not finding a way to do this is that it is not
>> possible.
>> Where did you find that "Microsoft" shows ALTER COLUMN to support
>> changing a
>> column to IDENTITY? You can add a new column with the IDENTITY property,
>> but
>> you can't add this property to an existing column. If it's actually in
>> the
>> BOL, it is a bug.
> Kalen,
> It is in BOL but it's not a mistake, it's just that it applies to
> Compact Edition only. I'd guess that Charles is using some other
> edition and didn't notice the title at the top of the page:
> http://msdn2.microsoft.com/en-us/library/ms174123.aspx
> http://msdn2.microsoft.com/en-us/library/ms190273.aspx
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>|||On 6 Jun, 20:31, "Charles A. Lackman" <Char...@.CreateItSoftware.net>
wrote:
> Hello,
> This is the link to the web site:http://msdn2.microsoft.com/en-us/library/ms174123.aspx
> I am using SQL Express 2005. I guess I could just drop the column and
> recreate it as an identity column. The data in the ID column can be
> dropped, providing it is replaced with unique numbers.
> If I drop the column and recreate it, it places the column at the end of the
> table. I know this might seem like a stupid question, but can I add the
> column and have it move to the first position?
>
You'd have to recreate the entire table to do that. At runtime the
column order should be defined by a SELECT statement not by the order
the columns were created in, so for most purposes it shouldn't be a
problem to put the column at the end. It may cause some inconvenience
during support and development however.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--