title: SQL - Cannot bulk load
body: <p>I am trying to perform a bulk upload of multiple CSV files to SSMS.
  I have the table created with the appropriate column names but when I execute my code the access to the folder containing the CSV files is denied.</p>
  <p>I have read online that you need to store the files in the same location that the server is on but this is not an option for me.</p>
  <p>I have also tried to edit the permissions of the folder that the CSV files are in but the access is still denied.</p>
  <p>Does anyone have a solution to this, I've spent hours trying to find one.</p>
  <p><strong>My Code</strong></p>
  <p><strong>1. Create a table to store CSV info.</strong></p>
  <pre><code>CREATE TABLE [dbo].[data_cube](
      [Name] [nvarchar](1000) NULL,
      [Country][nvarchar](1000) NULL,
      [Occupation][nvarchar](1000) NULL,
      [Hobby][nvarchar](1000) NULL
  ) on [Primary]
  </code></pre>
  <p><strong>2. Create table to store information on the imported files</strong></p>
  <pre><code>CREATE TABLE [dbo].[files_logtable](
      [id] [int] IDENTITY(1,1) NOT NULL,
      [filename] [varchar](200) NULL,
      [date_created] [datetime] DEFAULT GETDATE()
  )ON [Primary]
  </code></pre>
  <p><strong>3. Create a stored procedure</strong></p>
  <pre><code>CREATE PROCEDURE import_files @filepath varchar(500), @pattern varchar(100), @tablename varchar(100)
  AS
  BEGIN
  
  DECLARE @query varchar(1000), @moveProcessed varchar(1000)
  
  CREATE TABLE #x(name varchar(200))
  
  set @query = 'master.dbo.xp_cmdshell &quot;dir '+@filepath+@pattern+' /b&quot;'
  
  insert #x exec (@query)
  
  delete from #x where name IS NULL
  
  select identity(int,1,1) as ID,name into #y from #x
  
  drop table #x
  
  Declare @max1 int, @count1 int, @filename varchar(200)
  
  set @max1 = (select max(ID) from #y)
  
  set @count1 = 0
  
  while @count1 &lt; @max1
  BEGIN
  SET @count1 = @count1+1
  
  SET @filename = (select name from #y where [id]=@count1)
  
  set @query = 'BULK INSERT '+@tablename+' FROM &quot;'+@filepath+@filename+'&quot; WITH (FIELDTERMINATOR = '','',FIRSTROW=1,ROWTERMINATOR=''\n'')'
  
  EXEC (@query)
  
  insert into file_logtable(filename) select @filename
  
  set @moveProcessed = 'MOVE &quot;'+@filepath+@filepath+'&quot; C:\Users\cob2202\Desktop\Files\Data Cube\FlatFileDestination\&quot;'+SUBSTRING(@filename,1, LEN(@filename) -4)+'_'+cast(FORMAT(getdate(),'yyyMMDDHHmmss') as varchar)+'.csv&quot;'
  
  
  EXEC master..XP_cmdshell @moveProcessed
  END
  
  END
  </code></pre>
  <p><strong>4. Read in files</strong></p>
  <pre><code>Exec import_files 'C:\Users\cob2202\Desktop\Files\Data Cube\FlatFileSource\', '*.csv', 'data_cube'
  </code></pre>