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 )
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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With