Databricks JDBC write to table with PK column, error, key not found.

[JamesY]

Hello, I am trying to write data to table, it works find before, but after I recreated the table with one column as PK, there is an error.

Unable to write into the A_Table table....key not found: id

 What is the correct way of doing this?
PK column:   [ID] [bigint] IDENTITY(1,1) PRIMARY KEY NOT NULL

[Kaniz_Fatma · Accepted solution]

Hi @JamesY, If you’re using Databricks with SQL Server, you can use the OUTPUT clause to retrieve the primary key value after an INSERT query.

CREATE TABLE A_Table (
    ID BIGINT IDENTITY PRIMARY KEY,
    -- Other columns...
);

INSERT INTO A_Table OUTPUT inserted.ID VALUES ('new item');

The OUTPUT clause allows you to capture the inserted primary key value.