I came across the need to do some t-sql work prior to our annual dealer conference next week. The short version is that we need to sanitize our customer data by replacing customer pictures with some stock photos of people around PBS. I certainly didn't want to have to manually change every customer record so I figured a gigantic t-sql script to update everyone would be a huge help.
Now I gotta admit - this is not the kind of component I normally work on. Database administrators tear through these kinds of apps but when you're a bit more of a generalist you need to be pretty good at googling the right kind of questions.
Anyway, here's the requirements...
1. Update every customer record with a new photo.
2. Choose the photo from a subset of 10 or 12 stock pictures.
3. Try to make sure that "consecutive" customer records don't have the same photo.
The first thing that takes figuring out is how to load an image from a file into a table. For some reason google-ing this didn't do too good. In the end it's pretty easy. The following command loads an image file into a blob-style object for use in t-sql...
SELECT * FROM OPENROWSET(BULK N'c:\creek.jpg', SINGLE_BLOB) as i
Note that you need the rowset descriptor "as i". Otherwise you'll receive some error about corelations.
Next I wanted to build a temporary table of customer id's and picture id's. Something like...
Customer1 Picture1
Customer2 Picture2
Customer3 Picture3
Customer4 Picture1
Customer5 Picture2
...
This would make a somewhat random picture list where at least adjacent customers wouldn't have the same picture. I accomplished this using the ROW_NUMBER() function against our customer code's.
INSERT INTO #Links (ContactId, PictureId)
(SELECT fldId, ROW_NUMBER() OVER(ORDER BY fldCode) % @Image_Count
FROM tblContacts)
Once we've got these two pieces together we can generate our test data pretty reliably. Here's the final sql script ...
DECLARE @Image_Count int SET @Image_Count = 2
CREATE TABLE #Links (
ContactId uniqueidentifier,
PictureId int
)
INSERT INTO #Links (ContactId, PictureId)
(SELECT fldId, ROW_NUMBER() OVER(ORDER BY fldCode) % @Image_Count
FROM tblContacts)
UPDATE tblContacts SET fldImage =
(SELECT * FROM OPENROWSET(BULK N'c:\creek.jpg', SINGLE_BLOB) as i)
WHERE fldId IN (SELECT ContactId FROM #Links WHERE PictureId = 0)
UPDATE tblContacts SET fldImage =
(SELECT * FROM OPENROWSET(BULK N'c:\dock.jpg', SINGLE_BLOB) as i)
WHERE fldId IN (SELECT ContactId FROM #Links WHERE PictureId = 1)
DROP TABLE #Links