how to set auto increment identity in sql azure table

Question

_{Tuesday, March 26, 2013 7:57 AM}

Hi All,
    I'm new with SQL  AZURE and i find somethings very limited

I created a table like so

CREATE TABLE [dbo].[crmProducts](
    [PId] [int] IDENTITY(1,1) NOT NULL, 
    [crmProdid] [varchar](1000) NULL,
    [ProductName] [varchar](255) NULL,
    [ProductNumber] [varchar](255) NULL
)

GO

and all i want and hope is for the PId to increment as i add rows to the table.
But no it doesn't work.

I can't set it on the tables as i can't get to the properties of the table.

I tried using a store proc to insert but it complains NULL can't go into PId column which is fair enough.

How can i create a table with identity column and AUTO INCREMENT.

Whats the alternative?

THanks in Advance

All replies (3)

_{Wednesday, March 27, 2013 8:38 AM ✅Answered}

This worked fine for me in Azure:

CREATE TABLE [dbo].[crmProducts](
    [PId] [int] IDENTITY(1,1) NOT NULL,
 [crmProdid] [varchar](1000) NULL,
 [ProductName] [varchar](255) NULL,
 [ProductNumber] [varchar](255) NULL
)

create clustered index crmProducts_Idx on crmProducts(PId)

insert into crmProducts (crmProdId, ProductName, ProductNumber) values ('a', 'b', 'c')

Note that every table in Azure must have a clustered index. Maybe this is what you are lacking.

Also make sure you aren't including PId in the column list in your INSERT clause.

---

_{Tuesday, March 26, 2013 10:46 PM}

No help????

---

_{Wednesday, January 27, 2016 4:04 PM}

I tried your solution, but I got the following error message:

Msg 103010, Level 16, State 1, Line 1
Parse error at line: 2, column: 11: Incorrect syntax near '[int]'.