Assign date to table name - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Assign date to table name

Author

Topic

Rupa
Posting Yak Master

123 Posts

Posted - 2007-04-04 : 08:23:53

Hey all

I'm trying to assign a date to a table name. How do I do that?

I would like to create a procedure which would transfer details onto a new table before replacing the table with new data. I know how to do that but I don't know how to add today's date to the table name.

For example:

CREATE PROCEDURE proc_RenameNewDataTable

AS

SELECT *
INTO
tbl_OldData&getdate()
FROM tbl_NewData
GO

The &getdate() brings up syntax error.

Hope this makes sense.

Many thanks in advance!

Rupa

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-04 : 08:28:24

[code]declare @tbname varchar(255)

set @tbname = 'tbl_OldData' + convert(varchar(10), getdate(), 112)

exec('select * into ' + @tbname + ' from tbl_NewData')[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Rupa
Posting Yak Master

123 Posts

Posted - 2007-04-04 : 08:33:46

You're a star! Thank you soooooooooo much

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-04 : 08:55:04

Thats anyway you should avoid until you have db design where you need to create table for each month which has same numbewr of columns

Madhivanan

Failing to plan is Planning to fail

Rupa
Posting Yak Master

123 Posts

Posted - 2007-04-04 : 09:17:37

Yeah that's the plan!! We need to keep hold of old data before we replace it with new data. And columns are the same too.

Thanks again Harsh

Rupa
UK

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-04 : 09:23:19

Well. In that case if you want summary information on more than one month data you need to extensively use Dynamic SQL

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-04 : 09:32:37

Thanks Rupa. Glad it worked for you.

But how often do you need to dump such data in new table? If it is on frequent basis, You may be having lots of such archive tables lying around which won't be of much use. Are you trying to do versioning of the data?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Rupa
Posting Yak Master

123 Posts

Posted - 2007-04-04 : 10:00:15

It would be about once a month or so. It could be less than that. So it's not very frequent. Do you think that's ok?

Thank you both for the quick response.

Rupa
UK

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-04 : 10:09:10

What if you need to restore the data back from one of the archive table? Or what if somebody just wants to see what the data was in 4 months before? How would you deal with such situation while maintaining flexibility in your app?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Rupa
Posting Yak Master

123 Posts

Posted - 2007-04-04 : 10:16:58

That's why we've got the date added to it so we can run a query based on that!

Rupa

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-04 : 10:25:22

Well, in that case managing tables is going to be nightmare. Also, you may need to make lot of use of D-Sql as Madhi said, which is going to hurt performance severely.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Rupa
Posting Yak Master

123 Posts

Posted - 2007-04-04 : 10:30:41

I shall take yours and madhivanan's advice :-)

Thank you again!!!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-04 : 13:18:20

Without knowing exactly what you are doing, I'd suggest that you investigate Partitioning in BOL. That way you can create a table for a particular date (say for a month) and restrict data to that for only that time period. Then you can put a view on top of that and you should be able to use one view to access your paritions and still make use of indexes.

Cheers,

-Ryan

X002548
Not Just a Number

15586 Posts

Posted - 2007-04-04 : 13:21:24

Why don't you add 1 column to your table for the date?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Rupa
Posting Yak Master

123 Posts

Posted - 2007-04-04 : 17:30:12

Good suggestion but that won't do the job unfortunately. Thanks though...much appreciated.

Rupa

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-04 : 23:54:29

If you cant avoid dynamic sql, read this to know more about it
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Rupa
Posting Yak Master

123 Posts

Posted - 2007-04-05 : 04:26:53

You're all awesome. Can't stop saying thanks but here it is again. Thank you!!!!!!!!

Rupa

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-05 : 04:31:23

quote:
Originally posted by Rupa

You're all awesome. Can't stop saying thanks but here it is again. Thank you!!!!!!!!

Rupa

You are welcome

Make sure that you read it fully

Madhivanan

Failing to plan is Planning to fail

Rupa
Posting Yak Master

123 Posts

Posted - 2007-04-05 : 05:08:27

Shall do! Thank you gentlemen!

Rupa

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources