Sql-server – Cursor to insert .pdf files into a table

cursorssql server 2014

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?

Best Answer

I created a directory called c:\TestPdf and created two txt files with some text in them (you would use your pdf files)

123456789.txt
987654321.txt

From your example, my assumption is you want file 123456789.txt inserted as ID 123 and you want file 987654321.txt inserted as ID 987.

This dynamic SQL worked for me - you would need to test against your data

set nocount on
IF OBJECT_ID('dbo.PDFTest', 'U') IS NOT NULL 
  DROP TABLE dbo.PDFTest; 
Create table PDFTest (NumExample integer,image_data VARBINARY(MAX))
DECLARE @FileTable TABLE (FileName VARCHAR(max),DepthColumn INT,FileColumn INT)

INSERT INTO @FileTable
EXEC master.sys.Xp_dirtree 'c:\testpdf',0,1

DECLARE @cmd VARCHAR(max)
DECLARE @FileName VARCHAR(max)

DECLARE _CURSOR CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT FileName
FROM @FileTable

OPEN _CURSOR

FETCH NEXT
FROM _CURSOR
INTO @FileName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmd = '
INSERT INTO PDFTest(NumExample ,image_data)
SELECT ' + substring(@FileName, 1, 3) + ',image_data
FROM OPENROWSET(BULK N''c:\testpdf\' + @FileName + ''',SINGLE_BLOB) AS ImageSource(image_data)
'

    PRINT @cmd

    EXEC (@cmd)

    FETCH NEXT
    FROM _CURSOR
    INTO @FileName
END --End While

CLOSE _CURSOR

DEALLOCATE _CURSOR

SELECT *
FROM pdftest

Related Solutions

Sql-server – How to FETCH from a cursor into a table variable

No, you can't fetch scalar values from a cursor row into a table variable. You would have to declare the variables, fetch into them, and then insert:

FETCH NEXT FROM idCursor INTO @id, @data;
INSERT @currentId([id],[data]) SELECT @id, @data;

However, perhaps it's the case that you don't need a cursor at all. Why are you processing one row at a time? Why not populate the @table variable with whatever query originally populated #tempRemaining?

INSERT @currentId([id], [data])
SELECT DISTINCT [id], [data]
    FROM #tempTable;

Or even just using #tempTable by itself and skipping the table variable altogether? Maybe even skipping the #temp table? A cursor combined with a table variable and a #temp table just sounds like a nightmare.

At the risk of sounding like Celko, this seems very much like flat file processing from the 1970s... and it's even worse that there are so many columns that declaring those variables would be prohibitive.

Sql-server – INSERT from SELECT using a CURSOR

Below is an example that uses a recursive CTE to create weekly values and a simple INSERT...SELECT to populate your table. I assumed a few identity IDs for testing's sake in addition to the postdate being the current date. You don't need a cursor to do this work. Enjoy!

    --CREATE SCENARIO TEMP TABLE
    IF OBJECT_ID('tempdb..#TempUserPaid','U') IS NOT NULL
        DROP TABLE #TempUserPaid

    CREATE TABLE #TempUserPaid
    (
          aid INT IDENTITY
        , uid INT
        , eid INT
        , name VARCHAR(50)
        , dob DATE
        , paid DECIMAL(19,4)
    )

    INSERT #TempUserPaid
    VALUES
         (100,10,'Mick','1980-03-29',40.00)
        ,(101,11,'Jack','1969-11-21',40.00)
        ,(205,11,'Lynn','1990-07-10',80.00)
        ,(217,10,'Dana','1987-02-15',40.00)


    --CREATE WEEKLYPAYMENTS TABLE IF IT DOESN'T EXIST TO MODEL USER TABLE
    --DROP TABLE WeeklyPayments  --> CLEAR OUT FOR TESTING PURPOSES
    IF OBJECT_ID('WeeklyPayments','U') IS NULL
        CREATE TABLE WeeklyPayments
        (
              id INT IDENTITY
            , uid INT
            , eid INT
            , [week] INT
            , postdate DATE
            , paid DECIMAL(19,4)
        );

    --INSERT YOUR CODE TO GENERATE NUMBER OF WEEKS HERE TO SET @weeksInMonth
    --HARDCODING FOR THIS EXAMPLE
    DECLARE @weeksInMonth SMALLINT;
    SET @weeksInMonth = 4;


    --RECURSIVE CTE TO GENERATE THE NUMBER OF WEEKS
    WITH cteWeeks AS
    (
        SELECT 1 WeekInMonth
        UNION ALL
        SELECT WeekInMonth+1 
        FROM cteWeeks
        WHERE WeekInMonth < @weeksInMonth
    )
    INSERT WeeklyPayments
    SELECT t.uid, t.eid, c.WeekInMonth, CONVERT(DATE,GETDATE()), CAST(t.paid/4 AS money)
    FROM #TempUserPaid t, cteWeeks c
    ORDER BY t.uid;

    SELECT * FROM WeeklyPayments;

Best Answer

Related Solutions

Sql-server – How to FETCH from a cursor into a table variable

Sql-server – INSERT from SELECT using a CURSOR

Related Question