Link to home
Start Free TrialLog in
Avatar of RadhaKrishnaKiJaya
RadhaKrishnaKiJaya

asked on

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
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

This runs in Oracle and should run in SQL Server as well but I cannot test that.

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;

Open in new window

do you want to do it in MSSQL or Oracle?
Avatar of RadhaKrishnaKiJaya

ASKER

It is MSSQL.
Hello slightwv,
Looks like your query statement will run for 1 row.  I have so many rows.

Thank you!
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!
>>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.
Again, this is Oracle but I believe the syntax works in SQL Server as well.

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.

Open in new window

Thank you Ryan.  Your query worked great.  Thank you again!

Thank you slightwv for your help.  I used Ryan's query it solved my problem.