Write-SqlTableData

Writes data to a table of a SQL database.

Syntax

Write-SqlTableData
     [-Force]
     -InputData <PSObject>
     [-Passthru]
     [-Timeout <Int32>]
     [[-Path] <String[]>]
     [<CommonParameters>]
Write-SqlTableData
     [-DatabaseName <String>]
     [-SchemaName <String>]
     [-TableName <String>]
     [-IgnoreProviderContext]
     [-SuppressProviderContextWarning]
     [-Force]
     -InputData <PSObject>
     [-Passthru]
     [-Timeout <Int32>]
     [[-ServerInstance] <String[]>]
     [-Credential <PSCredential>]
     [-ConnectionTimeout <Int32>]
     [<CommonParameters>]
Write-SqlTableData
     [-Force]
     -InputData <PSObject>
     [-Passthru]
     [-Timeout <Int32>]
     [-InputObject] <Table[]>
     [<CommonParameters>]

Description

The Write-SqlTableData cmdlet inserts data into a table of a SQL database. This cmdlet accepts the following input types the follow output formats:

  • System.Data.DataSet
  • System.Data.DataTable
  • System.Data.DateRow objects
  • Collection of objects

If you provide a DataSet, only the first table in the dataset is written to the database.

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 expects the table to exist. By default, the cmdlet appends data to that table.

If you specify the Force parameter, the cmdlet generate missing objects, which include the database, the table schema, and the table itself. This usage enables quick transfer of data into a database. The cmdlet infers the schema of the table from the data. The result may not be optimal. For example, strings are mapped to NVARCHAR(MAX).

Examples

Example 1: Write information about processes to a table

PS C:\> (Get-Process | Select-Object -Property Id,ProcessName,StartTime,UserProcessorTime,WorkingSet,Description) | Write-SqlTableData -ServerInstance "MyServer\MyInstance" -DatabaseName "MyDatabase" -SchemaName "dbo" -TableName "TaskManagerDump" -Force

This example gets information about processes that run on a system and writes it to a table.

The current cmdlet writes the data to MyDatabase.dbo.TaskManagerDump on MyServer\MyInstance. Because you specify the Force parameter, if the database, schema, and table do not exist, this cmdlet creates them.

Example 2: Write data to a table

PS C:\> cd SQLSERVER:\SQL\MyServer\MyInstance\Databases\MyDatabase\Tables
PS SQLSERVER:\SQL\MyServer\MyInstance\Databases\MyDatabase\Tables> $Table = Write-SqlTableData -TableName "KeyValuePairs" -SchemaName "dbo" -InputData @{ cca=10; cac='Hello'; aac=1.2 } -PassThru
PS SQLSERVER:\SQL\MyServer\MyInstance\Databases\MyDatabase\Tables> Read-SqlTableData -InputObject $Table

WARNING: Using provider context. Server = MyServer\MyInstance, Database = [MyDatabase]. 

Key Value
--- -----
aac   1.2
cac Hello
cca    10

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

The final command displays the contents of the $Table variable by using the Read-SqlTableData cmdlet.

Example 3: Import data from a file to a table

PS C:\> ,(Import-Csv -Path ".\a.csv" -Header "Id","Name","Amount") | Write-SqlTableData -ServerInstance "MyServer\MyInstance" -DatabaseName "MyDatabase" -SchemaName "dbo" -TableName "CSVTable" -Force
PS C:\> Read-SqlTableData -ServerInstance "MyServer\MyInstance" -DatabaseName "MyDatabase" -SchemaName "dbo" -TableName "CSVTable"

Id Name  Amount
-- ----  ------
10 AAAA  -1.2
11 BBBB   1.2
12 CCCC  -1.0

The first command imports the contents of a file by using the Import-Csv cmdlet. The file contains the following content:
    
10,AAAA,-1.2
11,BBBB,1.2
12,CCCC,-1.0
    
This example runs completely from the file prompt. It cannot use context information. Therefore, you must specify all relevant parameters.

Note the use of the "," in front of the line: this is to force PowerShell to pass the entire content of the file directly to
the **Write-SqlTableData** cmdlet, which in turn can do a bulk-insert (which is way more performant than a row by row insert)

Required Parameters

-InputData

Specifies the data to write to the database.

Typical input data is a System.Data.DataTable, but you can specify System.Data.DataSet or System.Data.DateRow* objects.

Type:PSObject
Position:Named
Default value:None
Accept pipeline input:True (ByValue)
Accept wildcard characters:False
-InputObject

Specifies an array of SQL Server Management Objects (SMO) objects that represent the table to which this cmdlet writes.

Type:Table[]
Position:1
Default value:None
Accept pipeline input:True (ByValue)
Accept wildcard characters:False

Optional Parameters

-ConnectionTimeout

Specifies the number of seconds to wait for a server connection before a time-out failure. The time-out value must be an integer between 0 and 65534. If 0 is specified, connection attempts do not time out.

Type:Int32
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-Credential

Specifies a PSCredential object for the connection to SQL Server. To obtain a credential object, use the Get-Credential cmdlet. For more information, type Get-Help Get-Credential.

Type:PSCredential
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-DatabaseName

Specifies the name of the database that contains the table.

The cmdlet supports quoting the value. You do not have to quote or escape special characters.

Type:String
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-Force

Indicates that this cmdlet creates missing SQL Server objects. These include the database, schema, and table. You must have appropriate credentials to create these objects.

If you do not specify this parameter for missing objects, the cmdlet returns an error.

Type:SwitchParameter
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-IgnoreProviderContext

Indicates that this cmdlet does not use the current context to override the values of the ServerInstance, DatabaseName, SchemaName, and TableName parameters. If you do not specify this parameter, the cmdlet ignores the values of these parameters, if possible, in favor of the context in which you run the cmdlet.

Type:SwitchParameter
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-Passthru

Indicates that this cmdlet returns an SMO.Table object. This object represents the table that includes the added data. You can operate on the table after the write operation.

Type:SwitchParameter
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-Path

Specifies the full path in the context of the SQL Provider of the table where this cmdlet writes data.

Type:String[]
Position:1
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-SchemaName

Specifies the name of the schema for the table.

If you run this cmdlet in the context of a database or a child item of a database, the cmdlet ignores this parameter value. Specify the IgnoreProviderContext parameter for the cmdlet to use the value of the SchemaName parameter anyway.

The cmdlet supports quoting the value. You do not have to quote or escape special characters.

Type:String
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-ServerInstance

Specifies the name of an instance of SQL Server. For the default instance, specify the computer name. For named instances, use the format ComputerName\InstanceName.

If you run this cmdlet in the context of a database or a child item of a database, the cmdlet ignores this parameter value. Specify the IgnoreProviderContext parameter for the cmdlet to use the value of the ServerInstance parameter anyway.

Type:String[]
Position:1
Default value:None
Accept pipeline input:True (ByValue)
Accept wildcard characters:False
-SuppressProviderContextWarning

Indicates that this cmdlet suppresses the warning message that states that the cmdlet uses the provider context.

Type:SwitchParameter
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-TableName

Specifies the name of the table from which this cmdlet reads.

If you run this cmdlet in the context of a database or a child item of a database, the cmdlet ignores this parameter value. Specify the IgnoreProviderContext parameter for the cmdlet to use the value of the TableName parameter anyway.

The cmdlet supports quoting the value. You do not have to quote or escape special characters.

Type:String
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-Timeout

Specifies a time-out value, in seconds, for the write operation. If you do not specify a value, the cmdlet uses a default value.

Type:Int32
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False

Inputs

System.Management.Automation.PSObject

Inputs

System.String[]

Inputs

Microsoft.SqlServer.Management.Smo.Table[]