How to clone and modify rows in SQL table?
<p>I use MSSQL 2012.</p>

<p>I have this table called SensorMeasure:</p>

<pre><code>| Id | SensorUnitId| MeasureDate            | AlertType | MeasureValue|   
| 1  |   3379      | 2016-06-05 00:01:34.000|         1 |     4       | 
| 3  |   3381      | 2016-09-18 11:26:33.000|         6 |     5       |
| 4  |   3381      | 2017-03-03 21:46:18.000|         3 |     3       |
| 5  |   3382      | 2016-09-18 11:26:33.000|         5 |     8       |
| 6  |   3381      | 2017-05-03 21:46:18.000|         3 |     0       |
</code></pre>

<p>I need to gruop the rows by SensorUnitId column then after groupping by SensorUnitId I need to clone rows which has latest date, set <code>AlertType</code> cell to 6 and set current date and time to <code>MeasureDate</code> cell and insert them into the table.</p>

<p>Here the result I expect:</p>

<pre><code>| Id | SensorUnitId| MeasureDate            | AlertType | MeasureValue|   
| 1  |   3379      | 2016-06-05 00:01:34.000|         1 |     4       | 
| 3  |   3381      | 2016-09-18 11:26:33.000|         6 |     5       |
| 4  |   3381      | 2017-03-03 21:46:18.000|         4 |     3       |
| 5  |   3382      | 2016-09-18 11:26:33.000|         5 |     8       |
| 6  |   3381      | 2016-11-03 21:46:18.000|         3 |     0       |
| 7  |   3379      | 2016-12-12 15:30:34.000|         6 |     4       | 
| 8  |   3381      | 2016-12-12 15:30:34.000|         6 |     0       |
| 9  |   3382      | 2016-12-12 15:30:34.000|         6 |     8       |
</code></pre>

<p>Here how I group the rows:</p>

<pre><code>SELECT  Max(MeasureDate) as Date, MAX(AlertType) as AlertType,SensorUnitId
FROM SensorsMeasure
GROUP BY SensorUnitId
</code></pre>

<p>But I don't know how after grouping I modify MeasureDate to current date and time and how to set AlertType to 6 and insert those rows to the table.         </p>

<p><strong>UPDATE</strong></p>

<p>As I already wrote I have table named SensorMeasure.I need to group all records in the table by column <code>SensorUnitId</code>.</p>

<p>After records were grouped I need to fetch from each gruop record with the latest date.If the fetched record has <code>AlertType</code> value 6 I need to filter it(ignore it) if record has <code>AlertType</code> value not 6 I need to clone this row 
set <code>AlertType</code> cell to 6 and set current date and time to <code>MeasureDate</code> cell and insert them into the table <code>SensorMeasure</code>.</p>

<p>For example I have this table:</p>

<pre><code>| Id | SensorUnitId|      MeasureDate       | AlertType | MeasureValue|   
| 1  |   3379      | 2016-06-05 00:01:34.000|         1 |     4       | 
| 3  |   3381      | 2016-09-18 11:26:33.000|         6 |     5       |
| 4  |   3381      | 2016-12-03 21:46:18.000|         3 |     3       |
| 5  |   3382      | 2016-09-18 11:26:33.000|         6 |     8       |
</code></pre>

<p>The desired table is:</p>

<pre><code>| Id | SensorUnitId|      MeasureDate       | AlertType | MeasureValue|   
| 1  |   3379      | 2016-06-05 00:01:34.000|         1 |     4       | 
| 3  |   3381      | 2016-09-01 11:26:33.000|         6 |     5       |
| 4  |   3381      | 2016-12-03 21:46:18.000|         3 |     3       |
| 5  |   3382      | 2016-09-18 11:26:33.000|         6 |     8       |
| 6  |   3379      | 2016-12-12 21:20:34.000|         6 |     4       | 
| 7  |   3381      | 2016-12-12 21:20:34.000|         6 |     3       |
</code></pre>