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