Variables in SQL Statements

[Pippen]

What am I doing wrong in the following snippet of code?

-----------------------------------
declare @FullPath varchar(255)
set @FullPath = 'C:\MyDir\MyFile.txt'

bulk insert Northwind.dbo.Sometable
from @FullPath
with
(FIELDTERMINATOR = ',')
-----------------------------------

This works when I write the path out instead of using the @FullPath
variable. I have a cursor that will Bulk Insert all files names from a
table. The path will not change but the file name will. I guess I could use
a system call to rename the file in use to a hardcoded name and then rename
back to original name but I would like find a way to make the above concept
work if it is possible.

TIA,

-p

[Dan Guzman]

According the the Books Online syntax, BULK INSERT doesn't take a variable
as a file name. A workaround is to build a SQL statement string and execute
it dynamically using EXEC or sp_executesql. For example:

DECLARE @SqlStatement nvarchar(4000)
SET @SqlStatement =
'
BULK INSERT Northwind.dbo.Sometable
FROM ''' + @FullPath + '''
WITH
(FIELDTERMINATOR = '','')
'

EXEC sp_executesql @SqlStatement

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Pippen" <12*@hotmail.com> wrote in message
news:HGM4d.246840$Fg5.43547@attbi_s53...

What am I doing wrong in the following snippet of code?

-----------------------------------
declare @FullPath varchar(255)
set @FullPath = 'C:\MyDir\MyFile.txt'

bulk insert Northwind.dbo.Sometable
from @FullPath
with
(FIELDTERMINATOR = ',')
-----------------------------------

This works when I write the path out instead of using the @FullPath
variable. I have a cursor that will Bulk Insert all files names from a
table. The path will not change but the file name will. I guess I could
use a system call to rename the file in use to a hardcoded name and then
rename back to original name but I would like find a way to make the above
concept work if it is possible.

TIA,

-p

[Pippen]

"Dan Guzman" <gu******@nospam-online.sbcglobal.net> wrote in message
news:Tc******************@newssvr30.news.prodigy.c om...

According the the Books Online syntax, BULK INSERT doesn't take a variable
as a file name. A workaround is to build a SQL statement string and
execute it dynamically using EXEC or sp_executesql. For example:

DECLARE @SqlStatement nvarchar(4000)
SET @SqlStatement =
'
BULK INSERT Northwind.dbo.Sometable
FROM ''' + @FullPath + '''
WITH
(FIELDTERMINATOR = '','')
'

EXEC sp_executesql @SqlStatement

--
Hope this helps.

Dan Guzman
SQL Server MVP

Thank you! Much more elegant that the hack I was considering.

-p