DataTable

Mass Merge Data From PowerShell To SQL

Merge-DataTableToSQL
Standard

If you have data that you’re collecting with PowerShell and wan’t to insert new entries or update existing ones in a Database then a SQL MERGE function is what you need. I frequently run scheduled queries to collect information that I want to submit to SQL for reporting purposes. When testing this function I was able to merge a DataTable with 100,000+ rows and 20+ columns very quickly. A unique identifier in the results is used as a primary key in the merger. When there’s a match it updates that row, if it doesn’t exist already it inserts it as a new row. To do this effectively a temporary table is created by first looking up the primary key in the targeted table (if there’s one); then builds the query with all the columns in the source. It also compares the source columns to the target table columns to make sure they correspond for the column mapping. The DataTable is then bulk copied to the temporary table, this becomes the source of the merge to the target table. After which the temporary table is dropped.

Continue reading

Bulk Copy Data From PowerShell to SQL

Copy-DataTableToSQL
Standard

Being able to quickly submit data from PowerShell to SQL is something I often do. Usually I want the results inserted into a SQL table. Being able to do so in bulk without needing a for each insert loop is very helpful and much faster. The easiest way to do this is to use sqlclient.sqlbulkcopy. It requires that the input variable be a DataTable object so if it’s not already use the ConvertTo-DataTable function I wrote earlier. Once you have your data in the DataTable you can use it as the source in this Copy-DataTableToSQL function I wrote.

Continue reading

Get SQL Table to PowerShell Simply

Get-SqlToDataTable
Standard

For many reasons you may want to retrieve data from SQL to be used in PowerShell. Maybe it’s data from a service’s database or data that was stored in SQL from another script. I wrote this function to quickly get a SQL table into a PowerShell DataTable object. It’s provides parameters for setting the target server, database, table, and where statement. Timeout defaults to 60 seconds for the SQL client connection but can be overridden. Security defaults to SSPI, but can be set, and I didn’t add any credentials arguments as the intent is that the script is run as a user with the appropriate permissions to select data from the database. In the case of Microsoft SQL the dbreader role at a minimum is required assigned to your Windows credentials.

Continue reading

Function to Convert PowerShell Objects to DataTables

ConvertTo-DataTable
Standard

There’s many ways to store data in PowerShell but I find DataTables to be the most useful. The extra capabilities available are very flexible. I’ve made this function to quickly convert arrays, hashes, and other objects to DataTables quickly. I’ll often use this in combination with SQL connections to complete bulk copies of entire tables in one command, or even user that in combination with SQL merge statements to insert and update matched records. Continue reading