0

I have a table that has the following schema:

Create table TempMedia

id int Primary key,
mediaid  int,
FileData varbinary(max)

This table has parts of the full file (we use a silverlight control to chunk the data to the web server)

We store the full file in another table:

create table Media
id int primary key,
FileData varbinary(max)

Is there are way to perform an insert into the Media table that concatenates the file parts completely in SQL, so we end up with the full file in the Media table? At the moment we combine these file parts by selecting the data from the TempMedia table and combining it on the web server and then inserting back to the Media table. Ideally we like to do this only on the SQL Server.

Yuck
  • 49,664
  • 13
  • 105
  • 135
Dave F
  • 31
  • 1
  • 3
  • 1
    datatype "blob" doesn't exits in SQL Server... please give correct DB engine or correct code – gbn Jun 10 '11 at 09:12
  • Is your binary large object (blob) stored in an "image" data type or does it use "varbinary(max)"? – Anthony Faull Jun 10 '11 at 09:58
  • this should help with part of your question: http://stackoverflow.com/questions/3108985/concatenating-2-rtf-fields-stored-in-blobs-with-a-sql-script – JeffO Jun 10 '11 at 10:49

1 Answers1

1

This should get the concatenated binary which you can then insert into your table.

DECLARE @bin varbinary(max)
SELECT @bin=CASE WHEN @bin is null THEN FileData ELSE @bin + FileData END
FROM TempMedia
WHERE MediaId = @mediaId
ORDER BY id -- make sure this is sequential
Tim Rogers
  • 21,297
  • 6
  • 52
  • 68
  • Might be worth reading Mircosoft's response to [this connect issue](http://connect.microsoft.com/SQLServer/feedback/details/383641/in-variable-concatenation-and-order-by-filters-results-like-where-condition#details) : "Using assignment operations (concatenation in this example) in queries with ORDER BY clause has undefined behavior" – Damien_The_Unbeliever Jun 10 '11 at 12:20
  • Interesting - I've used this several times and not noticed any problems. So I guess if you want it to be bulletproof, then it has to be a UDF and a cursor. – Tim Rogers Jun 10 '11 at 13:53