Getting Total Number of Rows Copied in SqlBulkCopy Using PowerShell

Getting the total number of inserted rows for SqlBulkCopy should be easier, but I believe the easiest (and fastest) is by using reflection and some C# code within your PowerShell script. Thanks to user601543 at stackoverflow, I was able translate his code for use within one of my own scripts.

rowscopied2

The Code

Here’s an approximation of how I did it. First, I added the code using Add-Type. Then I called it using [System.Data.SqlClient.SqlBulkCopyExtension]::RowsCopiedCount($bulkcopy)

As the user noted: “This count does not take into consideration the number of rows actually inserted when Ignore Duplicates is set to ON.”

Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, and holds a number of certifications, including those relating to SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in PowerShell, SQL Server
2 comments on “Getting Total Number of Rows Copied in SqlBulkCopy Using PowerShell
  1. Mike Price says:

    Is there any way to get a count of the Ignored Duplicates? I’d like to be able to get this for checks-and-balances to verify the number of rows inserted plus those igonred equal the number of records on the input.

  2. Hans Tacken says:

    Hi Chrissy,

    Thanks for your nice blog. I’m using your solution above in my scripts to download data from Oracle to SQL Server. I have some tables that contain more than 2 billion rows and therefore the variable that stores the count overflows. I tried changing the int to a long but that gives an error.
    Do you have a suggestion how to fix the script so it can handle more than 2^32 rows? (I’m not a C# programmer and don’t understand exactly what the code is doing)

    Thanks,
    Hans

Leave a Reply

Your email address will not be published. Required fields are marked *

*