Writing entire data table to db via db sql executor node

[Zvereec1]

I want to import a data table into my sql table, but the IDs need to come from an sql sequence (for reasons I have no control over).

I was therefore looping through my data table using chunk loop start, and then for every row I was transforming table rows to variable and then executing the below sql statement in my db sql executor node, using the flow variables.

However, this is taking way too long so I wanted to use a more efficient approach. One option was to do a db insert obviously, but for that I would first need my IDs. Unfortunately though, due to sub-query restrictions, it is not possible to query for sequence IDs in nodes such as db reader, db query reader or db looping.

Does anyone have a workaround for this?

DECLARE @NextWellID INT;
SELECT @NextWellID = NEXT VALUE FOR dbo.WELL_ID;
INSERT INTO dbo.WELL (WELL_ID, PLATE_ID, ROW_S, ROW_N, COL_S, COL_N, SAMPLE_ID, WELL_ROLE, VOLUME)
VALUES (@NextWellID, $${IPLATE_ID}$$, ‘$${SROW_S}$$’,$${IROW_N}$$, ‘$${SCOL_S}$$’, $${ICOL_N}$$, $${ISAMPLE_ID}$$, ‘$${SWELL_ROLE}$$’, ‘$${SVOLUME}$$’)

[mlauber71]

@Zvereec1

VALUES

Can you create a larger values block and put b that into a Flow variable? Might depend on the length but maybe 20-50 lines?

Also: are you able to create a temporary table on your database and then insert from within?

What kind of database is this?

[Zvereec1]

Thanks for your quick reply!

By “create a larger values block”, do you mean increasing my loop size (or even just do it without loop in one go) and do a “group by” to convert my columns to sets? I did not explore that option further because I would need to fetch multiple sequence IDs either way, and that requires NEXT VALUE FOR.

Ah yes, I can try activating a stored procedure. Good point.

[mlauber71]

@Zvereec1 the idea was to use a KNIME loop to create a larger VALUES block to get more data into one go. But creating text / SQL code to sort of manually insert data is maybe not ideal.

The VALUES block would be created in KNIME and then the whole statement would be sent via SQL executor (as I said: not ideal).

INSERT INTO dbo.WELL (WELL_ID, PLATE_ID, ROW_S, ROW_N, COL_S, COL_N, SAMPLE_ID, WELL_ROLE, VOLUME)
VALUES 
    (@NextWellID, 1234, 'A', 1, 'B', 2, 5678, 'Control', '50'),
    (@NextWellID, 1234, 'C', 3, 'D', 4, 5679, 'Test', '100'),
    (@NextWellID, 1234, 'E', 5, 'F', 6, 5680, 'Sample', '150');

[Zvereec1]

Right, but every row needs its own unique ID so the problem remains: how can I fetch multiple sequence IDs in one query. I will try to create a stored procedure that populates a table, and then read the sequence IDs from that table.

[Zvereec1]

Using the stored procedure works.

[mlauber71]

@Zvereec1 that is great. Maybe you can tell us in a few words what you did so that if future generation stumble upon this thread they might get an idea

[Zvereec1]

Sure:

On the end of my database

-I created a stored procedure on the end of my database that
drops table x if it exists, then creates table x and populates table x with n amount of IDs

In my knime workflow

-use a db sql executor node to execute said stored procedure, which then populates my temporary table
-read said temporary table via db table selector/table reader nodes
-use column appender to add the read table to the table I want to import into my database
-use a DB insert node to do the actual data import