Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
litrainer1
Starting Member
1 Post |
 Posted - 2013-07-09 : 12:53:48
|
| my users create data in excel which summarize into sql-this is the code:insert into spipublic.serviceattendance (as_tstamp, as_lastuser, as_se_id, as_startdate, as_reason) values ('7/5/13', 'dba', '114250', '2/2/13', 'Provided') ;the output is one column but can be several hundred rows. The "insert" is always the same, the "values" vary by row.i can copy the data, paste it into sql and run the query. i have server permissions, my users, who are inexpirenced and dangerous, do not.My ideal is they they can log onto to the server and thenopen to the querry pane for the corrent database (call it DATA1)paste their excel datarun querryexitthey can't go anywhere else, can't do anything else, just log on, paste and run.i'm committed to having the data start as excel, and this seem like a good approach; but if you have a better or smarter way, i'm willing to try and do anything.michael |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-09 : 17:09:27
|
| That approach can certainly work - you can give ONLY insert permissions on spipublic.serviceattendance to the users. But, it is a less than robust and cumbersome approach. Users might insert the data more than once; they will need to go through several steps to insert the data etc.You could have the users save the file and have them import it into the database. There are a number of ways to do this: http://support.microsoft.com/kb/321686Another approach might be to add a button to your Excel sheet and add a macro to it which goes through the data rows and inserts them into the database. Google for examples http://stackoverflow.com/questions/3767879/insert-data-from-excel-into-sql-dbYou could also have them save the file and write a powershell script to load the excel data and insert it into the tables. Here is an example: http://poshcode.org/1098 |
 |
|
|
|
|
|
|
|
limiting user privlages-run querry only