title: How do I BULK INSERT with additional column showing filename?
body: <p>I'm still getting used to SQL, so before I get to using stored procedure, I would like to understand how to use BULK INSERT effectively first.</p>
  
  <p>I need to combine 50+ csv files and dump them into an SQL table. The problem is, I'd like to be able to tell each record apart (as in, each record belongs to a certain csv file, which I will identify by the file name). </p>
  
  <p>Here's a small example of what I want:</p>
  
  <pre><code>CREATE TABLE ResultsDump
  (
      PC FLOAT,
      Amp VARCHAR(50),
      RCS VARCHAR(50),
      CW VARCHAR(50),
      State0 VARCHAR(50),
      State1 VARCHAR(50),
  )
  BULK INSERT ResultsDump
      FROM 'c:\distance1000_7_13_2010_1_13PM_Avery DennisonAD_2300008_10S_Lock.csv' 
      WITH 
      ( 
          FIRSTROW = 2, 
          MAXERRORS = 0, 
          FIELDTERMINATOR = ',', 
          ROWTERMINATOR = '\n'
      )
  BULK INSERT ResultsDump
      FROM 'c:\distance1000_7_13_2010_2_27PM_Avery DennisonAD_2300009_10S_Lock.csv' 
      WITH 
      ( 
          FIRSTROW = 2, 
          MAXERRORS = 0, 
          FIELDTERMINATOR = ',', 
          ROWTERMINATOR = '\n'
      )
  BULK INSERT ResultsDump
      FROM 'C:\distance1000_7_13_2010_2_58PM_Avery DennisonAD_230000A_10S_Lock.csv' 
      WITH 
      ( 
          FIRSTROW = 2, 
          MAXERRORS = 0, 
          FIELDTERMINATOR = ',', 
          ROWTERMINATOR = '\n'
      )
  BULK INSERT ResultsDump
      FROM 'c:\distance1000_7_13_2010_3_21PM_Avery DennisonAD_230000B_10S_Lock.csv' 
      WITH 
      ( 
          FIRSTROW = 2, 
          MAXERRORS = 0, 
          FIELDTERMINATOR = ',', 
          ROWTERMINATOR = '\n'
      )
  BULK INSERT ResultsDump
      FROM 'c:\distance1000_7_13_2010_3_41PM_Avery DennisonAD_230000C_10S_Lock.csv' 
      WITH 
      ( 
          FIRSTROW = 2, 
          MAXERRORS = 0, 
          FIELDTERMINATOR = ',', 
          ROWTERMINATOR = '\n'
      )
  </code></pre>
  
  <p>I know this is an inefficient way of doing things, but I definitely like to figure out how to manually dump one file in the SQL table in the format I want before I start to create a stored procedure.</p>
  
  <p>In the new table, I want something like this:</p>
  
  <pre><code>FileName,PC,Amp,RCS,CW,State0,State1
  c:\distance1000_7_13_2010_1_13PM_Avery DennisonAD_2300008_10S_Lock.csv, ...
  ...
  ...
  c:\distance1000_7_13_2010_2_27PM_Avery DennisonAD_2300009_10S_Lock.csv, ...
  ...
  ...
  c:\distance1000_7_13_2010_2_58PM_Avery DennisonAD_230000A_10S_Lock.csv, ...
  ...
  ...
  </code></pre>
  
  <p>Any simple suggestions or referrals to specific functions would be great! Remember, I'm getting used to SQL and it'd be great if I could take this one step at a time, that's why I'm starting with such a simple question.</p>
  
  <p>Thanks in advance!</p>