I need to insert all .pdf files from a folder, in a table, according to its number.
I could get a list of files ( I have all .pdfs inside a folder ):
exec master.sys.Xp_dirtree 'The_Folder_where_PDFS_are',0,1
And I could insert it inside a table:
CREATE TABLE PDFTest(NumExample integer,image_data VARBINARY(MAX)); DECLARE @NumExample integer SET @NumExample = 2456789 INSERT INTO PDFTest(NumExample ,image_data) SELECT @NumExample ,image_data FROM OPENROWSET(BULK N'C:\DBA\3937052016_Requisição.pdf',SINGLE_BLOB) AS ImageSource(image_data); Select * from PDFTest
But as you can see, files are named after a number (
3937052016 ). This number is the ID of another table, so, if the PDF file starts with
123, I need to insert it into the ID
123. I just would like a start point. I'm trying to create a cursor, looping throug all files, and using left() or substring () function to get the number, inserting it into a variable, and comparing it , like
insert this pdf into table where left/substring(pdf_name)=id.
I need something like a string like this :
Insert into tablex ( pdffield ) values ( 123_PdfFIle.pdf ( obviously using openrowset ) where left/substring(123_pdf to get the pdf's code )) = id`
I'm on the right patch? Is there a way to do this with less efforts?