How to copy data into same table based on existing data in sql?

Question

It is possible to copy a set of rows based on Date column and insert in same table with different date?

For example : I have 5 rows with Date column value '201839' I need to copy those 5 rows in '201840' as well as '201841'

Or can we create a view that have a copy of 201839 rows as 201840 in sql.

Answer 1

One more solution is to run sub query for two time with different updated Date_column value. It will select data from same table and updated with different date_value.

INSERT into TABLE_NAME (Column1, Column2, Date_column)( SELECT (Column1, Column2, '201840') FROM TABLE_NAME where Date_column = '201839');

INSERT into TABLE_NAME (Column1, Column2, Date_column)( SELECT (Column1, Column2, '201841') FROM TABLE_NAME where Date_column = '201839');

Answer 2

A simple way to do it would be to first load it up into a temp table, make the changes, and then insert it back into the main table.

select * into #temptable from table where date='201839'
update #temptable set date='201840'
insert into table select * from #temptable
update #temptable set date='201841'
insert into table select * from #temptable

Answer 3

You didn't provide us with your table schema, so suppose it is something like this:

CREATE TABLE Test (
    Column1     INT,
    Column2     INT,
    Date_column VARCHAR(32)
)

Then you could use a query below for your purpose:

INSERT Test (Column1, Column2, Date_column)

SELECT t.Column1, t.Column2, v.Date_column
FROM Test t
    CROSS JOIN (
    VALUES 
        ('201840'),
        ('201841')
    )v(Date_column)
WHERE t.Date_column = '201839'