Thursday, May 14, 2009

How to upload/modify more than one BLOB column in single statement

Recently, I have been visited our client who has one table with two columns defined as VARBINARY(MAX)to store the images. How can we insert/upade these columns with single statements? I have not used blobs too much ,so I ended up with the following

CREATE TABLE Blobs (id INT NOT NULL IDENTITY(1,1) PRIMARY KEY ,b1 VARBINARY(MAX),b2 VARBINARY(MAX))

---Single INSERT statement

INSERT INTO Blobs (b1,b2)
(SELECT *,(SELECT * FROM
Openrowset(Bulk 'N:\OnePicture.JPG', SINGLE_BLOB) AS d1)
FROM
Openrowset(Bulk 'N:\AnotherPicture.JPG', SINGLE_BLOB) AS d2)

--Update

UPDATE Blobs SET b1=(SELECT * FROM
Openrowset(Bulk 'N:\AnotherPicture.JPG', SINGLE_BLOB) AS d2),
b2=(SELECT * FROM
Openrowset(Bulk 'N:\OnePicture.JPG', SINGLE_BLOB) AS d2)
---WHERE condition put here

Wednesday, May 6, 2009

My wife gave birth to our third child on 4 May!

A son, 3 kg 370 grams!

We have replaced ourselves and are now done.