I needed to update a database table with all the domains I had deleted back in January this year. There were actually about 32 of them, and I have only included a sample of 8 in the code below. I had a series of emails from GoDaddy, so I found it quickest to just enter the date/time of th eemail and the domain in NotePad++.

This was a one time script, so I didn’t even read the file, I just copied the data directly into my Powershell script, and put it in here doc. Would have only taken 1 minute or two to read the file with a “get-contents” though.

My goal was to generate a series of SQL update statements that I could copy into SSMS (SQL Studio Management Studio) and run there. Of course, we could do the updates from PowerShell, but as I said, this was a quick get-it-done one-time one-off task (hopefully).

After I ran the command in SQL, I realized two things. I need to trim the website names (some had an extra space on the end).
And I needed a Print statement so SQL could show me website. It shows the “rows affected = 0” or “rows affected = 1” for each statement. But when I ran them all at once, the ones that were misspelled were hard to find (I had to count how many from the top or bottom).

In this specific case, all the deletes were on the same date, so I didn’t re-type that into the input; rather, hard-coded in the SQL update statement in the PowerShell.

<pre>
11:58 learn-guitar-online-video.com 
11:58 freelearntoplayguitar.com 
11:58 learn-ultimate-guitar-online.com 
12:00 learntoplaysongguitar.com 
12:19 parismages.com
12:22 swc2009.co.za
12:33 venaproexposed.org
12:42 littleaccelerators.com
</pre>

<pre>
cls 
$deletedDomains = @"
11:58 learn-guitar-online-video.com 
11:58 freelearntoplayguitar.com 
11:58 learn-ultimate-guitar-online.com 
12:00 learntoplaysongguitar.com 
12:19 parismages.com
12:22 swc2009.co.za
12:33 venaproexposed.org
12:42 littleaccelerators.com
"@

#split based on the new line character 
$rows = $deletedDomains.Split("`n")

foreach ($row in $rows)
{
   #write-host "row=$row"
   $hhmm = $row.substring(0,5)
   $website = $row.substring(6).Trim()
   #Write-host "HHMM=$hhmm website=$website"
   $sql = "update SEOWebSite set seows_dateDeleted = '2020-01-19 $($hhmm)' where seows_name = '$website'";
   Write-Host "print 'updating: $website'" 
   Write-Host $sql 
}

</pre>

I just copied the resulting SQL from the output window, pasted it into SSMS, and ran it! Easy-Peasy!
One minosr issue is that it wraps if the row was over 80 characters, so I manually had to fix a few of those.

print 'updating: learn-guitar-online-video.com'
update SEOWebSite set seows_dateDeleted = '2020-01-19 11:58' where seows_name = 'learn-guitar-online-video.com'
print 'updating: freelearntoplayguitar.com'
update SEOWebSite set seows_dateDeleted = '2020-01-19 11:58' where seows_name = 'freelearntoplayguitar.com'
print 'updating: learn-ultimate-guitar-online.com'
update SEOWebSite set seows_dateDeleted = '2020-01-19 11:58' where seows_name = 'learn-ultimate-guitar-online.co
m'
print 'updating: learntoplaysongguitar.com'
update SEOWebSite set seows_dateDeleted = '2020-01-19 12:00' where seows_name = 'learntoplaysongguitar.com'
print 'updating: parismages.com'
update SEOWebSite set seows_dateDeleted = '2020-01-19 12:19' where seows_name = 'parismages.com'
print 'updating: swc2009.co.za'
update SEOWebSite set seows_dateDeleted = '2020-01-19 12:22' where seows_name = 'swc2009.co.za'
print 'updating: venaproexposed.org'
update SEOWebSite set seows_dateDeleted = '2020-01-19 12:33' where seows_name = 'venaproexposed.org'
print 'updating: littleaccelerators.com'
update SEOWebSite set seows_dateDeleted = '2020-01-19 12:42' where seows_name = 'littleaccelerators.com'