Annual Tables

Question

Annual Tables

sqlok

Old Hand

Points: 311
More actions
October 14, 2003 at 5:53 am

#83134

I have three tables a.2003, b.2003 and c.2003. When the year rolls over to 2004, I was wondering is there an easy way to automate the renaming of these to a.2004, b.2004 and c.2004.
Appreiciate advice,
sqlok

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ray_higdon SSCarpal Tunnel Points: 4589 More actions · Answer 1

First question would be, why do you have this design? You could certainly create a script that was scheduled to run January 1st of every year to rename these tables, but why are you doing this?

Ray Higdon MCSE, MCDBA, CCNA

------------

Ray Higdon MCSE, MCDBA, CCNA

gundapaneni Mr or Mrs. 500 Points: 593 More actions · Answer 2

There is a way to change it. But we need to write script as per my knowledge.

Madhu

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Writing the script shouldn't be difficult, but as Ray mentioned, the even better question is why?

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

sqlok Old Hand Points: 311 More actions · Answer 4

The database was designed with yearly separate tables. For querying purposes views were created and past tables are joined. I believe this was done to speed up queries and reduce having large data tables.

guarddata SSCertifiable Points: 6583 More actions · Answer 5

You can use the procedure sp_rename <oldtablename>, <newtablename>

You might want to consider the original design to assure that you really want to rename - or do you want to create new tables for 2004.

Guarddata-

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

A basic script could look like this



SET NOCOUNT ON

USE Northwind

GO

DECLARE @newname VARCHAR(50)

SET @newname = CAST(DATEPART(yyyy,GETDATE()) AS CHAR(4))

EXEC sp_rename 'customers',@newname

GO

SELECT * FROM [2003]

EXEC sp_rename '2003', 'customers'

SELECT * FROM customers

SET NOCOUNT OFF

Still I think this design is questionable, maybe suboptimal implemented.

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

sqlok Old Hand Points: 311 More actions · Answer 7

The tables are separate. For example, 1990, 1991, 1992...........2003, etc....... Also, In this case, what is the best table design, separate tables or one table with all of the year's data inclusive?

guarddata SSCertifiable Points: 6583 More actions · Answer 8

Nothing wrong with tables being named for the year in my mind. The real question I would have is ... why rename? Seems like another table should be created. Perhaps a view needs to be updated to read from the new table. I imagine there is much more at stake that a simple table name here.

Guarddata

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

To piggy-back on Guarddata normally I would say keep it in one table, dessign and index it properly and you'll be fine.

But without knowing what kind of application (maybe even third-party), the data volume, requirements.....it is hard to tell.

So bottom line:

If you don't have plenty of time to redesign and/or rewrite AND it is working right now, stick to it.

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]