Update MS SQL DB using Data table as Parameter

Help
,
1

Hi Everyone

I am new to UiPath, still learning various activities and it’s applications. Faced an issue while dealing with DB and database, please help me with the same…
I have a MS SQL Server DB which contains a table ‘Robotdb’ with column ‘ID’ as primary key. Bot must update this DB with a Data table ‘dtSample’ containing exactly identical column structure. Requirement is to update DB with only new entries in Primary Key column ‘ID’. Can this be done using a Single Non Query. I tried the below code but getting this error -
Message - The table type parameter dtSample must have a valid type name
Exception Type - Argument Exception

Code -
“INSERT INTO Database.dbo.Robotdb SELECT * FROM @dtSample S WHERE NOT EXISTS (SELECT 1 FROM Database.dbo.Robotrpa R WHERE S.ID = R.ID)”

This query when executed in MS SQL Server with 2 tables from the DB itself, it works.

Tried searching in existing threads but could not identify a solution. Kindly advise how to pass the datatable as a parameter correctly in the above query.

Thank you…

2

Why dont you use a string variables ?

String tablename = “table name” //Use assign activity
String strSql = “INSERT INTO Database.dbo.Robotdb SELECT * FROM " + tablename + " S WHERE NOT EXISTS (SELECT 1 FROM Database.dbo.Robotrpa R WHERE S.ID = R.ID)”

Type strSql in place of query

Instead

3

Thank you for the reply.

Tried the same, received the below Exception -

Source: Newtonsoft.json

Message: Cannot create and populate list type System.Data.SqlClient.SqlErrorCollection.Path ‘$values’, line 1, position 163

Exception Type: JsonSerialisationException

In the ‘Output’ panel it is written as

.Net SqlClient Data Provider : Invalid object name ‘dtSample’

dtSample being the table name.

Can anything else be done?