Can you write out multiple views automatically?

Question

Can you write out multiple views automatically?

Viewing 15 posts - 16 through 29 (of 29 total)

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

Bill Cockerill SSC Enthusiast Points: 195 More actions · Answer 1

Really enjoyed reading this discussion though it was beyond my understanding in many parts!

Coming back to my original query I found that with User Defined Functions (UDFs) and Thomas’ looping T-SQL I could solve my problem without writing out lots of views.

1. I created two views, one that appended all my customer tables (one each month over 5 years), and another view that appended all my transaction tables (used UNION ALL in the views to append). I put each of these views into separate UDFs.

2. I joined and grouped by the customer and transaction UDFs in a view. Then put this view inside a third UDF.

3. This final UDF I wrote out into tables for each month using Thomas’ looping T-SQL (posted earlier in this thread).

Seems to work quickly and I’m not left with loads of views cluttering up the database. Also it’s easier to alter one UDF rather than write out all the views again.

Bill

ThomasH SSCertifiable Points: 7437 More actions · Answer 2

Bill, I'm glad we all could help, even if we like to get off topic once in a while. Usually Frank's replies are right on the mark and helpfull. They will likely lead you down a path of resolution you will not regret. That is the case in this thread also. If / When Frank does make a mistake (because he's up to late helping all of us), he is quick to respond to correct it. As you come to enjoy this forum, you'll find many members as helpfull and conciencious as Frank, all giving their time to further this great SQL community.

Enjoy

Once you understand the BITs, all the pieces come together

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

Hey Thomas,

it's not about you, me or anyone else here on this forum. This IS actually a community. Everyone makes mistakes, feels too brain dead to answer even the simplest question from time to time. But together it's only a matter of time, till a satisfying solution is found.

And I do hope that you stay around here. I certainly enjoy and appreciate your posts.

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

David Burrows SSC Guru Points: 65142 More actions · Answer 4

Modest as ever Frank.

Far away is close at hand in the images of elsewhere.
Anon.

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

I am what I am.

But good to see you're online, Dave. Did I ever mention that my laptop has now 1GB RAM and that I'm on top of the list of persons that will get next a Thinkpad T41 ?

I'm looking forward for the weekend, duhbiduh....

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

ThomasH SSCertifiable Points: 7437 More actions · Answer 6

Oops, Sorry David , I was thinking of you while writing... never got to it because the thought of "timing out on post submit" took over. I like giving "props" to posters...when I 1st joined, it made a difference to me on who's posts where worth reading if the toptic was not of interest to me.

Go to go

"Thanks" all of you. I really really enjoy this dialog with you all when time permits Happy SQL'ing

Once you understand the BITs, all the pieces come together

David Burrows SSC Guru Points: 65142 More actions · Answer 7

Bah phoey Frank! But hey you do burn that midnight oil eh Glad my PC has 1GB tho.

Thanks Thomas, it's nice to see people take time to appreciate others in the forum. I know what you mean by topics of interest, it seems quite common now to go 'off topic' without starting a new thread

Far away is close at hand in the images of elsewhere.
Anon.

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

Bah phoey Frank! But hey you do burn that midnight oil eh Glad my PC has 1GB tho.

Language barrier! burn that midnight oil ??? Hey, did I make you nervous. You hand seems to shiver while typing...

Thanks Thomas, it's nice to see people take time to appreciate others in the forum. I know what you mean by topics of interest, it seems quite common now to go 'off topic' without starting a new thread

Maybe we should ask for seperate forum, where we could praise ourselves and anyone else?

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

David Burrows SSC Guru Points: 65142 More actions · Answer 9

Language barrier! burn that midnight oil ???

Careful you don't break your arm patting yourself on the back

Far away is close at hand in the images of elsewhere.
Anon.

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

Hey, I'm married!

My wife has some very efficient methods to bring me back to reality. ....

Hm...where is the blackeye emoticon...

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

Jonathan SSC-Insane Points: 20427 More actions · Answer 11

I found what really makes a difference with my local Developer Edition is the 7200rpm drive in my new T41p laptop (with 1.5GB RAM).

Off-topic? Who, me?

--Jonathan

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

That's more than I can bear, Jonathan.

You got me going home now.

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

David Burrows SSC Guru Points: 65142 More actions · Answer 13

My wife has some very efficient methods to bring me back to reality

I know what you mean and the blackeye very appropraite (I too miss that icon)

I found what really makes a difference with my local Developer Edition is the 7200rpm drive in my new T41p laptop (with 1.5GB RAM)

Nice

Far away is close at hand in the images of elsewhere.
Anon.

Rohit @ SQL SSC Eights! Points: 938 More actions · Answer 14

Rohit @ SQL

SSC Eights!

Points: 938

January 30, 2004 at 8:45 am

#492357

a

Rohit

ThomasH SSCertifiable Points: 7437 More actions · Answer 15

Man it goes down hill fast when posts are shorter than "short stories"

Once you understand the BITs, all the pieces come together