Wednesday, November 13, 2013

Sql Server - Query to Insert/Update Binary info from system drive to local database

Sometime need occur to insert a file /Image to place in database' table from the local system location

Mostly programmer started coding for upload file to db

but not require at all just use sql query below

>>File as Binary
------------------------------------------------------------------------------------------
SELECT  * FROM OPENROWSET(BULK N'C:\NZyuqYD.gif', SINGLE_BLOB)  as BinaryData


------------------------------------------------------------------------------------------


>> Insert  binary file from System location to local Sql Server Db

------------------------------------------------------------------------------------------
INSERT INTO Files
(Id, Data)
SELECT 1, * FROM OPENROWSET(BULK N'C:\pic.jpg', SINGLE_BLOB) BinaryDataTOInsert
------------------------------------------------------------------------------------------

>> Update a binary file from System location to local Sql Server Db
------------------------------------------------------------------------------------------
Update Files
Set Data=( SELECT * FROM OPENROWSET(BULK N'C:\pic.jpg', SINGLE_BLOB) BinaryDataToUpdate)
where ID=1

------------------------------------------------------------------------------------------


www.sunitkanyan.in