5 Writing to SQL Server

 

In chapter 4, you were introduced to some of the common dbatools parameters. You also learned how to gather information about databases on multiple instances. This chapter will focus on saving data to the place where SQL Server DBAs feel most comfortable keeping data: a table in a SQL Server database! You will learn a number of 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, because it’s likely you will want to save your PowerShell output to a SQL Server database.

But for now, let’s 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 file 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 a CSV file

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