Dowt in create 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)

Dowt in create View

Author

Topic

iswan
Starting Member

28 Posts

Posted - 2007-06-04 : 05:18:21

I want to create a view from dynamic tables

Query:

CREATE VIEW dbo.TrialView
AS
SELECT *
FROM (select name from dbo.sysobjects where name like 'scTabForm%' and type='u') p

Output:
Name
scTabForm0
scTabForm1

But I want all the fields from table starts with 'scTabForm'

like: select * from scTabForm0,scTabForm1,...

Regards

Iswan

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-04 : 06:30:02

You need to use dynamic sql for this...

I did not understand why you want to create a view dynamically

--------------------------------------------------
S.Ahamed

iswan
Starting Member

28 Posts

Posted - 2007-06-04 : 06:59:04

I handled that in stored procedure. But my Team leader told me "try this in view". that is way I asked 'Is it possible in view?'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-04 : 11:18:40

Do you want to append data using View?
Then you can do

CREATE VIEW dbo.TrialView
AS
Select columns from scTabForm0
union all
Select columns from scTabForm1
union all
.
.
Select columns from scTabFormN

Madhivanan

Failing to plan is Planning to fail

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-04 : 11:33:27

Can't do it in a view as the structure has to be static.
You can generate the view from the system tables though.

Anyway your result would be a cartesian product of the rows in all the tables - I doubt if that's what you want.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

iswan
Starting Member

28 Posts

Posted - 2007-06-04 : 23:46:43

Actually I want like this:

CREATE VIEW dbo.TrialView
AS
SELECT *
FROM (SELECT * FROM sctabScFormHeader a FULL OUTER JOIN scTabForm0 p
ON a.projectFormHeaderID = p.projectFormHeaderID0 FULL OUTER JOIN scTabForm1 q ON a.projectFormHeaderID = q.projectFormHeaderID1
)

Here I used scTabForm0,scTabForm1(statically). But It may be vary from (scTabForm0,scTabForm1,...scTabFormN). So I want to join all these tables in View (Dynamically)

Iswan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-05 : 09:35:38

You cant use Dynamic sql in a view
Why are you using full outer join?
Do you want to append all data into one?

If you cant change the database structure, dynamic sql is the way
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources