Bulk Insert statement

Hi,

I was using a BULK INSERT statement in a stored procedure.Could any one help me out on one prob.I wanted to let the user select the file he wants to update and then i want to pass this address as a parameter in the stored procedure.

Suppose there is a parameter @.loc,so i want to use this parameter as

Bulk insert TableName from @.loc with(fieldterminator=',')
plz help me out thxI haven't test this but you may take help of Dynamic SQL, http://www.sommarskog.se/dynamic_sql.html fyi.|||Hi its been days now is there no one who could help me out?Is it because i am asking something that is impossible or something wrong plz let me know so that i would not waste my time n go ahead with something that would fulfill my task|||Did you even try dynamic sql as suggested to you?
declare @.fn varchar(255)
set @.fn='\\mypc\tmp\tmp1.txt'
exec ('bulk insert mytable from '''+@.fn+''' with(fieldterminator='','')')|||Hi its been days now is there no one who could help me out?Is it because i am asking something that is impossible or something wrong plz let me know so that i would not waste my time n go ahead with something that would fulfill my task
u haven't replied to Satya's solution.Then how we know that u got the answer or not?|||Even here u are giving the static location of the file from which u want the bulk insert the data from.What i wanted was to allow the user to define the path of the file and i wanted to pass this path as a parameter .In the example u have given its seems to me the same thing.May be if you could explain me more as i am a newbie.Sorry for the inconvienience.|||also i did try this code-:
@.loc nvarchar (50)
Bulk Insert Tablename From '''@.loc'' with(fieldterminator='','')
but i get an error while saving this stored procedure which says
"Cant find @.loc"|||post ur stored procedure|||Even here u are giving the static location
No, I am using a variable. Here are some code for you to play with
Note: UNC path used for the load as the server is not running on my local PC.
C:\tmp>echo aaa,bbb >tmp.txt

C:\tmp>more tmp.txt
aaa,bbb

C:\tmp>"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\isql" -U sa -S devdb
Password:
1> use tempdb
2> go
1> create proc pdreyer_load
2> @.filename varchar(255)
3> as
4> exec ('bulk insert #t1 from '''+@.filename+''' with(fieldterminator='','')')
5> go
1> create table #t1 (f1 varchar(10),f2 varchar(10))
2> exec pdreyer_load '\\pdreyer\tmp\tmp.txt'
3> select * from #t1
4> go
f1 f2
---- ----
aaa bbb

(1 row affected)
1> drop table #t1
2> drop procedure pdreyer_load
3> go
1> exit

C:\tmp>del tmp.txt|||Is the source file changed all the time?