Read-​Sql​Table​Data

Syntax

Read-SqlTableData
    [[-Path] <String[]>]
    [-TopN <Int64>]
    [-ColumnName <String[]>]
    [-ColumnOrder <String[]>]
    [-ColumnOrderType <OrderType[]>]
    [-OutputAs <OutputTypeSingleTable>]
    [-InformationAction <ActionPreference>]
    [-InformationVariable <String>]
    [<CommonParameters>]

Read-SqlTableData
    [[-ServerInstance] <String[]>]
    [-TopN <Int64>]
    [-ColumnName <String[]>]
    [-ColumnOrder <String[]>]
    [-ColumnOrderType <OrderType[]>]
    [-OutputAs <OutputTypeSingleTable>]
    [-InformationAction <ActionPreference>]
    [-InformationVariable <String>]
    [-TableName <String>]
    [-DatabaseName <String>]
    [-SchemaName <String>]
    [-IgnoreProviderContext]
    [-SuppressProviderContextWarning]
    [-Credential <PSCredential>]
    [-ConnectionTimeout <Int32>]
    [<CommonParameters>]

Read-SqlTableData
    [-InputObject] <ScriptSchemaObjectBase[]>
    [-TopN <Int64>]
    [-ColumnName <String[]>]
    [-ColumnOrder <String[]>]
    [-ColumnOrderType <OrderType[]>]
    [-OutputAs <OutputTypeSingleTable>]
    [-InformationAction <ActionPreference>]
    [-InformationVariable <String>]
    [<CommonParameters>]

Description

The Read-SqlTableData cmdlet reads data stored in a table of a SQL database. You can select which columns to read, limit the number of rows, and sort and order columns.

You can use this cmdlet with the Windows PowerShell SQL provider. This cmdlet can infer information such as server, database, schema, and table from its current path.

This cmdlet supports the follow output formats:

• DataSet. An object of type System.Data.DataSet that contains one table.
• DataTable. An object of type System.Data.DataTable. The TableName property of this object is the same as the table that this cmdlet queries.
• DataRows. A collection of System.Data.DateRow objects.

Examples

Example 1: Get three rows from a table

PS C:\> Read-SqlTableData -ServerInstance "MyServer\MyInstance" -DatabaseName "MyDatabase" -SchemaName "dbo" -TableName "MyTable" -TopN 3
Id Name   Amount
-- ----   ------
10 AAAAA  -1.2
11 BBBBB  1.2
12 CCCCC  -1.0

This command gets the first three rows from table [MyDatabase].[dbo].[MyTable] on the MyServer\MyInstance instance. The TopN parameter specifies the number of rows.

Example 2: Display a whole table

PS C:\> cd SQLSERVER:\sql\MyServer\MyInstance\Databases\MyDatabase\Tables\dbo.CSVTable
PS SQLSERVER:\sql\MyServer\MyInstance\Databases\MyDatabase\Tables\dbo.CSVTable> Read-SqlTableData
Id Name Amount
-- ---- ------
10 AAAA -1.2
11 BBBB 1.2
12 CCCC -1.0

13 DDDD -2.0

The first command changes the location to be a table in the SQLSERVER provider. The command prompt reflects the new location. For more information, type Get-Help about_Providers.

The second command displays the whole table. Because the command uses its context, it does not specify any parameters.

Example 3: Display selected sorted columns

PS C:\> cd SQLSERVER:\sql\MyServer\MyInstance\Databases\MyDatabase\Tables\dbo.CSVTable
PS SQLSERVER:\sql\MyServer\MyInstance\Databases\MyDatabase\Tables\dbo.CSVTable> Read-SqlTableData -TopN 3 -ColumnName "Id","Name" -ColumnOrder "Id","Name" -ColumnOrderType DESC,ASC
Id Name
-- ----
12 CCCC
11 BBBB
10 AAAA

The first command changes the location to be a table in the SQLSERVER provider. The command prompt reflects the new location.

The second command displays two columns in the order specified by the ColumnOrder parameter. The command specifies how many rows to display and the sort order for the columns. Sorting is performed on the server.

Required Parameters

Optional Parameters

Related Links

• Read-SqlViewData
• Write-SqlTableData