I am facing an errror while inserting datatable into Datatbase due to the spaces in the column names. Is there any way of doing this without removing the spaces in column names and without loop as well?
You need to remove the spaces in column names because databases are not going to accept column names with space. If you try to create a Table in MSSQL you can observe that a square bracket will be kept by designer itself.
If you keep square bracket it might accept [Column Name]
While using Insert activity to insert datatable into database, there is an issue as there are spaces in the column name. I would like to have the column names as it is and I dont want to use loop to insert row by row as I am having huge volume of data.
Is there any alternative way of doing this please.
It is not a good practice to have spaces in column names. So always try to make it a practice to have column names in ColumnName format. However, if you need to have those spaces and unable to remove them now, for the column name, you might need to use the square bracket to surround the column name.
Insert into dbo.TableName ([Column name 1], [Column name 2]) values (1,“Test value”)
Try this and let us know whether it works
Thanks. I understand that best practice. But there is a requirement where I had to do it as legacy system demands it.
I have a huge data. If I use the query in loop , it will take forever. Is there any alternate ?
Does bulkCopy.WriteToServer(DataTableName); works in the same way of Insert activity?
@Sugumar8785 - You can use the “Insert” activity that you find under UiPath.Database.Activities to directly insert a datatable without looping and inserting one row at a time. Have you tried this activity?
yes, I having issue in that activity because of the spaces in the column names.
@balupad14 please let me know if you can here.
@Sugumar8785 are you sure reason could be Space in the Column?
Datatable column description must match with column of Database
Yeah, I am sure. I verified changing the column names (as per best practices) in both DB and Datatable. It worked that time.
@Sugumar8785 What is the error that you get when you try to insert the data using that activity? Is the error saying that its because of the column name contains spaces?
I think bulk copy option that you mentioned, should do the job… But you will have to get some additional steps added to the workflow to get it working as shown in those example codes in the microsoft site…
@Lahiru.Fernando While I am using Insert activity, it is taking the words ‘TABLE’, ‘ALL’, ‘WITH’ as keywords and getting an error.
I tried removing the spaces between the column names in DB and DT and executed. It worked that time.
you can do one thing …create store Proc for that and use Execute Non Query activity
CREATE PROCEDURE [dbo].[Insert_Customers]
@tblCustomers CustomerType READONLY
SET NOCOUNT ON;
INSERT INTO Customers(CustomerId, Name, Country) SELECT Id, Name, Country FROM @tblCustomers
The approach @sandeep13 gave is really good… Try that and see… it should work for you…
Has anyone found a solution to this.
Am having a Merge query to be passed in Execute non-query and the column names have spaces in them.
Also, Uipath does not support multi line query ?
Appreciate if someone can post a solution here.