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.


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 PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. Always an avid scripter, she attended the Monad session at Microsoft’s Professional Developers Conference in Los Angeles back in 2005 and has worked and played with PowerShell ever since. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and helps maintain in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. 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)


Leave a Reply

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