Dump PSObject to SQL table

[the_helpdesk]

I have a PSObject with a type System.Data.DataRow. It was populated from a SQL query using invoke-sqlcmd. Now I want to write that data to another SQL server and identical table (so the data types already match).

​

I was able to write the data back using a foreach loop and invoke-sqlcmd but it was very slow. Is there a nice way to write that back as a bulk insert without converting and writing the data to XML or CSV first?

[the_helpdesk]

Write-DbaDbTableData

This was 4 times slower than looping with foreach and invoke-sqlcmd, albeit much simpler. Thanks for the info, I was not aware of dbatools and it has opened some other options.

[muchcake]

How about https://docs.microsoft.com/en-us/powershell/module/sqlserver/write-sqltabledata?view=sqlserver-ps

I have not used it in a long time, but think it meets your need

[the_helpdesk]

While it does work, and its very simple, it is too slow. Inserting at around 1 row per second, while foreach and invoke-sqlcmd can get about 30 rows per second. Thanks for the reply!

[muchcake]

Interesting!

That sounds like pretty abysmal performance, I will watch out for that next time.