Hi,
I am trying to use the INSERT database activity but it gave me an error
My datatable doesn’t have an ID field though…
I am able to SELECT from the database table, but able to insert… Can you please help?
I want to do a bulk insert instead of reading “For-Each-Row” from the datatable
Any suggestion?? Thanks!
Are the tables like for like in terms of column names i.e. your datatable (outputDT) and your SQL datatable?
Yes. The columns are the same. Basically I have these data loaded into the database to create table. And then I am loading the same data to the UiPath datatable and then try to append to the database table that I created.
Seems strange it thinks there is an ‘ID’ column somewhere.
Have you tried outputting each data table to confirm - is there a auto increment column (‘ID’) within your SQL data table?
Yes. I output the datatable to a CSV file and I don’t see any column called ID
Anyone has any idea where this ‘ID’ comes from? I really don’t have a column ID…
now I am basically taking the data from the table and insert the same data into the same table. Still get the same error
Then I read excel directly again and change the column names to lower case and got this error
I think the column names maybe an issue… but again… I don’t have a column name id…
Can you show the design of table in sql ?
like this?
there is an IDENT ITEM ID … not sure if it’s because the column name have spaces…
I will check it out again with a fewer column database table I guess…
yeah, can you change space to underscore and also for testing keep all datatypes as nvarchar(255).
so drop this table and create a new one.
Thank you! yes… it’s because of column names have the space… now I get another error of another column name…
However, now my question is that in the actual database which contains millions of live data, the column names have space in them…
If I change my excel files to have no spaces in the column names, the column names will be different from the actual database and those columns won’t be matched…?
Basically the actual database and the excel file input have exactly the same name now… what can I do at this point for a bulk upload? just like import as easy as in an import wizard…
you can rename column of datatable in uipath. so I dont think it should be a problem.
dataTable.Columns["ColumnName"].ColumnName = "newColumnName";
Hello @palindrome
It doesn’t where I rename the new data’s column names - in Uipath data table or in the excel sheet. What I meant is that after I change the new data’s column names with _ or no space, it won’t match the existing database’s column names. And I have just proved it
.Net SqlClient Data Provider : Invalid column name ‘period_number’.
Invalid column name ‘week_number’. — in database it’s “week number”
Invalid column name ‘route’. — in database it’s “route #”
Invalid column name ‘ident_item_id’. — in database it’s “ident item id”
You know what I mean?
What is the best approach for this scenario? I don’t prefer to change the column name of the original database unless it’s absolutely necessary. Thanks!
If you can not rename columns in database then only way to solve it is by calling a store procedure in database.
Check this out: sql - Create a stored procedure to insert new data into a table - Stack Overflow
Hi plindrome, where should i put the rename column script btw.
thanks!
Chen
Hello @lavint Use Read Workbook Activity instead of using Excel Read Range Activity. Extra columns/rows will be added if you don’t provide exact range in Excel Read Activity.
Store Result in Data-table & use Insert Activity to store data inside Database.
