未加星标

New PowerShell cmdlets to read and write SQL Server tables

字体大小 | |
[系统(windows) 所属分类 系统(windows) | 发布者 店小二04 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

By:Rajendra Gupta || Related Tips:More >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
New PowerShell cmdlets to read and write SQL Server tables

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
New PowerShell cmdlets to read and write SQL Server tables

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
New PowerShell cmdlets to read and write SQL Server tables

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
New PowerShell cmdlets to read and write SQL Server tables

We can change the columnOrderType for each column as shown below.


New PowerShell cmdlets to read and write SQL Server tables

We can also access the tables using PowerShell from within SSMS. To access the tables go to Databases > Tables > Start PowerShell.


New PowerShell cmdlets to read and write SQL Server tables

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.


New PowerShell cmdlets to read and write SQL Server tables
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
New PowerShell cmdlets to read and write SQL Server tables

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


New PowerShell cmdlets to read and write SQL Server tables

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
New PowerShell cmdlets to read and write SQL Server tables

We can see the data has been inserted into the table.


New PowerShell cmdlets to read and write SQL Server tables

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.


New PowerShell cmdlets to read and write SQL Server tables

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


New PowerShell cmdlets to read and write SQL Server tables
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 outSQL Server 2016 Tips. Read more about SQL Server Management Studio with windows PowerS

本文系统(windows)相关术语:三级网络技术 计算机三级网络技术 网络技术基础 计算机网络技术

主题: SQLPowerShellSQL ServerWindows
分页:12
转载请注明
本文标题:New PowerShell cmdlets to read and write SQL Server tables
本站链接:http://www.codesec.net/view/520662.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 系统(windows) | 评论(0) | 阅读(58)