title: Update always encrypted column from decrypted column
body: <p>I would like to encrypt an existing database column with always encrypted. My project is a ASP.NET project using code first and database is SQL Server. The database has already data. I created a migration to achieve my goal.</p>
  
  <p>First I tried to alter the column type, using the following.</p>
  
  <pre><code>ALTER TABLE [dbo].[TestDecrypted] ALTER COLUMN [FloatCol] [float] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL
  </code></pre>
  
  <p>I got the following error.</p>
  
  <pre><code>Operand type clash: float is incompatible with float encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'TestEncrypt')
  </code></pre>
  
  <p>Then I decided to created another column and migrate the data.</p>
  
  <pre><code>ALTER TABLE [dbo].[TestDecrypted] ADD [FloatCol2] [float] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL
  UPDATE [dbo].[TestDecrypted] SET [FloatCol2] = [FloatCol]
  </code></pre>
  
  <p>And I got the same error.</p>
  
  <p>After I looked at <a href="https://blogs.msdn.microsoft.com/sqlsecurity/2016/12/13/parameterization-for-always-encrypted-using-ssms-to-insert-into-update-and-filter-by-encrypted-columns/" rel="nofollow noreferrer">this</a>, I noticed that it is possible to insert data like the following</p>
  
  <pre><code>DECLARE @floatCol FLOAT = 1.1
  UPDATE [dbo].[TestDecrypted] SET [FloatCol2] = @floatCol
  </code></pre>
  
  <p>But if I try to obtain the value from my existing column, it fails.</p>
  
  <pre><code>DECLARE @floatCol FLOAT = (SELECT TOP 1 FloatCol FROM TestDecrypted)
  UPDATE [dbo].[TestDecrypted] SET FloatCol2 = @floatCol
  </code></pre>
  
  <p>The error follows.</p>
  
  <pre><code>Encryption scheme mismatch for columns/variables '@floatCol'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '4' expects it to be (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'TestEncrypt'). 
  </code></pre>
  
  <p>Does anyone knows how can I achieve my goal?</p>
  
  <hr>
  
  <p><strong>Update 1</strong></p>
  
  <p>@Nikhil-Vithlani-Microsoft did some interesting suggestions.</p>
  
  <ul>
  <li><em>Always Encrypted Wizard in SSMS</em> - I would like to achieve my goal with code first migrations, so this idea does not fit.</li>
  <li><em>SqlBulkCopy</em> - It does not work inside migrations, because the new column will only exist after all 'Up' method is run. Therefore we cannot insert data into this column in this way inside this method.</li>
  </ul>
  
  <p>Anyway, his suggestions drove me to another attempt: obtain the decrypted values and update the encrypted column with them.</p>
  
  <pre><code>var values = new Dictionary&lt;Guid, double&gt;();
  
  var connectionString = ConfigurationManager.ConnectionStrings["MainDb"].ConnectionString;
  using (var sourceConnection = new SqlConnection(connectionString))
  {
      var myCommand = new SqlCommand("SELECT * FROM dbo.TestDecrypted", sourceConnection);
      sourceConnection.Open();
  
      using (var reader = myCommand.ExecuteReader())
      {
          while (reader.Read())
          {
              values.Add((Guid)reader["Id"], (double)reader["FloatCol"]);
          }
      }
  }
  
  Sql("ALTER TABLE [dbo].[TestDecrypted] ADD [FloatCol2] [float] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL");
  
  foreach (var valuePair in values)
  {
      // The error occurs here
      Sql($@"DECLARE @value FLOAT = {valuePair.Value}
  UPDATE [dbo].[TestDecrypted] SET [FloatCol2] = @value WHERE Id = '{valuePair.Key}'");
  }
  </code></pre>
  
  <p>In fact, I did not try to create another column and to migrate the data, as mentioned in an example above. I tried it only on SSMS.
  And now I got a different error.</p>
  
  <pre><code>Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
  </code></pre>
  
  <p>I tried to do it without encrypting the new column, and it worked properly.</p>
  
  <p>Any idea why this error occurs?</p>