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

By: Aaron Nelson | 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];"

Invoke-Sqlcmd example shwing when navigating an instance, you can leave off the -ServerInstance parameter

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];"

If we navigate further down the SQL PowerShell Provider, underneath a database, we can then leave off the -Database paramete with Invoke-Sqlcmd

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

Pipeline Example with Invoke-Sqlcmd and Write-SqlTableData

-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

Comments For This Article

Thursday, March 5, 2020 - 9:40:13 AM - Mohan

Hi, I used the query similar to this 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 --------

only the column name differs when I run the script in sql server job it fails and shows the error 'A parameter cannot be found that matches parameter name 'OutputAs'. '. Process Exit Code -1. .

But the parameter Outputas is one of the parameter for Invoke-sqlcmd

Thursday, November 15, 2018 - 2:54:44 AM - pregunton

Modules:
SQLPS: This module is included with the SQL Server installation (for backwards compatibility), but is no longer being updated. The most up-to-date PowerShell module is the SqlServer module.
SqlServer: This module includes new cmdlets to support the latest SQL features. The module also contains updated versions of the cmdlets in SQLPS.

Invoke-SQLCMD not supports:

# ## - Block of Code required to build SQL Authentication:
# $MySQL.ConnectionContext.LoginSecure = $false;
# $MySQL.ConnectionContext.set_Login($SQLUserName);
# $SqlUserPwd = ConvertTo-SecureString $sqlPwd -AsPlainText -Force;
# $MySQL.ConnectionContext.set_SecurePassword($SqlUserPwd);

In Windows 10, I try Import-Module -name SQLPS, I get error: not found assembly 'Microsoft.SqlServer.ConnectionInfo, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'.

Maybe I have not latest versión SQLPS. Anyways, SQLPS is no longer being updated.

Thursday, January 26, 2017 - 9:29:41 PM - Aaron Nelson

Scott,

There's no need to go to that much trouble. The SQL Tools team is aware of SqlBulkCopy and did in fact usE it to do the heavy lifting in the Write-SqlTableData cmdlet.

While the examples I used in this particular article showed how to use the cmdlets with the SQL PowerShell Provider, they can be used outside of the provider by simply adding the -ServerInstance parameter.

Thursday, January 26, 2017 - 10:50:17 AM - Scott

I've had good luck with inserts using the SqlBulkCopy object. Assuming you have a DataTable, which could be from a SQL query or something you created from scratch and loaded from some other source, plus a SqlConnection object for the destination server/database, all you need is:

$SQLBulkCopy = New-Object System.Data.SqlClient.SqlBulkCopy -ArgumentList $SQLConnection,
([System.Data.SqlClient.SqlBulkCopyOptions]::TableLock), $null -Property @{DestinationTableName=$tablename};
$SQLBulkCopy.WriteToServer($DataTable);

If the Write-SqlTableData cmdlet is using normal logged INSERT commands, I would expect the bulk copy to be faster (although I haven't done much testing).

Thursday, January 26, 2017 - 9:30:59 AM - Jeff Moden

This is great news! I can think of some great uses for the Write-SqlTableData cmdlet. Thank you for the article, Aaron.

Shifting gears a bit, have you done any high volume performance/memory usage testing on such things? It would be interesting to compare something like "Native" BCP query out and import against the PoSh methods.