Split out delimited text within SQL Server

Sometimes splitting delimited text within SQL server is easier than splitting the text on the way in via SSIS etc. The following (all shamelessly stolen from the link below) is an example of splitting the following myAddress column:

into the following columns:

using this SQL:

SELECT 

     REVERSE(PARSENAME(REPLACE(REVERSE(myAddress), ',', '.'), 1)) AS [Street]

   , REVERSE(PARSENAME(REPLACE(REVERSE(myAddress), ',', '.'), 2)) AS [City]

   , REVERSE(PARSENAME(REPLACE(REVERSE(myAddress), ',', '.'), 3)) AS [State]

FROM dbo.custAddress;

GO

Reference:

https://www.mssqltips.com/sqlservertip/6321/split-delimited-string-into-columns-in-sql-server-with-parsename/

Getting started with Python Web Scraping

Getting started with Python on a Mac was fairly straightforward, but I had a few stumbling blocks on Windows. The easiest way to get started with Python, a decent IDE & terminal, and additional libraries, was to install Anaconda on Windows, and use the Spyder IDE.

Using Beautiful Soup for web scraping, the following is a script I wrote to get the current top non fiction audiobooks from Audible:

# Script to get top Audible personal development books  

import requests 
from bs4 import BeautifulSoup 

URL = 'https://www.amazon.co.uk/Best-Sellers-Books-Self-Help-How/zgbs/books/2996349031/ref=zg_bs_nav_b_3_2996114031' 
page = requests.get(URL) 
soup = BeautifulSoup(page.content, 'html.parser') 
results = soup.find('ol', class_ = 'a-ordered-list a-vertical') 

list_elems = results.find_all('li', class_ = 'zg-item-immersion') 
for list_elem in list_elems[:50]: 
    rank_elem = list_elem.find('span', class_ = 'zg-badge-text') 
    title_elem = list_elem.find('div', class_ = 'p13n-sc-truncate p13n-sc-line-clamp-1') 
    author_elem = list_elem.find('span', class_ = 'a-size-small a-color-base') 
  
    title_elem = title_elem.text.replace('  ', '') 
    title_elem = title_elem.replace('\n', '')  

    print(rank_elem.text.replace('#', '') + ' - ' + title_elem + ' - ' + author_elem.text.replace('\t','')) 

Returns the following:

 

SQL Server Migration using dbatools Powershell Module

Migrations between SQL Servers can be laborious, and doing them manually leaves a lot of room for human error. Scripting a migration using the dbatools Powershell module is incredibly simple, quick and robust.

Prerequisites

Powershell with dbatools module installed:

https://dbatools.io/download/

Steps

Run the following Powershell:

    $startDbaMigrationSplat = @{

    Source = 'sourceServer'

    Destination = 'destinationServer'

    BackupRestore = $true

    SharedPath = '\\path\both-servers-can-access'

    }

    Start-DbaMigration @startDbaMigrationSplat -Force | Select * | Out-GridView

This will migrate everything associated with the source instance, to the destination instance (inc logins, linked servers, startup procs...), using a backup and restore method for moving the databases. Use the -Exclude flag (see documentation link below) to exclude bits you don't want to be migrated over

Resources

dbatools migration documentation: https://docs.dbatools.io/#Start-DbaMigration

YouTube vid demo of dbatools migration: https://www.youtube.com/watch?v=hg8tovMRX2k

Fix issues with Powershell: https://stackoverflow.com/questions/4037939/powershell-says-execution-of-scripts-is-disabled-on-this-system

Stack Exchange article on migrations: https://dba.stackexchange.com/questions/258559/side-by-side-upgrade-and-migrate-sql