0

I have a table

CREATE TABLE [dbo].[Stamp_Merchandise](
    [merch_id] [float] NULL,
    [merch_desc] [nvarchar](255) NULL,

) ON [PRIMARY]

Im trying to alter the column merch_id by adding identity(1,1) but i keep getting error:

  alter table Stamp_Merchandise 
  alter column merch_id  identity(1,1)

error: Incorrect syntax near the keyword 'identity'.

I'm using sql server 2012.

Stephen Patten
  • 6,333
  • 10
  • 50
  • 84
user3591637
  • 499
  • 5
  • 20
  • You can't like that. And float columns can't be `identity` anyway. Does the table have data in it? – Martin Smith Aug 22 '14 at 23:04
  • yes it does , what i really want to do make that collum auto increment – user3591637 Aug 22 '14 at 23:05
  • Do you need to preserve the identity values in the existing rows or can the new values be arbitrarily assigned? – Martin Smith Aug 22 '14 at 23:05
  • you can't make a float field identity; identity columns must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0 – hex494D49 Aug 22 '14 at 23:07
  • @hex494d49 so if i change it to int it can work ? – user3591637 Aug 22 '14 at 23:07
  • No there's no syntax like that to add identity to an existing column. [It is possible](http://stackoverflow.com/questions/6084572/sql-server-how-to-set-auto-increment-after-creating-a-table-without-data-loss/6086661#6086661) but given the datatype change you may well be best off just rebuilding the table anyway. – Martin Smith Aug 22 '14 at 23:09
  • As said above, you can't alter the existing columns for identity; create a new column with identity and drop the existing one. Or create a new table with identity – hex494D49 Aug 22 '14 at 23:13
  • 1
    Create a new table with same schema, except make `[merch_id]` INT data type and Identity column, SET identity_insert on, Insert all the data from your existing table into new table, drop this old table and rename the new table to this table name and your are good. – M.Ali Aug 22 '14 at 23:17
  • 1
    Also dont forget to run DBCC reseed commands to reseed the identity values. – M.Ali Aug 22 '14 at 23:18
  • thanks ,what i actually end up doing was create another table and insert ect. , it work thanks – user3591637 Aug 22 '14 at 23:22

0 Answers0