Dynamic table in View? - 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)

Dynamic table in View?

Author

Topic

swatib
Posting Yak Master

173 Posts

Posted - 2006-02-07 : 03:49:02

I need to create a view. It will contains Select statement in which dynamic table is used. How could I do this?

Njoy Life

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2006-02-07 : 03:54:21

what do u mean by dynamic table? r u refering to temperory tables?

swatib
Posting Yak Master

173 Posts

Posted - 2006-02-07 : 04:51:27

See, there are tables which are created daily e.g. Table04022006 etc i.e. Tbl name+today date.

So I have to create a view for taking records from a table depends on the date parametere.
for e.g. If the date parameter is 02/04/2006 (todays date) then the records from Table04022006 has to be retrieved.

Njoy Life

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-02-07 : 06:43:08

Use union. Search partitioned views in BOL.

swatib
Posting Yak Master

173 Posts

Posted - 2006-02-07 : 06:59:23

But I wanted to use the dynamically created table name in Select statement instead of static table name.

I didn't understand ur answer

Njoy Life

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-02-07 : 07:04:59

My idea is to create tables in advance. That's the best solution I figured out so far.

swatib
Posting Yak Master

173 Posts

Posted - 2006-02-07 : 07:11:45

which tables u r talking about?

As I told before, tables are created daily e.g. Table04022006 etc i.e. Tbl name+today date.

Njoy Life

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-02-07 : 07:15:51

Can you create tables in advance and populate them daily? In that case you can use union:

select ...
from table04022006
union all
select ...
from table05022006
....

and so on. Anyway, what is the reason you create daily tables?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-07 : 07:17:27

quote:
Originally posted by swatib

which tables u r talking about?

As I told before, tables are created daily e.g. Table04022006 etc i.e. Tbl name+today date.

Njoy Life

have a script to alter your view daily after the daily table is created

----------------------------------
'KH'

swatib
Posting Yak Master

173 Posts

Posted - 2006-02-07 : 07:29:01

I'll explain-

I need to show daily report on user date input.
Suppose user gives a date like 04/02/2006(m/d/y) then I have to select recods from Table04022006
I have to create some child views using this tables and then create a final view which should be outer join of these child views.

here I'm unable to have this dynamic created table name in a view.

Njoy Life

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-02-07 : 07:41:29

1. K H Tan's suggestion is good one.

2. What I don't get from your explantion is: What is the problem with having smallDateTime column in a single reporting table and preparing reports from there? This is the standard way of handling such report and with a good index design it is usually the simplest and the most efficient solution. What is so special in your app/process that you have to go with so complex solution?

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-02-07 : 07:44:17

To rephrase khtan's suggestion: Alter view immediately after creating new table replacing old table reference with new one.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-07 : 07:58:56

quote:
Originally posted by swatib

which tables u r talking about?

As I told before, tables are created daily e.g. Table04022006 etc i.e. Tbl name+today date.

Njoy Life

Not a good idea. YOu should have 1 table, with a datetime value stored in each row to represent the day for each row. Then you do not need any dynamic SQL, everything is more efficient and easier, and you are using a relatonal database properly.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-07 : 09:43:08

Also, why do you want to create seperate table to hold each day's data. As Jeff said, it is easy to have a single table with datetime column and other data. Then you will be able to get the data by passing datevalue in where condition. Also you cant pass parameter to a view to execute it to select data from dynamic table although possible from Stored Procedure.

Madhivanan

Failing to plan is Planning to fail

swatib
Posting Yak Master

173 Posts

Posted - 2006-02-07 : 23:25:31

Creating separate day table is the project requirement. I know keeping datetime field will be easy in a single table but because of this requirement I've to create table name dynamically depending upon the entered date.

Also I could have use one stored procedure to do my work but there are 4-5 child views required to create one final view from which i can read data for report

Try to understand my problem and give me proper direction

Njoy Life

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-07 : 23:39:54

quote:
Try to understand my problem

I think we understand your problem but don't understand the requirement.

quote:
Creating separate day table is the project requirement

Can you create a single table with datetime as part of the PK and create separate day view instead of table ? So you don't have to create the daily table Table04022006 but create Table04022006 as a view from that single table.

Then your initial requirement of view :

quote:
I need to create a view. It will contains Select statement in which dynamic table is used.

should not have any issue.

----------------------------------
'KH'

swatib
Posting Yak Master

173 Posts

Posted - 2006-02-07 : 23:53:37

But the live project is in the mid and I'm designing crystal reports for the same

Njoy Life

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-08 : 01:09:33

>>Creating separate day table is the project requirement.

Sounds bad

>>because of this requirement I've to create table name dynamically depending upon the entered date.

Then how do you enter data to that table?
Do you use bcp?

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-08 : 07:13:59

quote:
Originally posted by swatib

Creating separate day table is the project requirement. I know keeping datetime field will be easy in a single table but because of this requirement I've to create table name dynamically depending upon the entered date.

Also I could have use one stored procedure to do my work but there are 4-5 child views required to create one final view from which i can read data for report

Try to understand my problem and give me proper direction

Njoy Life

The problem is the decision to create a new table each day. You are really making things difficult for yourself. Haven't you asked yourself yet "why is this so complicated? There must be an easier way?" What happens when a report needs to consolidate data over a range of days?

The proper direction is to stop, put all your data in 1 table, and fix things. Do it now before it is too late; it should be an easy change to make since you will essentially be takng dozens of lines of complicated dynamic sql that creats and selects from multiple tables and replacing it with a single line of standard SQL in each case.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources