By: Rajendra Gupta | Updated: 2017-01-06 | Comments (2) | Related: > PowerShell
Problem
In my previous tip PowerShell changes and new cmdlets for SQL Server 2016, we explored the new PowerShell module SQLSERVER along with the new cmdlets to get error log information. In this tip we will explore a few more new cmdlets that allow us to read and write SQL Server database tables.
Solution
With the release of Microsoft SQL Server 2016 Management Studio version 16.4.1, new cmdlets were introduced to read and write SQL Server database tables which we will cover in this tip.
To find these new PowerShell commands, you can run the below PowerShell command:
Get-Command -Name *SQL*TableData
We can see Read-SQLTableData and Write-SqlTableData are new cmdlets.
Read-SqlTableData PowerShell Cmdlet
As its name suggests, this cmdlet is used to read SQL Server table data.
The syntax for Read-SQLTableData is as follows:
Read-SqlTableData [[-ServerInstance] <String[]> ] [-ColumnName <String[]> ] [-ColumnOrder <String[]> ] [-ColumnOrderType <OrderType[]> ] [-ConnectionTimeout <Int32> ] [-Credential <PSCredential> ] [-DatabaseName <String> ] [-IgnoreProviderContext] [-OutputAs <OutputTypeSingleTable> {DataSet | DataTable | DataRows} ] [-SchemaName <String> ] [-SuppressProviderContextWarning] [-TableName <String> ] [-TopN <Int64> ] [ <CommonParameters>]
Suppose we want to read the top 2 rows from instance localhost\sql2016, database Adventureworks2012_2014, schema person and table person. The syntax is as follows:
read-sqltabledata -serverInstance localhost\sql2016 -databasename adventurworks2012_2014 -schemaname person -tablename person -top 2
We can also limit the number of columns to display as we do with a T-SQL statement. Suppose we want to display columns firstname, lastname, emailpromotion and want to display the output in the form of a datatable.
read-sqltabledata -serverInstance localhost\sql2016 -databasename adventurworks2012_2014 -schemaname person -tablename person -columnname firstname,lastname,emailpromotion -outputas datatable -top 2
We can further refine the query output by adding columnOrderType in descending or ascending order. This is similar to adding an ORDER BY clause for a T-SQL query.
read-sqltabledata -serverInstance localhost\sql2016 -databasename adventurworks2012_2014 -schemaname person -tablename person -columnname firstname,lastname,emailpromotion -columnordertype DESC,DESC,DESC -outputas datatable -top 20
We can change the columnOrderType for each column as shown below.
We can also access the tables using PowerShell from within SSMS. To access the tables go to Databases > Tables > Start PowerShell.
This provides flexibility to access SQL tables like file system tables. For example if we want to read the person.person table we can do the following to change to that object and then read the table.
-- this changes to this object cd person.person -- this reads the top 2 rows from this object read-sqltabledata -top 2
Below shows the output when we directly run the Read-SQLTableData to read the content.
Write-SqlTableData PowerShell Cmdlet
This cmdlet is used to insert data into a SQL Server table.
The syntax is as follows.
Write-SqlTableData [[-ServerInstance] <String[]> ] -InputData <PSObject> [-ConnectionTimeout <Int32> ] [-Credential <PSCredential> ] [-DatabaseName <String> ] [-Force] [-IgnoreProviderContext] [-Passthru] [-SchemaName <String> ] [-SuppressProviderContextWarning] [-TableName <String> ] [-Timeout <Int32> ] [ <CommonParameters>]
Suppose we want to capture the output of a SQL query into a database table. If the specified object doesn't exist, we can specify the -force parameter to create the missing SQL Server object. You will still need sufficient permission to create the object.
The below command runs a SELECT statement and writes the output to a table called testdatabases. Here we specified the -force parameter, so the table should be automatically created.
Invoke-Sqlcmd -ServerInstance localhost\sql2016 -Database master -OutputAs DataTables -Query " select name,database_id,compatibility_level,collation_name ,user_access_desc from sys.databases"|Write-SqlTableData -ServerInstance localhost\sql2016 -DatabaseName Test2 -SchemaName dbo -TableName testdatabases -Force
We can now see the table in the database with the data.
If we want to insert the data into existing table, we need to use -Passthru while running the query.
For this demo, I created a table named TestInsert that has 2 columns. In the below example, we are using -Inputdata to pass the values.
Write-SqlTableData -serverInstance localhost\sql2016 -database Test2 -TableName TestInsert -SchemaName dbo -InputData @{ 01='abc'; 02='xyz'} -PassThru
We can see the data has been inserted into the table.
We can use the SQLSERVER provider as well for Read-SqlTableData. To access the tables go to Databases > Tables > Start PowerShell.
In the below example, we are using the SQLSERVER provider to insert data with the -force parameter.
If we want to supress the warnings, we can specify -SuppressProviderContextWarning.
Next Steps
- PowerShell is being used more and more to administer SQL Server and more, so take some time to learn how you can take advantage of what PowerShell has to offer.
- Read more about SQL PowerShell: July 2016 update.
- Read more about SQL Server Management Studio - Changelog.
- Download and explore SQL Server 2016.
- Check out SQL Server 2016 Tips.
- Read more about SQL Server Management Studio with Windows PowerShell Cmdlets.
- Read more about SQL Server PowerShell Tips.
About the author
Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2017-01-06
