hii all,
we have a requirement of uploading a excel file and once user click preview the entire data in the excel should visible in grid for user , and once clicked on submit then the data should store in database
for achieve this we are plane to use a staging table(dummy db) for storing uploaded excel data and showing in grid , once user click submit the data in staging table should move to main database table and need to delete the staging table data , why because if the staging table not cleared the next user data also store and entire data in the staging table will appear in grid
please help with this , guide me to achieve this.
thankyou,
Ramesh
Discussion posts and replies are publicly visible
Two solution for this problem
1. Add one more column in the staging table i.e. is_processed by default that must be set to 0. When you import a file only pick rows which are having is_processed flag set to 0 and process these rows. If every validation pass put this row in the main table otherwise write in error. Once the write operation is done set the flag to 1 for that particular row.
2. Write a stored procedure where you read rows from the staging table and if it passes the validations and constraints then write to the main table otherwise write the row into the error table with a proper error message. Processed rows from the staging table can be deleted using the stored procedure by delete row syntax.
Call execute stored procedure smart service in process model to finish importing from the staging table to the main table.