Solved

Bulk Insert update through path value

Posted on 2013-01-28
1
673 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Pivot Query Problem 9 49
SQL Query with Sum and Detail rows 2 62
Need help how to find where my error is in UFD 6 46
SQL- GROUP BY 4 22
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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

763 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