Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (3)   |   Related Tips: More > 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

-InputData Parameter Example

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.


Last Update:






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

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



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

 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 Back To Top

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 Back To Top

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.


Learn more about SQL Server tools