We help IT Professionals succeed at work.

Copy data to New Year

118 Views
Last Modified: 2017-04-09
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
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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

Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer / Applications Consultant
CERTIFIED EXPERT

Commented:
do you want to do it in MSSQL or Oracle?

Author

Commented:
It is MSSQL.

Author

Commented:
Hello slightwv,
Looks like your query statement will run for 1 row.  I have so many rows.

Thank you!
Software Tead Lead / Business Analyst / System Analyst / Data Engineer / Applications Consultant
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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!
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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

Author

Commented:
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.
Unlock the solution to this question.
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.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.