Sunday, March 25, 2012

Bulk Insert with FileName Variable

I am trying to run a bulk insert in a SP using the filename as a variable:
BULK INSERT #tblTemp
From '+@.FilePath+'
WITH
(ROWTERMINATOR = '\n')
My procedure is not recognizing @.Filepath as a variable file name. Is there
a way to do this without resorting to full dynamic SQL?
Larry Menzin
American Techsystems Corp.> My procedure is not recognizing @.Filepath as a variable file name. Is
> there
> a way to do this without resorting to full dynamic SQL?
No, BULK INSERT command is not parameterized that way.|||Example below:BULK INSERT pubs..publishers2 FROM 'c:\newpubs.dat'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eeH4hL1oFHA.3936@.TK2MSFTNGP10.phx.gbl...
> No, BULK INSERT command is not parameterized that way.
>|||I think Larry knows how BULK INSERT works when you hard-code everything, the
problem is that @.FilePath is variable/unknown (in your example, it is
hard-coded as c:\newpubs.dat) .

> Example below:BULK INSERT pubs..publishers2 FROM 'c:\newpubs.dat'
> WITH (
> DATAFILETYPE = 'char',
> FIELDTERMINATOR = ',',
> ROWTERMINATOR = '\n'
> )

No comments:

Post a Comment