question

signforarun avatar image
signforarun asked

Its intersting try tis Problem in bulk insert using a stored procedure need to pass sinqle quote

Have writen a procedure to bulk insert using a stored procedure BULK INSERT test FROM 'D:\bcp\ check.txt' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) GO Proc IS CREATE PROCEDURE BULKINSERTTXT ( @TABLENAME VARCHAR(50), @PATHNAME VARCHAR(50) ) AS BEGIN DECLARE @SSQL NVARCHAR(50); SET @SSQL= SELECT ' BULK INSERT ' + '@TABLENAME' + ' FROM '' ' + '@PATHNAME' + ' '' WITH ( FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'' ) GO' exec sp_ExeCuteSQL @SSQL END Problem with this is the query becomes like the below BULK INSERT @TABLENAME FROM ' @PATHNAME ' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) GO Now @PATHNAME is not a parameter Also tried without sinqle quote in dynamic query as BULK INSERT @TABLENAME FROM @PATHNAME WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) GO and exec the procedure as Exec BULKINSERTTXT ‘tablename’, ’'D:\bcp\ check.txt'’ this also so error Kindly provide solution for this
sql-server-2005t-sqlbulk-insert
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
What you need to do is change your `SET @SQL ` line to take into account the quotes. Something like this might work: SET @SSQL= ' BULK INSERT ' + @TABLENAME + ' FROM ''' + @PATHNAME + ''' WITH (FIELDTERMINATOR='','', ROWTERMINATOR=''\n'') GO' What happens here is that the @SQL variable will now look like BULK INSERT FROM '' WITH (FIELDTERMINATOR=',', ROWTERMINATOR='\n') GO (where `` and `` are whatever was in those variables)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The SQL statement for your query is BULK INSERT @TABLENAME FROM '@PATHNAME' WITH (FIELDTERMINATOR=',', ROWTERMINATOR='\n') GO again the @pathname comes inside sinqlequote so it doesnt consider it as a parameter.
0 Likes 0 ·
robbin avatar image
robbin answered
You need to change the statement as mentioned by ThomasRushton. I do not know how you were able to get this whole query with @SQL NVARCHAR(50). It should have been truncated after 50 characters. Following is the modified procedure CREATE PROCEDURE BULKINSERTTXT ( @TABLENAME VARCHAR(100), @PATHNAME VARCHAR(100) ) AS BEGIN DECLARE @SSQL NVARCHAR(500); SET @SSQL= ' BULK INSERT ' + QUOTENAME(@TABLENAME) + ' FROM ''' + @PATHNAME + ''' WITH ( FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'' ) ' exec sp_ExeCuteSQL @SSQL END
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Thomas, Think u are almost to the solution but now the problem is with the @pathname not for @tablename ,I hve applied QUOTENAME to @pathname as CREATE PROCEDURE BULKINSERTTXT ( @TABLENAME VARCHAR(100), @PATHNAME VARCHAR(100) ) AS BEGIN DECLARE @SSQL NVARCHAR(500); SET @SSQL= ' BULK INSERT ' + QUOTENAME(@TABLENAME) + ' FROM ''' + QUOTENAME(@PATHNAME) + ''' WITH ( FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'' ) ' exec sp_ExeCuteSQL @SSQL END but the error now is Msg 4861, Level 16, State 1, Line 1 Cannot bulk load because the file "[C:\Users\sqluser\Desktop/ newdocument.txt]" could not be opened. Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.) The open braces has been there for the filepath which i dnt have actually so its not getting opened can any one check on to this
0 Likes 0 ·
Why did you use QUOTENAME for the file path? You do not have to apply QUOTENAME function to your @PATHNAME variable. You should have at-least checked the QUOTENAME function in Books online before applying it.
0 Likes 0 ·
signforarun avatar image
signforarun answered
prepare the query and update it inside a temp table and remove the braces using substring function so that it could be used for any file path and and table name CREATE PROCEDURE BULKINSERTTXTCHK1 ( @TABLENAME VARCHAR(100), @PATHNAME VARCHAR(100) ) AS BEGIN CREATE TABLE #TEMP ( SNO INT IDENTITY(1,1), FNAME VARCHAR(300) ) DECLARE @SSQL NVARCHAR(500); DECLARE @SSQL1 NVARCHAR(500); SET @SSQL= 'BULK INSERT ' + QUOTENAME(@TABLENAME) + ' FROM ''' + QUOTENAME(@PATHNAME) +' ' SET @SSQL1=' WITH ( FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'' ) ' --select @SSQL --SELECT @SSQL1 DECLARE @I INT ; DECLARE @f INT; SET @f=LEN(@TABLENAME)+12 SET @I=LEN(@PATHNAME)+ LEN(@TABLENAME)+23 INSERT INTO #TEMP(FName) SELECT @SSQL INSERT INTO #TEMP(FNAME) SELECT @SSQL1 UPDATE #TEMP SET FName= ( SELECT SUBSTRING(FName,1,13)+@TABLENAME+ '] FROM '+''''+ @PATHNAME +'''' FROM #TEMP WHERE SNO='1' )WHERE SNO='1' SET @SSQL=(SELECT FName FROM #TEMP WHERE SNO='1' )+(SELECT FName FROM #TEMP WHERE SNO='2' ) -- SELECT @SSQL exec sp_ExeCuteSQL @SSQL DROP TABLE #TEMP END
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.