Conversion IDENTITY column H2 to SQL Server

Question

I've written a migration script for my local in memory H2 database. Now the table is created as follows:

CREATE TABLE CaptureResults
(
    id IDENTITY PRIMARY KEY,
    eventId UNIQUEIDENTIFIER CONSTRAINT fk_CaptureResults_Events REFERENCES Events,
    resultKey VARCHAR(255) NOT NULL,
    resultValue VARCHAR(255) NOT NULL,
    createdAt DATETIME2 DEFAULT CURRENT_TIMESTAMP NOT NULL
);

But when I run this in my development environment in Azure using SQL Server, I get an error. Now I believe it has something to do with the IDENTITY part, where SQL Server needs it to have "INT" in front of it. But when I add that it doesn't work in H2 anymore.

The error I'm getting is the following:

When I use INT AUTO_INCREMENT:

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'AUTO_INCREMENT'

Does anyone know how to fix this?

Answer 1

Try the following (not tested):

CREATE TABLE CaptureResults
(
    id INT NOT NULL IDENTITY(1,1),
    eventId UNIQUEIDENTIFIER CONSTRAINT fk_CaptureResults_Events REFERENCES Events,
    resultKey VARCHAR(255) NOT NULL,
    resultValue VARCHAR(255) NOT NULL,
    createdAt DATETIME2 DEFAULT CURRENT_TIMESTAMP NOT NULL,
    CONSTRAINT [PK_dbo.CaptureResults] PRIMARY KEY CLUSTERED (id)
);