Jeff,

A while back I experimented with TVPs and Powershell.

Basically, you create a table on a SQL server, and create a stored proc with a TVP to handle the inserts.

In Powershell, you create a datatable object and populate it. Then create a sqlcommand object of type stored proc, add a parm that will be a TVP, then set the value of the parm = datatable, and call the sproc.

It's not exactly elegant, and you still have to populate the PoSh datatable RBAR.

But you can pass many rows to SQL Server in a single call.

I adapted the following from code found on the net. I wish I could remember where for credit.

#Datatable object

$Datatable = New-Object system.Data.DataTable

$null = $Datatable.Columns.Add("Server", "System.String")

$null = $Datatable.Columns.Add("DBName", "System.String")

$null = $Datatable.Columns.Add("TableName", "System.String")

etc ....

$Data = <some result set you're going to insert into your $Datatable>

if ($Data)

{

foreach ($Row in $Data)

{

$Info = $Row.ItemArray

if ($Info)

{

$null = $Datatable.Rows.Add($Info)

}

}

}

#Connection and Query Info

$serverName = "<MyServer>"

$databaseName = "<MyDB>"

$query = "<MyInsertSproc>"

#Connect

$connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;"

$conn = new-object System.Data.SqlClient.SqlConnection $connString

$conn.Open()

#Create Sqlcommand type and params

$cmd = new-object System.Data.SqlClient.SqlCommand

$cmd.Connection = $conn

$cmd.CommandType = [System.Data.CommandType]"StoredProcedure"

$cmd.CommandText = $query

$null = $cmd.Parameters.Add("@TVP", [System.Data.SqlDbType]::Structured)

$cmd.Parameters["@TVP"].Value = $Datatable

#Create and fill dataset

$ds = New-Object system.Data.DataSet

$da = New-Object system.Data.SqlClient.SqlDataAdapter($cmd)

$null = $da.fill($ds)

$conn.Close()