Inserting PDFs into Student Files using SQL

donna lewis 1 Reputation point

I have > 5,000 Privacy notices to insert into student files specifically into student records. This is waaaaay too many to do individually. We use MS SQL server and requisite SQL tools to import, export, and talk with our data.

Is there a process where I can write a script or something TSQL? that I can attach the documents that way via SQL?

And this is for SELECT students, not all students.


SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,359 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Erland Sommarskog 78,111 Reputation points MVP

    There is on convenient way to do this T-SQL. It can be done, but as I said, it is not convenient. Rather you would write a program/script that scans the folder and then writes to the database. I guess it also can be done with SSIS, but I don't know SSIS myself.

    I happen to have an old example in VB .NET on how to load a single file into the database on my web site:

    Oh, there is something in SQL Server known as a FileTable. With such a table, you can just drag the files into the database through Explorer. But whether that fits the current design of the database, I don't know.

    0 comments No comments

  2. Bert Zhou-msft 3,396 Reputation points

    Hi,@donna lewis

    Welcome to Microsoft T-SQL Q&A Forum!

    If I understand correctly , you want existing PDF into sql , it should be hosted on a secure file share and the path to the file should be stored in the database.
    I seem to have seen this situation in the official documentation , here is the link1 ,link2 about it , you can also use php to achieve it will be easier .
    Try this:

    create table pdfTest(pdfData varbinary(max))  
     Declare @sql varchar(max)  
     Set @sql='INSERT INTO pdfTest (pdfData) SELECT * FROM OPENROWSET(BULK'+ @filePath+', SINGLE_BLOB) AS BLOB'  
     exec @sql  

    Best regards,
    Bert Zhou

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  3. Isabellaz-1451 3,606 Reputation points

    Hi @donna lewis

    Maybe you can use bulk insert to load the data into table,then do a merge ?

    BULK INSERT Sales.Orders  
    FROM '\\SystemX\DiskZ\Sales\data\orders.csv'  
    WITH ( FORMAT='CSV');  

    About Bulk Insert :
    If i misunderstand you,please correct me.

    Best Regards,