How to write rows into MS SQL DB? (Error: IDENTITY_INSERT is set to off)

[schneich]

Hi,

I have a JSON with some 250’000 lines and I would like to write some of the data into a data base.

I was able to parse all the needed data, but now, I am struggling to write it into my MS SQL DB. The connection seems to be fine, but there is an issue with the RowID?

I don’t need the KNIME-RowID, as the DB itself will generate an ID and I have my own unique identifier…

Questions:

• Which node should I take to write rows into a MS SQL DB?
• How can I only write my columns, without the RowID?

This is KNIME version 5.9.0.

Thank you for your help,

Chris

[schneich]

hm, my mistake: it was not the KNIME-RowID, but my own roleID, which was causing the issue. When creating the database, I made the roleID as primary key with automatic numbering…

After using this to create the tables, everthing works fine:

USE [IAM-Rollen];
GO

DROP TABLE IF EXISTS ADGruppen;
DROP TABLE IF EXISTS Regelwerk;
DROP TABLE IF EXISTS Rollen;
GO

CREATE TABLE Rollen (
ID int IDENTITY(1,1) PRIMARY KEY,
roleID varchar(36) NOT NULL CONSTRAINT UQ_Rollen_roleID UNIQUE,
roleName varchar(255) NOT NULL,
gueltig_von date NULL,
gueltig_bis date NULL
);
GO

CREATE TABLE Regelwerk (
ID int IDENTITY(1,1) PRIMARY KEY,
roleID varchar(36) NOT NULL,
attributeTypes varchar(15) NOT NULL,
attributeValues varchar(255) NULL,
CONSTRAINT FK_Regelwerk_Rollen FOREIGN KEY (roleID) REFERENCES Rollen(roleID)
);
GO

CREATE TABLE ADGruppen (
ID int IDENTITY(1,1) PRIMARY KEY,
roleID varchar(36) NOT NULL,
adGroupCN varchar(500) NOT NULL,
adGroupDN varchar(1000) NULL,
CONSTRAINT FK_ADGruppen_Rollen FOREIGN KEY (roleID) REFERENCES Rollen(roleID)
);
GO

CREATE INDEX IX_Regelwerk_roleID ON Regelwerk(roleID);
CREATE INDEX IX_ADGruppen_roleID ON ADGruppen(roleID);
CREATE INDEX IX_Regelwerk_types ON Regelwerk(attributeTypes);
GO