Bulk Insert FROM Issue 1

[mjjks]

Hi All,

I'm using SQL 2005 Express and pass path\filename to stored procedure to import file into table, but get an error in BULK INSERT statement as it doesn't like having a variable as a FROM parameter.

Create Procedure dbo.spImportAreas(@ProjectPlanKey int, @FilePath varchar(300))

as

[COLOR=green]-- create temp table to store imported data[/color]
create table #temp(ID int IDENTITY not null,
				AreaName varchar(50),MaterialName varchar(50),
				ActualMatlQty decimal(15,3),MatlUOM varchar(10),
				AreaMeasQty decimal(15,3),MatlDescription varchar(100),
				AreaType varchar(30))

[COLOR=green]-- dump content of import file into temp table[/color]
BULK INSERT #temp
   FROM [highlight]@FilePath[/highlight]
   WITH 
      (
         FIELDTERMINATOR =',',
         ROWTERMINATOR ='\n'
      )

I get this error:
[COLOR=#ff0000]
Msg 102, Level 15, State 1, Procedure spImportAreas, Line 15
Incorrect syntax near '@FilePath'.
Msg 319, Level 15, State 1, Procedure spImportAreas, Line 16
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.
[/color]

I tried adding single quotes around @FilePath like this:
''+ @FilePath + '' , but no luck.

Anyone has and idea?

Thanks much

 

[vbkris]

use dynamic sql:

exec('
BULK INSERT #temp
FROM '+@FilePath+'
WITH
(
FIELDTERMINATOR ='','',
ROWTERMINATOR =''\n''
)
')

Known is handfull, Unknown is worldfull

 

[mjjks]

Thanks for the tip, vbkris.
However, I still get pretty much the same error after using your sample.

There's nothing weird in the file path.
exec spImportAreas 2,'C:\Inetpub\wwwroot\PS\Import\Floorright.txt'


Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'C'.
Msg 319, Level 15, State 1, Line 4
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.

 

[SQLDenis]

exec('
BULK INSERT #temp
FROM ''' +@FilePath+'''
WITH
(
FIELDTERMINATOR ='','',
ROWTERMINATOR =''\n''
)
')

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mjjks]

Thanks Denis, that worked great.