stored procedure for importing txt in sql server db

Question

I have to insert new records in a database every day from a text file ( tab delimited). I'm trying to make this into a stored procedure with a parameter for the file to read data from.

CREATE PROCEDURE dbo.UpdateTable

    @FilePath

    BULK INSERT TMP_UPTable 
    FROM @FilePath
    WITH 
    ( 
        FIRSTROW = 2, 
        MAXERRORS = 0, 
        FIELDTERMINATOR = '\t', 
        ROWTERMINATOR = '\n' 
    )

    RETURN

Then i would call this stored procedure from my code (C#) specifying the file to insert.

This is obviously not working, so how can i do it ?

Just to be clear the problem here is that i can't pass the parameter @FilePath to the FROM clause, or at least i don't know how.

Answer 1

Sorry, I misunderstood. You need to create the SQL statement dynamically and then execute it:

    CREATE procedure dbo.UpdateTable

    @FilePath varchar(max)
AS

 declare @sql varchar(max)
 declare @parameters varchar(100)
 set @parameters = 'FIRSTROW = 2, MAXERRORS = 0, FIELDTERMINATOR = ''\\t'', ROWTERMINATOR = ''\\n'' '
 SET @SQL = 'BULK INSERT TMP_UPTable FROM ' + @FilePath + @parameters

EXEC (@SQL)

RETURN