-1

I have a SQL database table containing configuration for a large number of devices. I need to update the value of one of the properties (TemplateID) for about 1000 of these devices, based on a spreadsheet from the business side of the company. In a more mainstream programming language, I'd read the spreadsheet into an array and do a foreach against the array into a where against the dataset.

TSQL doesn't seem to have a foreach function, and I'm not sure how to read in a spreadsheet ... or what data type to use once I read it in.

What's the standard or proper way to do this TSQL?

3
  • A macro in the spreadsheet could handle that. Commented Apr 12, 2018 at 1:17
  • @GerardH.Pille there’s an interesting idea that chills my bones. So is that the standard answer? Use a different language and make an ODBC connection? Commented Apr 12, 2018 at 1:21
  • You are easily frightened. I never provide standard answers, I aim above that. Here I merely suggested a possible solution. Commented Apr 12, 2018 at 9:51

2 Answers 2

2

There are a few options but there is no “standard” way. Which one you choose will be influenced by your unique situation. I do have a couple of suggestions.

If you’re a whiz at Excel, you can write a formula that builds a T-SQL UPDATE statement from the column values. Copy and paste into SSMS and go. I’d go this route if this task is a one-off.

You can use the Import Data Wizard to read your spreadsheet into a table. From there you can use an UPDATE...FROM statement to modify your configuration table with the spreadsheet values. I’d go this route if it’s somewhat likely this will be a regular thing. Save your work and you have a process that can be completed in just a few minutes.

1
  • Thanks for pointing me in the right direction. I thought that if this could be done with Excel macros, it could surely be done with PowerShell. And yeah, there's a cmdlet for that. Commented Apr 12, 2018 at 5:23
0

Turns out this can be done with PowerShell these days, so that's the approach I'll take. Something like:

# Import the data from Excel with PSExcel module.
$KioskstoUpdate = Import-XLSX $ExcelFile;
ForEach ($Device in $KioskstoUpdate)
{
     $SQLQuery = "USE $SQLDatabase UPDATE MyDatabase.TableToChange SET TemplateID ='$TemplateID' WHERE ClientID = '$Device.ClientID'"
     $TemplateID = $KioskstoUpdate.TemplateID -as [int]
     Invoke-SQLcmd -query $SQLQuery -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
} 
2
  • 1
    I think the UPDATE needs a WHERE, otherwise it will update all rows of the table in each step of the iteration. Commented Apr 12, 2018 at 5:29
  • If ever an alias was more appropriate. Commented Apr 12, 2018 at 11:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.