1

I have a table for storing document content with a FILESTREAM column, and another column which is a ROWGUIDCOL column (required when you have a FILESTREAM column). I want to migrate this table with its existing data, into a slightly new structure as part of a code refactor so that 2 different projects can reuse code to manage their document records.

The problem is that I want to rename the ROWGUIDCOL column, so that both projects have the same column definitions for their document tables.

I can't use sp_rename because it doesn't work on ROWGUIDCOL columns.

I can't drop the existing ROWGUIDCOL column (and then add the replacement) because I get an error "A table that has FILESTREAM columns must have a nonnull unique column with the ROWGUIDCOL property."

I can't add the new ROWGUIDCOL column (and then drop the redundant original) because I get an error "Duplicate column specified as ROWGUIDCOL"

Is there any solution to this problem? Do I have to backup all the document content (the FILESTREAM column) and drop this first, then do the ROWGUIDCOL change, and then put the FILESTREAM content back in?

Simon Green
  • 1,131
  • 1
  • 10
  • 28

1 Answers1

0

Consider creating the new table with a different name for the migration. Then load with INSERT...SELECT (mapping columns as desired), drop the original table, and finally rename the new table to the original name.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71