Let's say I have a T-SQL table with 20-30 columns and 2-3 millions rows (huge table). I want to allow a user to update this data directly (the solution must work for different tables). I generally have the following options:
- to create simple web interface displaying only part of the data via paging
- to create desktop application which can download the data (on parts) and sync it when it is changed
This will probably work, but the user
will be force to learn how to work with the interface, and the application functionalities will consume a lot of time to develop - it will be easier to use tool that is already created and the user is familiar with (for example excel).
I know I can export/import data between T-SQL table and excel file, but is there a way to build some kind of collaboration between them - for example the excel to call a stored procedure which is returning the data (it will be nice if I can send a hash of the data or the rows in order to return only the rows that have been changed). And to call a stored procedure which can upload/delete/insert data in the table.
If not, I guess I am going to work with files - download the file, edit the data in excel, then upload it, if there aren't other ways?