PowerShell Invoke-SQLCmd outputs DataTables you can INSERT into SQL Server

By: Aaron Nelson   |   Updated: 2017-01-26   |   Comments (5)   |   Related: > PowerShell

Problem

You need to take an existing SQL query and run it against a SQL Server and then take the results and save them to a different SQL Server without using Linked Servers.

Solution

One of the first enhancements made to an existing cmdlet for SSMS 2016 was to add the -OutputAs parameter to the Invoke-SQLCmd which allows you to output your query results as a .Net DataSet, DataTable, or DataRow. Multiple solutions use .Net DataTables as a quick and easy to take rows of data and insert them into SQL Server.

Invoke-SqlCmd Examples

Let’s first get acclimated with the Invoke-SqlCmd cmdlet. Invoke-SqlCmd is versatile because it can be used on its own or from within the SQL PowerShell Provider. When used within the SQL Server Provider it is possible to leave off some of the parameters, depending on where you have navigated. When navigating an instance, you can leave off the -ServerInstance parameter.

CD SQLSERVER:\sql\localhost\SQL2016     
        
Invoke-Sqlcmd -Database WideWorldImporters -Query "
SELECT DB_NAME([database_id]) AS DatabaseName,
       [file_id],
       [filegroup_id],
       ( [total_page_count] / 128 ) AS TotalSpaceInMB,
       ( [allocated_extent_page_count] / 128 ) AS SpaceUsedInMB,
       ( [unallocated_extent_page_count] / 128 ) AS SpaceFreeInMB
  FROM [AirStats].[sys].[dm_db_file_space_usage];"

If we navigate further down the SQL PowerShell Provider, underneath a database, we can then leave off the -Database parameter.

Invoke-Sqlcmd -Query "
SELECT DB_NAME([database_id]) AS DatabaseName,
       [file_id],
       [filegroup_id],
       ( [total_page_count] / 128 ) AS TotalSpaceInMB,
       ( [allocated_extent_page_count] / 128 ) AS SpaceUsedInMB,
       ( [unallocated_extent_page_count] / 128 ) AS SpaceFreeInMB
  FROM [AirStats].[sys].[dm_db_file_space_usage];"

Author's Note

The use of "| Format-Table -AutoSize" is for readability only. It is critical that you do not include this Format-Table cmdlet when working with .Net DataTables in SQL PowerShell because it will destroy the .Net DataTable and prevent you from being able to insert the data. That is why it is being omitted from the code samples.

The Invoke-SqlCmd cmdlet allows us to use the -Query parameter to specify our query by enclosing it in quotes or by supplying a variable with the query in it. Alternatively, you can supply the query as a .SQL file when you supply the -InputFile parameter.

Add the OutputAs Parameter to Invoke-SqlCmd

By adding the -OutputAs parameter to Invoke-SqlCmd and picking the DataTables choice from the parameter set, you can output the results as a .Net DataTable, which works very well in concert with the Write-SqlTableData cmdlet.

Write-SqlTableData Parameter Example

The Write-SqlTableData cmdlet is a game changer for SQL Server. This cmdlet was modeled off of the Write-DataTable function that Chad Miller made available to the community years ago. This cmdlet is primarily for inserting data into tables in your database, but it also comes with a -Force parameter which will go ahead and create a table for you based off of the column names and data types of your .Net DataTable that you are inputting to it.

Write-DataTable also has -ServerInstance and-DatabaseName parameters, and likewise, depending on where you have navigated to within the SQL PowerShell Provider, you can use less of these parameters, including the -SchemaName & -TableName parameters. In addition, this cmdlet comes with a -IgnoreProviderContext parameter as a sort of safety measure to make sure that the current location in the SQL PowerShell Provider is ignored and the values for the -ServerInstance, -DatabaseName, -SchemaName, & -TableName parameters are used instead.

Putting all this in action, you can run a query against one instance of SQL Server, and write the results to a completely different instance of SQL Server, without having to use something like a linked server.

There are two different approaches to using the Write-SqlTableData cmdlet that I think are important to call out.

Pipeline Example with Invoke-Sqlcmd and Write-SqlTableData

First you can use the Invoke-Sqlcmd cmdlet (or other commands that output a .Net DataTable) and pipe those results directly into the Write-SqlTableData cmdlet like this.

Invoke-Sqlcmd -Query "
SELECT DB_NAME([database_id]) AS DatabaseName,
       [file_id],
       [filegroup_id],
       ( [total_page_count] / 128 ) AS TotalSpaceInMB,
       ( [allocated_extent_page_count] / 128 ) AS SpaceUsedInMB,
       ( [unallocated_extent_page_count] / 128 ) AS SpaceFreeInMB
  FROM [sys].[dm_db_file_space_usage];" -OutputAs DataTables |             
Write-SqlTableData -ServerInstance localhost -DatabaseName tempdb -SchemaName dbo -TableName DataFileSizes -Force

-InputData Parameter Example

Alternatively, you can have the command gathering your data store the results in a PowerShell variable and then write those results to your SQL Server separately, this approach is important to know about because it allows you to run additional logic before you write your data.

$Results = Invoke-Sqlcmd -Database WideWorldImporters -Query "
SELECT DB_NAME([database_id]) AS DatabaseName,
       [file_id],
       [filegroup_id],
       ( [total_page_count] / 128 ) AS TotalSpaceInMB,
       ( [allocated_extent_page_count] / 128 ) AS SpaceUsedInMB,
       ( [unallocated_extent_page_count] / 128 ) AS SpaceFreeInMB
  FROM [sys].[dm_db_file_space_usage];" -OutputAs DataTables            
Write-SqlTableData -ServerInstance localhost -DatabaseName tempdb -SchemaName dbo -TableName DataFileSizes -InputData $Results

Bonus Tip:

If the table you want to write your data to doesn’t exist yet, you can add the -Force parameter to the Write-SqlTableData cmdlet and the cmdlet will create the table for you, name all the columns based on the column names you have in your .Net DataTable, and make an attempt to convert the .Net datatypes into SQL Server datatypes. I say 'attempt' because this cmdlet errs on the side of larger SQL Server datatypes to make sure it can insert all the data it has.

Next Steps

• The Invoke-Sqlcmd & Write-SqlTableData are two cmdlets available now in the new SqlServer module which comes in the latest version of SSMS 2016. These cmdlets also work with earlier versions of SQL Server which make them an extremely handy tool to have in your toolkit.
• For more information on the New SqlServer module see this post on the SQL Server team blog.
• To download the latest version of SSMS 2016.
• Check out more SQL Server PowerShell Tips.

About the author

Aaron Nelson is a Senior SQL Server Architect with over 10 years experience in architecture, BI, development and tuning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips