[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

SQL Script Question

I created a SSIS package that will update certain tables every year.  Prior to updating these tables I want to run the "execute sql script" to change the current year to the next year automatically.  For instance, I want to 2011 to 2012.  I know I can update a column like...

update tablename
set columnname = 2012
where columnname = 2011

But I want to have this done automatically at the beginning of my SSIS package and not for just this year, but years to come, without having to worry about it.  Is there a sql script that would perform this.  Thanks in advance.
0
Tyecom
Asked:
Tyecom
  • 3
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
Hi.

There are multiple ways to do this. First if the data always has the current year only when run, you can simply use YEAR() and GETDATE() to automatically generate the values like:

UPDATE tablename
SET columnname = YEAR(GETDATE())+1
WHERE columnname = YEAR(GETDATE())
;

Open in new window


Or even:

UPDATE tablename
SET columnname = columnname+1
WHERE columnname = YEAR(GETDATE())
;

Open in new window


If the scenario is a little more complex, then you can use other methods like finding the MAX year in the table and incrementing that by +1. It just depends. Let me know if the above doesn't solve your scenario.

Kevin
0
 
jvejskrabCommented:

would't be better to have COMPUTED column in these tables?

like...

CREATE TABLE #test (
      i int IDENTITY(1, 1),
      actualYear AS DATEPART(YY, GETDATE())
)

or, is it really necessary to have some column like that in a table at all???
What's the meaning of that column?? Which way do you use it in queries ??

0
 
TyecomAuthor Commented:
Thank you both for responding.  MWVISA1 your approach seems to be what I want, I will be testing it and will get back to you.  jvejskrab, it is necessary to have these column, they are  part of a larger scope.  The SSIS package will be ran once a year at the end of each year.  The tables will be updated with the current data and the year column needs to be updated to the next fiscal year.  Again, I'm just trying to automate this entire process.  It used to be done manually.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
TyecomAuthor Commented:
Mwvisa1 your approach worked great.  Thank you very much.  I chose the first option and it worked just like I wanted it to.  Thanks again!
0
 
TyecomAuthor Commented:
Worked great!  Thank you very much!
0
 
Kevin CrossChief Technology OfficerCommented:
You are most welcome. Good luck!
Best regards and happy coding,

Kevin
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now