How to insert a datatable into SQL Server database using Uipath database activities?

I have a datatable DT with 25 columns. I want to insert that data from DT into a SQL Server table SQDT with the same columns. I’m using an INSERT query for now.

Is there a better way to approach this problem instead of using INSERT on each record? Can I bulk insert into the database using a stored procedure or some other efficient way? It takes a lot of time to insert as DT has 130000 tuples.

There is a insert datatable activity, you must make sure all columns and datatypes are matched.

So the datatable in uipath and database must have the same number of columns etc.

A good way to ensure the dt is correct format is to do a select statement om the dt.

Select top 0 * from dt. This returns a blank datatable with the headers.

2 Likes

Hi… Renan Souza

I hope this information may help you -

Yes, there is a better way. You need to create a Type in SQL server with the same definition as your Datatable.

Here you can User-Defined TableType

CREATE TYPE [dbo].[MyTable] AS TABLE(
    [Id] int NOT NULL,
)

Define Parameter in SP

CREATE PROCEDURE [dbo].[InsertTable]
    @myTable MyTable readonly
AS
BEGIN
    insert into [dbo].Records select * from @myTable 
END

and send your DataTable as a parameter

Regards,
Srija

Hello Tim, i don’t see the Insert Data Table activity, I don’t have it. I google it to check which package have to be installed, but couldn’t find anything. Can you tell me which package needs to be installed in order to have it? Thank you very much!

You could use the insert datatable activity by installing the following package: Uipath.Database.Activities

Thank you very much!!