Parameterizing BULK INSERT

I have a statement in a stored procedure as follows - which works a dream.
BULK INSERT cashpost
FROM 'c:\carparkfines\data\cashpost.txt'
WITH
(
FORMATFILE = 'c:\carparkfines\data\cashpost_format.txt'
)
However I want to parameterize the two file paths.
I know how to get them as follows
select @.filename01 = (SELECT ImportCashFilePath FROM tblSystem WHERE RecNo =
1)
However TSQL objects if I write
BULK INSERT cashpost
FROM @.filename01
WITH
(
FORMATFILE = @.filename02
)
So who is going to tell the new boy how to do it? Thanks in anticipation.
Andy Willis
Use some dynamic SQL, e.g.
DECLARE @.Sql nvarchar(4000)
SET @.Sql = '
BULK INSERT cashpost
FROM ''' + @.filename01 + '''
WITH
(
FORMATFILE = ''' + @.filename02 + '''
)
'
PRINT @.Sql
EXEC(@.Sql)
Darren Green
http://www.sqldts.com
"Andy Willis" <andrewrwillis@.blueyonder.co.uk> wrote in message
news:%23gqfKAlaEHA.2908@.TK2MSFTNGP10.phx.gbl...
> I have a statement in a stored procedure as follows - which works a dream.
> BULK INSERT cashpost
> FROM 'c:\carparkfines\data\cashpost.txt'
> WITH
> (
> FORMATFILE = 'c:\carparkfines\data\cashpost_format.txt'
> )
> However I want to parameterize the two file paths.
> I know how to get them as follows
> select @.filename01 = (SELECT ImportCashFilePath FROM tblSystem WHERE RecNo
=
> 1)
> However TSQL objects if I write
> BULK INSERT cashpost
> FROM @.filename01
> WITH
> (
> FORMATFILE = @.filename02
> )
> So who is going to tell the new boy how to do it? Thanks in anticipation.
> Andy Willis
>