Retrieve filename of uploading file in SQL

I've been searching for a solution on how to get the filename of using SQL Server. I know that it's possible if you're using C#. But how is it done in SQL?

For example, I have a file (example: uploadfile.txt) located in C:\ that is about to be uploaded. I have a table which has a field "filename". How do I get the filename of this file?

This is the script that I have as of the moment.

-- Insert to table
BULK INSERT Price_Template_Host
FROM 'C:\uploadfile.txt'
WITH
(开发者_JAVA技巧
    FIELDTERMINATOR = '\t',
    ROWTERMINATOR = '\n'
)

-- Insert into transaction log table filename and datetime()

To the best of my knowledge, there is no direct method in T-SQL to locate a file on the file system. After all this is not what the language is intended to be used for. From the script you have provided, BULK INSERT requires that the fully qualified file name already be known at the time of the statement call.

There are of course a whole variety of ways you could identify/locate a file, outside of using T-SQL for example using SSIS, perhaps you could use xp_cmdshell (has security caveats), or create a managed code module within SQL Server to perform this task.

To provide you with specific guidence, it may help if you could provide us all with details of the business process that you are trying to implement.

I would personally attach this problem with an SSIs package, which would give you much more flexibility in terms of load and subsequent logging. However, if you're set on doing this through T-SQL, consider exec'ing dynamically-constructed SQL:

declare @cmd nvarchar(max), @filename nvarchar(255)
set @filename = 'C:\uploadfile.txt'

set @cmd = 
'BULK INSERT Price_Template_Host
FROM '''+@filename+'''
WITH
(
    FIELDTERMINATOR = ''\t'',
    ROWTERMINATOR = ''\n''
)'

-- Debug only
print @cmd
-- Insert to table
exec(@cmd)

-- Insert into transaction log table filename and datetime()
insert into dbo.LoadLog (filename, TheTime)
values (@filename, getdate())

If I understand your question correctly, this paramaterizes the filename so that you can capture it further down in the script.