upload a pdf file directly into a sql server existing table

Question

I need to upload an pdf to a column in SQL Server existing table.

SELECT TOP (1000) [Id]
,[Name]
,[EntryDate]
,[Attachment]
,[IsActive]
,[Remarks]
FROM [db].[dbo].[Mst]

i want upload pdf files directly in column Attachment.

How can i do?

Amit Mohanty · Answer

Try this UPDATE [db].[dbo].[Mst] SET [Attachment] = (SELECT * FROM OPENROWSET(BULK N'C:\path	o\your\file.pdf', SINGLE_BLOB) AS pdf) WHERE [Id] = your_id;

Vishal Yelve · Answer

Hi Garima Bansal, Yes as per Rajkiran sugestion, we cannot directly save file in table we can store file on some location (file system) and we have save that file location (file path) in db. Thats why i share you below link https://www.aspsnippets.com/Articles/Save-and-Retrieve-Files-from-SQL-Server-Database-using-ASPNet.aspx

Rajkiran Swain · Answer

It is not recommended to directly upload binary files such as PDFs to a database column. Instead, it is best practice to store the file on a file system or in cloud storage and store the file path or URL in the database column. This approach can help reduce the size of the database and improve performance. Here are the general steps to follow: Create a directory or container in your file system or cloud storage to store the PDF files. Save the PDF file to the directory or container using a unique file name, such as a GUID or timestamp. Store the file path or URL in the database column using a parameterized query or stored procedure to avoid SQL injection attacks. Here's some sample code in C# using ADO.NET to insert the PDF file path into the database table: // Set up the connection and command objects using (SqlConnection connection = new SqlConnection("your_connection_string")) { using (SqlCommand command = new SqlCommand("INSERT INTO [Mst] ([Name], [EntryDate], [Attachment], [IsActive], [Remarks]) VALUES (@Name, @EntryDate, @Attachment, @IsActive, @Remarks)", connection)) { // Set the parameter values command.Parameters.AddWithValue("@Name", "file_name.pdf"); command.Parameters.AddWithValue("@EntryDate", DateTime.Now); command.Parameters.AddWithValue("@Attachment", "/path/to/file/file_name.pdf"); command.Parameters.AddWithValue("@IsActive", true); command.Parameters.AddWithValue("@Remarks", "file description"); // Open the connection and execute the command connection.Open(); command.ExecuteNonQuery(); } } In this example, the @Attachment parameter contains the file path or URL to the PDF file. You will need to replace the connection string and parameter values with your own values.

Garima Bansal · Answer

Rajkiran Swain INSERT INTO [db].[dbo].[Mst] ([Name], [EntryDate], [Attachment], [IsActive], [Remarks]) VALUES ('Example', GETDATE(), (SELECT BulkColumn FROM OPENROWSET(BULK N'C:\Users\dell\Desktop\Aadharcard.pdf', SINGLE_BLOB) AS Document), 1, 'Example remarks') I tryed your query but getting this error Cannot bulk load. The file "C:\Users\dell\Desktop\Aadharcard.pdf" does not exist or you don't have file access rights

Rajkiran Swain · Answer

You can use SQL Server's `INSERT INTO` statement to add a PDF file to a table's column. Here's an example: INSERT INTO [db].[dbo].[Mst] ([Name], [EntryDate], [Attachment], [IsActive], [Remarks]) VALUES ('Example', GETDATE(), (SELECT BulkColumn FROM OPENROWSET(BULK N'C:\example.pdf', SINGLE_BLOB) AS Document), 1, 'Example remarks') This query assumes that your PDF file is located at `C:\example.pdf`. It uses the `OPENROWSET` function to read the binary data of the PDF file into a `varbinary(MAX)` column in the table. You can replace the values in the `VALUES` clause with the appropriate data for your use case.

Garima Bansal · Answer

is it possible to upload PDFs in to SQL Server not using code With any query

Vishal Yelve · Answer

Hi Garima Bansal, Do refer below link, It will help you. https://www.aspsnippets.com/Articles/Save-and-Retrieve-Files-from-SQL-Server-Database-using-ASPNet.aspx