Dynamically generate create view statement

Question

Dynamically generate create view statement

sqlnewbie2010

SSC Enthusiast

Points: 180
More actions
September 8, 2009 at 4:49 pm

#137990

HI,
I am trying to write a script to dynamically generate a CREATE VIEW Statement that is a union of different tables.
For example:-
Table1
colA colB
Table2
colA colC
Table3
colD colC colB
Table4
colA colB colC colD ColF
CREATE VIEW testView as
SELECT colA
,colB
NULL AS colC,
NULL AS colD
NULL AS colF
FROM TABLE1
UNION ALL
SELECT colA,
NULL AS colB,
colC,
NULL AS colD
NULL AS colF
FROM TABLE2
UNION ALL
SELECT NULL AS colA,
ColB,
ColC,
ColD,
NULL AS colF
FROM TABLE3
SELECT colA,
colB,
colC,
colD,
ColF
FROM TABLE4
The actual tables have 200+ columns and number of tables are also 100+
Is it even possible and feasible to do this in T-SQL?
What other ways can this be accomplished?
Thanks for your inputs!

Viewing 4 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323755 More actions · Answer 1

well...yes it is possible, but without more info, you won't have any useful product.

100 tables? as many as 200 columns per table? an you just want to merge them all into a ten thousand column, billion row view?

let us help you here....tell us why, specifically, you think you need a view like this, isntead of hitting the actual tables on demand. what is the purpose of such a view? I'm sure there is a better way to tackle whatever you are trying to do.

as far as building such a view, you'd have to do something like find the columns...something like select distinct name from syscolumns where object_name(id) in('listOfSpecificTables','ThatYouWant'),

with that list, then you'd dynamically build the list of columns that are not in it...man is that scary, but doable

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sqlnewbie2010 SSC Enthusiast Points: 180 More actions · Answer 2

Thanks for the reply.

The view will not have thousands of columns.

Usually the number of columns are amost same in all tables but there maybe like say 5-10 columns that may be new in a certain table.

We have a number of staging tables per market..eg stage_chicago_XYZ,stage_Seattle_XYZ,stage_NewYork_XYZ.

We don't want users to query the staging tables.We just want to give the user access to a single view e.g viewXYZ which is a union of all the data in the staging tables.

Lowell SSC Guru Points: 323755 More actions · Answer 3

ok, it's making a little more sense; do all the staging tables have a common layout, or, as in your example, they might be missing some columns? is there ONE staging table that truely has ALL the columns?

give us the actual CREATE TABLE of a couple of staging tables, and maybe a couple of rows of INSERT INTO data for those two tables, so we can test to see how the view would look.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!