Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Bulk Insert update through path value

Posted on 2013-01-28
1
Medium Priority
?
680 Views
Last Modified: 2013-01-28
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
Comment
Question by:Mehram
1 Comment
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 38826331
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question