Error in reading CSV file

I am using the below code in SP, SQL Server 2005

declare @path varchar(500)
set @path = 'E:\Support\test.csv';
print @path
 Create table #mytable( 
name varchar(max), class varchar(max), roll varchar(max)
)

BULK INSERT #mytable FROM @path 
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
);
Go
select * from #mytable

drop table #mytable

But it is throwing the following error :

Msg 102, Level 15,开发者_Go百科 State 1, Line 8
Incorrect syntax near '@path'.
Msg 319, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'with'. 
If this statement is a common table expression or an xmlnamespaces clause, 
the previous statement must be terminated with a semicolon.
Msg 208, Level 16, State 0, Line 1
Invalid object name '#mytable'.

Could anybody help me.

You can't do the following

BULK INSERT #mytable FROM @path 

if you are expecting this to translate to

BULK INSERT #mytable FROM 'E:\Support\test.csv'

It's not the file name that's in the varchar, SQL sees it @Path as the data and not a string containing the path.

If you need to use a variable for the path, you will need to use some dynamic SQL which roughly translates to (excuse syntax errors)

DECLARE @SQL varchar(max)
SET @SQL = 'BULK INSERT #mytable FROM '+ @path + ' 
--Add the rest of your code here

EXEC (@SQL)

If your variable is never going to change though I'd just go ahead and stick the string into the statement itself.

Hope that helps.