Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

New PowerShell cmdlets to read and write SQL Server tables


By:   |   Read Comments   |   Related Tips: More > PowerShell

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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

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


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


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


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 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.

access the tables using PowerShell from within SSMS

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.

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


SELECT statement and writes the output to a table called testdatabases

We can now see the table in the database with the data.

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


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.

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.

SQLSERVER provider to insert data with the -force parameter.

If we want to supress the warnings, we can specify -SuppressProviderContextWarning.

Supress the warnings with -SuppressProviderContextWarning
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips
Related Resources





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 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools