title: How to insert multiple sheets of Excel data into one table in SQL Server?
body: <p>I have written a PowerShell script to download Excel data from a website:</p>
  
  <pre><code>## Download the NSCC-MPID-Directory.xls from website
  $webclient = New-Object System.Net.webclient
  $source = "http://www.dtcc.com/~/media/Files/Downloads/client-center/NSCC/NSCC-MPID-Directory.xls"
  $destination = "C:\LSC\NSCC_MPID\NSCC-MPID-Directory.xls"
  $webclient.downloadfile($source, $destination)
  $filename = "NSCC-MPID-Directory.xls"
  </code></pre>
  
  <p>I want to write a <code>bcp</code> command to insert the Excel data into a table in my SQL Server database. But there are multiple sheets in the Excel file, and the following <code>bcp</code> will not work:</p>
  
  <pre><code>bcp SIAC.dbo.NSCC_MPID in $filename  -S database1 -T -c -q -m9999
  </code></pre>
  
  <p>Furthermore, I wonder if there is a way to get the sheet name in a separate column or I could insert each sheet's data into a different table. Because different sheets have the same format of data :</p>
  
  <p><a href="https://i.stack.imgur.com/0Nck6.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/0Nck6.jpg" alt="dataset"></a></p>
  
  <p>But they have different category which is shown in the sheet name like this:</p>
  
  <p><a href="https://i.stack.imgur.com/JIrQO.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JIrQO.jpg" alt="multiple sheet name"></a></p>
  
  <p>I want to separate them in one table or insert them in the same table but create another column named category, for example "OTC"</p>
  
  <p>I am really confused about how to deal with this... would anyone give me some suggestion about this? Thanks.</p>