Inserting PDFs into Student Files using SQL

Question

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.

Thanks
D

Answer 1

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: https://www.sommarskog.se/blobload.txt.

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.

Answer 2

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.

Answer 3

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 ：https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-ver15
If i misunderstand you，please correct me.

Best Regards，
Isabella