Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I insert binary file data into a binary SQL field using a simple insert statement?

I have a SQL Server 2000 with a table containing an image column.

How do I insert the binary data of a file into that column by specifying the path of the file?

CREATE TABLE Files (   FileId int,   FileData image ) 
like image 298
scottm Avatar asked Jul 13 '09 16:07

scottm


People also ask

How do I add binary data to a table in SQL?

In order to add binary data to a table, you must first use the parameter marker (?) in the query as a placeholder for the binary value. The execution of the query should include a record that contains the binary data in one of its fields.

How do I insert a file into a SQL database?

In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance. Expand Databases, right-click the database from which to add the files, and then click Properties. In the Database Properties dialog box, select the Files page. To add a data or transaction log file, click Add.

Which data type is used to store binary data in SQL?

Binary data can be stored in a table using the data type bytea or by using the Large Object feature which stores the binary data in a separate table in a special format and refers to that table by storing a value of type oid in your table.


1 Answers

I believe this would be somewhere close.

INSERT INTO Files (FileId, FileData) SELECT 1, * FROM OPENROWSET(BULK N'C:\Image.jpg', SINGLE_BLOB) rs 

Something to note, the above runs in SQL Server 2005 and SQL Server 2008 with the data type as varbinary(max). It was not tested with image as data type.

like image 185
Blithe Avatar answered Nov 06 '22 02:11

Blithe