How to insert into a table with just one IDENTITY column (SQL Server CE)

Question

I am trying to insert a value in a one IDENTITY column Table in SQL Server CE 3.5. I Tried the following:

INSERT Target DEFAULT VALUES
INSERT Target (ID) VALUES (DEFAULT)
INSERT Target (ID) VALUES ()

But none of them worked. This is the SQL command I used to create the table (Using SQL Server Management Studio):

CREATE TABLE Target(
ID int NOT NULL IDENTITY (1, 1) PRIMARY KEY
);

Microsoft help site (http://msdn.microsoft.com/en-us/library/ms174633%28SQL.90%29.aspx) mentions that DEFAULT values are not valid for identity columns however they do not mention any alternative.

They mention something about uniqueidentifier and ROWGUID but I have not been able to make it work.

I would appreciate any pointers on how to solve this problem or links to documentation about valid sql commands for sql server CE.

Thank you

Answer 1

Using Default Values works for identity columns on the standard version of SQL. I can't see any reason why it wouldn't work on CE.

In your case you would do something like this:

Insert Into Target
Default Values

Edit:

This issue looks like it is specific to SQL CE.

The only other thing I could suggest would be to add another column to your table, such as DateInserted.

Insert Into Target (DateInserted)
Values (getdate())

This should then insert a new row thus generating a new ID.

If you can change your table structure then you could us a UniqueIdentifier instead.

Create Table Target
(
IDColumn uniqueidentifier not null
)

Insert Into Target
Values (newId())