Insert Data retrieved from another row except one updated column

Question

I would like to take all the data used in a previous row and insert it into a new row. However, in one column I will be using a new value. I am using a SqlDataSource in a FormView to show this information once completed. Is there a way to handle this?

For example,

Old table

DataID    StartDate    EndDate
112       2014-05-01   2014-06-02

New table

DataID    StartDate    EndDate
112       2014-05-01   2014-06-02
113       2014-05-01   2014-06-02

I am using this query to increment the DataID:

SELECT DISTINCT 
    (SELECT MAX (DataID) + 1 
     FROM Core.DataCollectionPeriod) AS DataID 
FROM Core.DataCollectionPeriod

Answer 1

You could use the following (SQL Fiddle):

INSERT INTO MyTable
(StartDate, EndDate)
SELECT TOP 1 StartDate, EndDate
FROM MyTable
ORDER BY Dataid Desc;