stored procedure for importing txt in sql server db

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.

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

Sorry if I am late here, but I would suggest a different approach - open the file in your C# application and convert it to something more SQL-friendly, a DataTable or even XML. In C# you have complete control over how you parse the files. Then write the stored procedure to accept your DataTable or XML. A DataTable is preferable, but cannot be used with Entity Framework.

There is lots of help around of how to do inserts by joining to this sort of input, and SQL Server is optimised for set operations.