title: Variables in MS SQL Bulk Insert path string
body: <p>I need to import 30 .csv files into an MS SQL SERVER database. I can use BULK INSERT, but because of the number of files I want to do it with loop. Each file is named as DP(1).csv, DP(2).csv, DP(3).csv, ..., DP(30).csv. </p>
  
  <p>I did write a <code>WHILE</code> loop and with a counter that can also be used to identify a file name, but I have problems with the syntax when including the counter variable in the path name. Here is my code:</p>
  
  <pre><code>DECLARE @COUNT INT
  
  SET @COUNT = 1
  USE Db_Pc 
  
  WHILE @COUNT &lt;= 30
      BEGIN
          BULK INSERT acks FROM 'C:\Users\JASON SAMUELS\Documents\M-DD\DP('+@COUNT+').csv' 
        WITH(FIRSTROW = 2, 
             ROWTERMINATOR = '\n',  
             FIELDTERMINATOR = ',')
  
         SET @COUNT = @COUNT + 1
      END
  </code></pre>
  
  <p>The error seems to be at the first + just before the variable @COUNT in the path string. This is the error message i get:</p>
  
  <pre><code>Msg 102, Level 15, State 1, Line 8
  Incorrect syntax near '+'.
  Msg 319, Level 15, State 1, Line 8
  Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
  </code></pre>
  
  <p>How should i insert the variable in the string? </p>