Apologies if this has been answered elsewhere but I couldn't find anything here or online.
Does openrowset have a file size limit? I'm trying to write a stored procedure, part of which checks if certain characters exist within the file and if they do I'd have to skip to the next section of the stored procedure. So far I've simply bulk inserted the entire file into a one column table then did:
IF(SELECT COUNT(*) FROM #fulltable WHERE fulltable LIKE '%}%')>0 GOTO NEXTSECTION
IF(SELECT COUNT(*) FROM #fulltable WHERE fulltable LIKE '%~%')>0 GOTO NEXTSECTION
IF(SELECT COUNT(*) FROM #fulltable WHERE fulltable LIKE '%#%')>0 GOTO NEXTSECTION
It's reliable but very slow when dealing with large files (sometimes over 10GB). I'm thinking the below query would be quicker for large files
DECLARE @FILE NVARCHAR(MAX)
select @FILE = BULKCOLUMN from (
select * from openrowset(BULK N'filpath', single_clob) [a]
)a
IF(SELECT IIF(@FILE LIKE '%{%',1,0)) = 1 GOTO NEXTSECTION
IF(SELECT IIF(@FILE LIKE '%}%',1,0)) = 1 GOTO NEXTSECTION
IF(SELECT IIF(@FILE LIKE '%~%',1,0)) = 1 GOTO NEXTSECTION
IF(SELECT IIF(@FILE LIKE '%@%',1,0)) = 1 GOTO NEXTSECTION
IF(SELECT IIF(@FILE LIKE '%£%',1,0)) = 1 GOTO NEXTSECTION
But as I say Bulk insert is reliable and I'd hate to risk the file being truncated when using OPENROWSET if there is some sort of file size limit.
Any advice on the matter would be appreciated.
Asked by Anonymous
(155 rep)
Oct 15, 2019, 12:01 PM
Last activity: Feb 25, 2020, 09:11 PM
Last activity: Feb 25, 2020, 09:11 PM