April 11, 2014 at 9:04 am
Hi,
I am trying the bulk insert concept using the below link.
The additional step what i am doing is i need to pass the filepath,tablename, delimiter to the storedproc. Here is my try
CREATE PROCEDURE SampleBulkInsert(
@FilePath NVARCHAR(4000)
,@TableName NVARCHAR(20)
,@delimiter nvarchar(1)
)
AS
BEGIN
declare @sql nvarchar(4000);
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
set @sql = 'BULK INSERT ' + @TableName +
' FROM ' + @FilePath + ' WITH (
FIELDTERMINATOR = ''' + @delimiter +'''
,ROWTERMINATOR = ''''
)'
Execute @sql;
END
GO
The procedure run successfully without compile error.
If i execute the procedure using
exec SampleBulkInsert 'D:\CSVTest.csv','CSVTest',','
getting errors as follows
Msg 2812, Level 16, State 62, Procedure SampleBulkInsert, Line 26
Could not find stored procedure 'BULK INSERT CSVTest FROM D:\CSVTest.csv WITH (
FIELDTERMINATOR = ','
,ROWTERMINATOR = ''
)'.
If am not wrong that quotation missing on the file path variable on the procedure. Tried to give the quotation in different ways. but unable to resolve the error. Any suggestion or corrections please
April 11, 2014 at 9:09 am
I think you just need to put the @sql in brackets:
EXECUTE (@SQL)
I would strongly recommend you read about SQL injection before putting this live.
John
April 11, 2014 at 9:09 am
I believe you just need to Wrap your @SQL in brackets:Execute (@sql); Otherwise, it thinks you are execution a stored-procedure
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 11, 2014 at 9:16 am
Hey guys,
thanks for your replies.
my try :
set @sql = 'BULK INSERT ' + @TableName +
' FROM ' + @FilePath + ' WITH (
FIELDTERMINATOR = ''' + @delimiter +'''
,ROWTERMINATOR = ''''
);'
Execute (@sql);
Here is the next error :
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'D:'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
any suggestions please
April 11, 2014 at 9:19 am
Put PRINT @SQL in your code and you'll see what the problem is. The path needs to be in quotes.
Don't forget the SQL injection thing.
John
April 11, 2014 at 9:23 am
Hi Jhon,
Yes, i knew i am missing the quotation for the path. that's what i mentioned on my first post. Here is my another try
set @sql = 'BULK INSERT ' + @TableName +
' FROM ' ''+ @FilePath +'' ' WITH (
FIELDTERMINATOR = ''' + @delimiter +'''
,ROWTERMINATOR = ''''
);'
Though, i am getting compile error. Having trouble in adding the quotation for the path. Any help please
April 11, 2014 at 9:23 am
Try this
set @sql = 'BULK INSERT ' + @TableName +
' FROM ' + '''' + @FilePath + '''' + ' WITH (
FIELDTERMINATOR = ''' + @delimiter +'''
,ROWTERMINATOR = ''''
);'
Execute (@sql);
April 11, 2014 at 9:31 am
Hi Josh.
Thank you Man. understood how to include quotes. Awesome.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply