• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 682
  • Last Modified:

Bulk Insert update through path value

Dear Experts,
I am using below mentioned query to update text file into database. I need to declare @path in bulk insert statement instead of hard coding. Please help.
 
Bulk Insert Emp_DB.dbo.intermediary  From '\\10.0.0.150\att\txtfile.txt' With (FieldTerminator=',', FirstRow=1, rowterminator ='\n')


Need to do like this

Declare @Path Varchar(100)
Set @Path ='\\10.0.0.150\att\txtfile.txt'


Bulk Insert Emp_DB.dbo.intermediary  From @Path  With (FieldTerminator=',', FirstRow=1, rowterminator ='\n')

Error:
Msg 102, Level 15, State 1, Procedure UpdateTxtFile, Line 14
Incorrect syntax near '@Path'.
Msg 319, Level 15, State 1, Procedure UpdateTxtFile, Line 14
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.
0
Mehram
Asked:
Mehram
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
You cannot pass @Path as a variable to BULK INSERT statement. Instead you need to use Dynamic SQL like this..

Declare @Path Varchar(100)
declare @sql nvarchar(1000)
Set @Path ='\\10.0.0.150\att\txtfile.txt'
SET @sql = 'Bulk Insert Emp_DB.dbo.intermediary  From ''' + @path + '''  With (FieldTerminator='','', FirstRow=1, rowterminator =''\n'')'

exec sp_executesql @sql
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now