Copy data to New Year
Hello Experts,
I have 2 tables (Items and Item Details). I am trying to copy the data of Current Year 2017 to a New Year 2018. In the table Item Details for 2018 everything should remain same as 2017 except the row Item Id and the Year. Please let me know if there is a simple way to do it in SQL. Please see the attachment for details. Thank you very much in advance.
CopyDataToNewYear.png
I have 2 tables (Items and Item Details). I am trying to copy the data of Current Year 2017 to a New Year 2018. In the table Item Details for 2018 everything should remain same as 2017 except the row Item Id and the Year. Please let me know if there is a simple way to do it in SQL. Please see the attachment for details. Thank you very much in advance.
CopyDataToNewYear.png
This runs in Oracle and should run in SQL Server as well but I cannot test that.
Try this:
Try this:
insert into items
select max(item_id) over()+row_number() over(order by item_id),item_name,year+1 from items;
insert into item_details
select max(id) over()+row_number() over(order by id),
max(item_id) over()+dense_rank() over(order by item_id),
item_contents
from item_details;
Ryan,
Thank you very much. This is what exactly I was looking for. Currently I am trying to customize it according to my need. Now it is already mid night here. I will let you know tomorrow.
Thank you again!
Thank you very much. This is what exactly I was looking for. Currently I am trying to customize it according to my need. Now it is already mid night here. I will let you know tomorrow.
Thank you again!
>>Looks like your query statement will run for 1 row. I have so many rows.
I tested with your sample data so it will insert more than one row.
I tested with your sample data so it will insert more than one row.
Again, this is Oracle but I believe the syntax works in SQL Server as well.
Here is the complete test output:
Here is the complete test output:
SQL>
SQL> select * from items;
1 Mango 2017
2 Banana 2017
3 Apple 2017
SQL> select * from item_details;
1 1 Haden
2 1 Kent
3 1 Francis
4 2 Burro
5 2 Organic
6 2 Manzano
7 3 Red Delicious
8 3 Mclntosh
9 3 Golden (or Yellow) Delicious
9 rows selected.
SQL>
SQL> insert into items
2 select max(item_id) over()+row_number() over(order by item_id),item_name,year+1 from items;
3 rows created.
SQL>
SQL> insert into item_details
2 select max(id) over()+row_number() over(order by id),
3 max(item_id) over()+dense_rank() over(order by item_id),
4 item_contents
5 from item_details;
9 rows created.
SQL>
SQL> select * from items;
1 Mango 2017
2 Banana 2017
3 Apple 2017
4 Mango 2018
5 Banana 2018
6 Apple 2018
6 rows selected.
SQL> select * from item_details;
1 1 Haden
2 1 Kent
3 1 Francis
4 2 Burro
5 2 Organic
6 2 Manzano
7 3 Red Delicious
8 3 Mclntosh
9 3 Golden (or Yellow) Delicious
10 4 Haden
11 4 Kent
12 4 Francis
13 5 Burro
14 5 Organic
15 5 Manzano
16 6 Red Delicious
17 6 Mclntosh
18 6 Golden (or Yellow) Delicious
18 rows selected.
Thanks for using Experts Exchange.
Please provide your email to receive a sample view!
*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.