1

Okay, so I basically have a huge excel list with two columns, ID and Value. I'd like to update an Employee table in my SQL and set a column in that table equal to the Value from the excel file where the ID of the excel file is equal to the ID of the Employee table.

I've looked at other similar questions and they use OpenRowSet and other SQL excel functionality, but unfortunately it looks like our DBA hasn't installed any of that. What can I do as an alternative to update that table?

As for what I've tried, I created two XML lists and separated each column of the DB into a temporary table so now I have two temporary tables, one for each excel column, but now I'm reading that combining two tables without a common identifier is really bad practice so I'm back at square one.

proseidon
  • 2,235
  • 6
  • 33
  • 57
  • Do I understand correctly? You are not allowed to send / execute T-SQL commands to / on the SQL server? Or are you actually allowed to query the SQL server directly but within the VBE you cannot find a reference to `Microsoft ActiveX Data Objects 2.X or 6.0 Libraray`? Or is it yet something else? – Ralph Apr 25 '16 at 22:43
  • I cannot find a reference is what the issue seems to be. – proseidon Apr 25 '16 at 22:45
  • If you cannot find the ADO library (which comes normally with Windows) then you can install it from this source for free: https://support.microsoft.com/en-us/kb/168335 But before you do that. Try to run the following line of code and see if you get an error message: `Dim conn1 As Object: Set conn1 = CreateObject("ADODB.Connection")`. If that runs without a problem then you have definately `ADO` already installed and can use it. – Ralph Apr 25 '16 at 22:57
  • Sorry I am not very knowledgeable with some of this database stuff. I tried installing it on my computer (windows 10) but the installer doesn't run. It looks like it's for older operating systems. – proseidon Apr 25 '16 at 23:07
  • Do you have MS Office installed (including MS Access)? If so, `ADO` also comes automatically with MS Access: http://allenbrowne.com/ser-38.html Also, have you tried running the above line of code? The essential problem is: if you cannot use ADO or DAO to send a query to the SQL server then there is nothing you can do to send your table the the server (unless your admin is running SSIS on the SQL server and writes a package to read your file). Yet, I severely doubt that non of them is available!! – Ralph Apr 25 '16 at 23:11
  • I cannot run that code inside of SQL. It doesn't recognize the keyword "Dim". So I'm not sure what that means. The code looked liked visual basic, not SQL, though, to me. – proseidon Apr 25 '16 at 23:15
  • Look here: https://www.experts-exchange.com/articles/3025/Retrieving-Data-From-Excel-Using-OPENROWSET.html – Alex Kudryashev Apr 25 '16 at 23:40
  • @proseidon Is this something you have to do once, or repeatedly? Also, how many records are in your Excel sheet? (Ballpark it for me) 1K, 10K, 100K, 1M? If under say....10K, and if you only have to do this once, I've used a pretty common Excel function to concatenate the necessary SQL before, for each line. Drop this formula into column C on your spreadsheet (assuming A & B are your key & value) and drag the formula down for each row. `= "UPDATE {my table} SET Value = '" & B1 & "' WHERE Key = '" & A1 & "'"` It's a hack, no doubt about it, but it might help, if it's a one-time thing. – BenM Apr 26 '16 at 00:59

1 Answers1

0

You can do that easily using SSIS. You can use excel source, Lookup transformation or Merge Join and OLE DB destination.

Drishya1
  • 254
  • 2
  • 4