title: Insert file into SQL Server 2008 R2 using Bulk Insert
body: <p>I'm trying to insert a file of 500 MB into table (column data type : <code>VARBINARY(MAX)</code>) using Bulk method, the SQL takes about 15 min to insert the file. I have 2GB RAM and my PC is core 2 duo and I'm using SQL Server 2008 R2.</p>
  
  <p>My questions:</p>
  
  <ul>
  <li>Is it normal to take 15 min ! , or it's too long? </li>
  <li>Are there any faster methods?</li>
  </ul>
  
  <p>Thanks.</p>
  
  <p>My procedure is SQL is </p>
  
  <pre><code>CREATE PROCEDURE [dbo].[UploadFile]
  (
      @FileName   nvarchar(100) , 
      @FileSize  nvarchar(50) ,
      @FileDescription  nvarchar(200),
      @FileExtontion  nvarchar(10), 
      @DateUploaded  nvarchar(50),
      @FilePath  nvarchar(3500),
      @UserId int 
  )
  AS
  BEGIN
      SET NOCOUNT ON;
  
      declare @FileInBinary varbinary(MAX)
  
      IF OBJECT_ID('#ORStable') IS NULL 
      BEGIN
          -- Temporary stored into a hash table for avoid data error     
          CREATE TABLE #ORStable (Length BIGINT, vDocument VARBINARY(MAX))
  
          DECLARE @SQL_QUERY NVARCHAR(4000)
          SET @SQL_QUERY= 'INSERT INTO #ORStable
                           SELECT len(bulkcolumn), *
                           FROM OPENROWSET(BULK '''+@FilePath+''', SINGLE_BLOB) AS BinaryData'
  
          exec SP_executesql @SQL_QUERY
      END
  
      SELECT TOP 1 @FileInBinary = vDocument FROM #ORStable
  
      insert into [File] ([FileName], FileSize, FileDescription, FileExtontion,
                          DateUploaded, FileInBinary, UserId)
      values (@FileName, @FileSize, @FileDescription, @FileExtontion,
              @DateUploaded, @FileInBinary, @UserId)
  
      DROP TABLE dbo.#ORStable
  END
  </code></pre>