Learn how to a build a cloud-first strategyRegister Now

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

rename TSQL

Logic

Hello All,
Hello All,


TSQL – renaming tables logic – I am looking for a renaming table logic

if you find 2013 as part of any columns names or one column within a table “rtyu_25”–
then if that table name doesn’t have  a “_13” at the end of table name>>
 then rename as table name + “_15”
else
 do nothing
0
Rayne
Asked:
Rayne
  • 3
  • 2
  • 2
  • +1
3 Solutions
 
nemws1Commented:
Not sure I understand the logic you are describing.

This:?
1) Any column name has "2013" in it *OR* any column starts with "rtyu_25"
2) Table name must *not* end in "_13"

If so, execute this in SSMS text mode and then cut/paste the output in a new query and run it.

SELECT DISTINCT
	'EXEC sp_rename '''
	+ t.NAME
	+ ''', '''
	+ t.NAME
	+ '_15'';'
FROM sys.columns AS c
	INNER JOIN sys.tables AS t
		ON c.object_id = t.object_id
WHERE (
		c.NAME LIKE '%2013'
		OR c.NAME LIKE 'rtyu_25'
		)
	AND t.NAME NOT LIKE '%_13';

Open in new window

0
 
PortletPaulCommented:
AFTER making a backup, yikes
0
 
nemws1Commented:
For sure, PP!
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Why do you want to rename a table? Worst, not one but many!

Do you know the implications of this operation? If the table have dependencies they won't work anymore until you rename them also.

If you could tell us what do you want to achieve maybe we can point you to a better solution.
0
 
RayneAuthor Commented:
Hello all,

Thanks to everyone for your very wise suggestions. The issue is I have two ssis jobs that are year specific. So one job refers to files that RE OF 2013 and the other (identical ) job refers to files of 2014. Now after the data is imported into the DB, it undergoes several transformation. So for example staging table for dataB is named stage1. Note: all the 12 months are across the first row as column headers for a specific metric.
Table 1 (specific to 2013) >>
Account | product |2013P1 |2013P2|……….2013P12

Table 1 (specific to 2014) >>
Account | product |2014P1 |2014P2|……….2014P12


Now the way I was thinking is keep two tables of identical structure but different column heade4rs and then have two tables – stage1_14
Stage1_13
Stage1 is the main staging table that imports in the data for a specific metric
When I am running the ssis job specific to 2013, I will rename stage1_13 to stage1
When I am running the ssis job specific to 2014, I will rename stage1_14 to stage1

Let me know if I am thinking too complex or making my life difficult….
Also what is some good practice when I have work with different years of same data metrics at the same time? Please advise.
0
 
nemws1Commented:
If it were me, I would use "Tasks -> Generate Scripts" to generate the table structures you have now and then just use Search/Replace in a text editor to get the SQL to create new, empty, tables with the names you need.  Keep that script around and just use it year after year (updating for any schema changes you make, of course).
0
 
RayneAuthor Commented:
thank you , thank you
this is a life saver :)
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can use the script and modify it to create the tables with the current year on the name:
- Just concatenate the table name with YEAR(GETDATE())

Then you won't need to change it every year.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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