1

I have a table which has a Filestream column which I would like to move to another table.

Is this possible without having to do a "manual" move of the data (create target column; issue UPDATE/INSERT to target column from source column; DELETE source column)?

I'm hoping there might be a way to simply "detach" the column and move it to a different table.

I found a similar/related question about altering Filestream tables which makes me think that it isn't possible, but hoping that someone might be able to help!

Improve this question
1

You can't move the column without copying the data. There's one rather exotic idea to pull this of: You move all other columns and leave the filestream column in place.

Depending on the number of rows and schema changes required this can be a lot faster (or slower). You might have to completely gut some tables and create complicated scripts. I only would do this approach if you really need to avoid this downtime.

Improve this answer
1
  • I like your backwards thinking :) I am going to look into this. – oatsoda Jun 19 '15 at 11:37
0

No I don't know any way to do this. Just make an INSERT or UPDATE on the other table. You can detach whole partitions of a table and move them to another table. But not just one column.

Why don't you just want to copy/move the data?

Improve this answer
4
  • Because the column contains many GB of data and I don't want to have an upgrade path that takes hours! – oatsoda Jun 19 '15 at 8:54
  • 1
    Well many GB, how many? If you have a good IO subsystem it should be a operation of a few seconds maybe some minutes. I needed to move a table between two servers for a while which holds 200GB and it took 8 Minutes. Well many GB is relative. :-) And as I said, I haven't seen a way to perform such a operation. – Ionic Jun 19 '15 at 8:59
  • Ranging from 50GB up to 400GB...But some of this is on customer infrastructure... I suppose you are right - I need to run a test and see! – oatsoda Jun 19 '15 at 11:36
  • Ah ok. The customer side may vary. You can run a test by just moving 1000/10000 rows and try to evaluate how long it takes. By the way, if the customer side is set to RECOVERY MODE SIMPLE, I would suggest to run it in a loop and copy just 10.000 rows each time. If something fails, you won't rollback everything, just the last 10.000 rows. Another side-effect is that the transaction log won't grow to a huge monster in this case. – Ionic Jun 19 '15 at 11:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.