SQL Procedure with insert

CREATE PROCEDURE [LoadFile] 
  @SourceFilePath varchar(512),
  @LogFilePath    varchar(512)
AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;

  -- Set up a single column temporary table to hold the data whilst we play with it.
  -- Vary the column size to suit your data.
  SELECT CAST(NULL AS VARCHAR(255)) AS [LineText] INTO #IMPORTBUFFER WHERE 1 = 0

  -- The dynamic SQL lets us use variable names for our files.
  -- The ERRORFILE parameter is especially handy if you're importing data
  -- with column separators, such as csv, and you can't guarantee the 
  -- inbound data is well formatted.
  exec('
  BULK INSERT #ImportBuffer
  FROM ''' + @SourceFilePath + '''
   WITH (
    ROWTERMINATOR = ''\n'',
    ERRORFILE =  ''' + @LogFilePath + '''
  );
  ')

  -- Now take a subset of the data and copy it to another table.
  -- Start position and field lengths are for just for the example,  
  -- you may have to change them for your data.
  INSERT INTO MyIndexTable([ID], [NAME], [MESSAGE])
  SELECT 
    SUBSTRING([LineText],  1,1),
    SUBSTRING([LineText],  2,8),
    SUBSTRING([LineText], 11,9)
  FROM [#ImportBuffer]
  WHERE {any where clauses that might be necessary}

  -- Repeat for as many other modifications/extractions as are necessary.
  -- Finally copy the content of the buffer to its final resting place.

  Insert into [FinalTable] ([ImportedText])
  SELECT 
    [LineText]
  FROM [#ImportBuffer]
  WHERE {any where clauses that might be necessary}

END
GO

declare @filePath     varchar(512)
declare @errorLogPath varchar(512)
set @filePath =  'C:\@scratch\somedata.txt'
set @errorLogPath  = 'C:\scratch\error.log' 

-- Empty the target table(s)
truncate table MyIndexTable
select * from MyIndexTable

-- Fill the target table(s)
exec LoadFile @filePath, @errorLogPath

-- Prove that the data is loaded.
select * from MyOtherTable