2. Writing to SQL Server

 

Chapter 5 from Learn dbatools in a Month of Lunches by Chrissy LeMaire, Rob Sewell, Jess Pomfret, Cláudio Silva

In chapter 4, you were introduced to some of the common dbatools parameters. You also learned how to read the error log and gather information about databases on multiple instances. This chapter will focus on saving data to the place that SQL Server DBAs feel most comfortable keeping data: a table in a SQL Server database!

You will learn a number of different ways to write data to a SQL Server table using dbatools.

This chapter will be good to keep in mind as you go through the book, as it’s likely you will want to save your PowerShell output to a SQL Server database.

But for now, lets start with understanding PowerShell’s pipeline. The pipeline in PowerShell is a feature that you need to be familiar with to use PowerShell effectively. We are going to start with the pipeline because it enables us to write the output of any PowerShell command to SQL Server.

5.1  Piping Commands

One of PowerShell’s most powerful functionalities is the pipeline. The pipeline enables you to easily pass output from one command to another without using a cumbersome foreach. You have already seen this in action in Chapter 4 with $instances | Connect-DbaInstance. This takes the values in the $instances variable and pipes them to Connect-DbaInstance.

5.2  Writing to a database

5.2.1  Importing from a CSV to a database table

5.2.2  Importing to a database table from a dbatools command

5.2.3  Creating the database table first and then importing from CSV

5.2.4  Writing the results of other commands to a table

5.2.5  Writing the results of other commands to an Azure SQL Database

5.3  Copying Tables including their data

5.3.1  PowerShell splatting

5.4  Hands-on Lab