Can you write out multiple views automatically?
-
Dear Forum,
I have two tables of data: a transaction table and a customer information table.
There is a new table each month stretching over 7 years.
I need to join the two tables for each month, then using "group by", write out aggregated information into new tables.
The join of the two tables contains just over half a million records each month so I'm writing a separate view for each month to "group by". Then using all the resulting tables in a view with "union all". This way I don't use all the power on a heavily used server.
The problem is I have to write out over 84 separate views this way!
Is there a way to write out automatically multiple views automatically inserting different dates each time. Can some kind of loop be written that goes through this process? (rather like a macro in Excel). Or am I going about this in the wrong way?
Any advice is much appreciated.
Thanks, Bill Cockerill
-
As long as the "CREATE VIEW...." statement is the only statement in a script you can create views in T-SQL using dynamic sql....
Declare @SQL Varchar(8000)
Set @SQL = 'Create View [ViewName] as Select * from [Table]'
EXEC (@SQL)
You can have a loop modifing the contents of @SQL each time. You may want to include....
If Object_ID('[ViewName]') is not NULL Drop View [ViewName]
Prior to each View Creation.
Hope that helps.
Once you understand the BITs, all the pieces come together
-
The above solution is what I'd do.
-
Thomas,
Many thanks for your advice. It works! I've written the T-SQL below that writes out all the views I need.
The only part of your advice I can't get to work is dropping the view if it already exists. My full working code is below, but I've commented out the dropping line which it does not like. It seems it won't let me concatenate an object name inside the function Object_ID.
I've also tried declaring another variable and concatenating the view name before putting it in Ojbect_ID, but it's not happy with this either. Any suggestions?
Thanks, Bill
Declare @SQL Varchar(8000)
Declare @yearcount varchar(7)
set @yearcount=199701
WHILE @yearcount>=199701 and @yearcount<=200212
BEGIN
-- If Object_ID('NBP'+@yearcount) is not NULL Drop View 'NBP'+@yearcount
SET @SQL='CREATE VIEW NBP'+@yearcount+' AS SELECT * FROM f_customer'+@yearcount
EXEC(@SQL)
IF substring(@yearcount,5,2)=12
SET @yearcount=@yearcount+89
ELSE
SET @yearcount=@yearcount+1
END
-
While the above will certainly work, one might think the whole approach is not optimal.
Do you have any chance to only have one transaction and one customer table?
With appropriate table structure and indexing, there is basically no need to make up a new table for each and every month. With that design you are forced to use dynamic sql which certainly will make your SQL code less maintainable.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
It is the drop statement not object_id that is the problem. You will have to use dynamic sql like
SET @SQL='If Object_ID(''NBP'+@yearcount+''') is not NULL Drop View NBP'+@yearcount
EXEC(@SQL)
SET @SQL='CREATE VIEW NBP'+@yearcount+' AS SELECT * FROM f_customer'+@yearcount
EXEC(@SQL)
or alternatively use ALTER like
SET @SQL='CREATE '
If Object_ID('NBP'+@yearcount) is not NULL SET @SQL='ALTER '
SET @SQL = @SQL + 'VIEW NBP'+@yearcount+' AS SELECT * FROM f_customer'+@yearcount
EXEC(@SQL)
Far away is close at hand in the images of elsewhere.
Anon. -
David,
Yes you're right it was the drop statement that didn't work. Many thanks for your two suggestions. I pasted in the first one and everyting works beautifully.
Frank,
Yes, a very good point. However, the characteristics of the customers in the customer table change each month so customer and transaction tables must be matched each month. Overall that's 1.7 million customers to 0.5 million transactions each month. I'm not the database adminstrator but I will pursue indexing with the DBA.
Thanks again to all those posting. A really great site.
Bill
-
The characteristics change each month?
I'm not sure if I understand you
However, this might be interesting for you.
http://www.sommarskog.se/dynamic_sql.html
especially the chapter
http://www.sommarskog.se/dynamic_sql.html#Sales_yymm
I think Erland is right on almost every point in his article.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank, I understand where you are coming from, and generally Erland's ideas are Ok I guess. but...
99.9% of my SQL work has no client interface at all, it's all just data driven processing. I come from an Application Code Generation background (mostly front ends to DBs), and when I could use the native database backend as my code generation engine, all the better. In my case, alot of my SQL code and objects are being built and manipulated by other database objects, and Dynamic SQL is the only way to do this if I want have SQL Srv be my "code generator". This is just my perspective on thing, and I know, it's not the way most approach database usage.
It's natural for me to think in this way, and I treat T-SQL as a "general purpose" language. This will become even more powerfull with Yukon. All my T-SQL code is in a table itself, when I need to "upgrade" one of my customers, I just get the whole new contents of that table to them, and call my "rebuild yourself" SP. On my development desktop I have a little VB application that I use to maintain the data in the table. It has all kinds neat things I can do with my code since I'm treating my code as data in the database. Everything is in the database... most of the client front ends that deal with the "real" data, get thier own SQL code, screen layouts, buss. rules, from the database also. So very little "hard coded" SQL in the client apps. Client apps are just pretty much data interpreters. This lets me "upgrade" client applications (bug fixes or new features) by just manipulating database records in SQL Srv. So I guess I do pretty much only Dynamic SQL
Other developers are resposible for the client applications. They understand "data driven". Enhancements to the applications are mostly just adding new ways of intepreting the data for presentation or for processing. The cycle of delivering solutions to customer's changing demands is to short compared the time it takes to get new software versions deployed, so for us this is the best way to satisfy the market needs and have an edge on any competitors.
Just another "point of view". I do not consider your's or Erland's veiwpoint incorrect or more correct or less correct than others, just all a little differrent than each others. I think this is what makes all this so very interesting, and if you, like I, have a passion for this stuff, the "best way to make a living" there is ( aside from maybe movie/sports/music star).
Once you understand the BITs, all the pieces come together -
Thomas,
see I'm in a position I doubt anyone else here on the forum is. I don't do programming for a living anymore
This is more of a hobby of me, and because I am a very comfortable person, that likes to automate things whereever I can to spent less time as possible on stupid number crunching and have more time for the truely interesting and creative aspects of my work, I use my skills to develop apps I need. This implies that I have all the time I need. Actually I can think on my database design, indexing strategy, T-SQL programming and alike. And be assured that I do think on those things. And if a solution does not work at once, maybe it will tomorrow or next week. For me this is a kind of perfect programmer world. And as this is a hobby of me, I am interested in getting a deeper understanding on the background of databases. One true advantage of relational databases is that they have a solid house of thought. Thanks to Dr. Codd! Now, the fact is, that, as I become more and become familiar with the set based theory mindset (or database mindset in general), the time I need to spent on thinking about database solutions is getting shorter and shorter.
T-SQL and database programming require a different mindset than front end programming. Parameterizing your code to get generic code is a honorable goal, but not in database programming! The db is not meant to be some kind of 'code generation engine'. Neither is T-SQL a general purpose language. In fact, I think just the opposite is the case. T-SQL serves only one purpose and has a very limited scope. And that's good so! As you've mentioned Yukon and the implementation of the CLR in SQL Server. I highly doubt that this is a step in the right direction. I think it is marketing dictated and will only lead to more and more horrible code.
Have you actually read Erland's article? I did, and I did this quite closely, because I've just finished the german translation of it. IMHO, Erland is right on almost every point, but missed to mention one thing (maybe intentionally, I don't know).
Dynamic SQL and Cursors are something that looks familiar to programmers who just started T-SQL programming, but know some front end language. The concepts of row-by-row processing and generalizing your code is something they know. You've mentioned one (more or less) valid reason for this. Programmers have their deadline, project milestones and whatsoever. So they use practices they know that these will get the job done in the given timeframe. Gotcha... Once you've start an app using cursors and dynamic sql, and it goes into production, you're bound to this, because the cost for reengineering are far too high.
Do you think this is an optimal approach? I don't.
You won't find any use of cursors or dynamic sql in my production code (yes, I do have apps in production!), because with a proper design I see no need for such a solution.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank,
I was looking forward to your response
. I knew I could get you to write a "short story" length post 
.... I appreciate you taking the time to continue this thread. Been pretty busy, and haven't had the time to respond "right away". No matter, this topic is not an "urgent response needed" thing anyway. I feel a little guilty because I know you, like me, are putting some thought behind what you write, and thus taking time away from you to post your great answers on other threads that could benifit from your timely responses. I'm wondering a little why others have not posted on this thread??? Perhaps to fully convey what they think takes more time than they have at the moment, or maybe they're waiting and watching ....I understand when programming the database to do the "only one purpose and has a very limited scope" type operations, that Dr. Codd's statements and the thoughts in Erland's article you support, should be followed and implemented as best as possible in the "normal" database environment. So my database objects (that do the "normal" type db operations) allign themselves with these ideas. In otherwords, when my db application comes across a new form of data to deal with, because of a new "data specification" an external, automated source has "directed" my system to process, my db application goes ahead, maybe using lots of dynamic SQL, an generates new db objects (tables, indecies SPs, Views, Server links.... whatever) to handle the "new" form of "business" data to be processed. These generated [semi permanent] objects follow, as much as possible ... Cobb's rules, no use of Dynamic SQL, no Cursors etc. and will look about the same as if someone had written/created them by hand following your general ideas on the subject. Given that, I will however, break from the "rules" in order to produce significant performance gains, or make a very complex process much more readable.
Do you consider this "using dynamic SQL"?
I do not agree with your statement that SQL Server is a program "only one purpose and has a very limited scope". I can think of many things that a "real" database could handle much much better than the proprietary "database(s)" they use, such as Client (and for that matter Server) Email, the MS Registry, the O/S file system, Source control, document control ...etc. All of these, do mostly "database" work as their primary operation (primary meaning "the stuff we users of them usually have to wait for them to do), and they mostly all suck at the "database" work they try to perfom. "Real" databases have had the best minds available, and the most development time and resources spent, on making the database "engine" portion of their product perform database tasks fantastically. This is very unlike the resources spent on the "database" portion of the above mentioned programs. I think MS is actually going to replace the O/S FileSystem and "Registry" with a form of SQL Server in their next "major" Windows release??? Or so I've heard rumors. Then us database guys realy "rule", the C programmers will be coming to us
.I think of application code, and business logic as "data" in a "database". Todays "real" databases are great for keeping track of all the objects of an application, system of applications and the "business" behind them. This is especially true of database applications, since within the database and engine are both the data and the intelligence to deal with the data. Both are accessible at the identical time and both can "know" about each other natively. Imagine if you had relational database access to all your business rules, the code that implements them, and the data itself all at the same time. Now, I have no problem with the idea of keeping the different "components" logically separated in the database, but then again keeping "data" separated into to logical groups while still having relational access to them, is what these "real" databases like SQL2K to best.
Just my $.02, make that $.05 on the subject.
Looking forward to the further posts in this thread.
Once you understand the BITs, all the pieces come together -
Thomas,
to be honest, I don't know why we're the only ones to post in this thread? Maybe its off-topic...
Actually I am no upholder of moral standards who preaches: "Thou shall not use dynamic sql or cursor."
What I am trying to convey is, that, as you know, there are almost every day questions on dynamic sql here on the forum. The answer is ever so often use dynamic sql accompanied with an example and that's it. Now, when you take a closer look at most of these questions, the same result can be achieved with static sql (and sometimes with an even better result in terms of performance). Well, using static sql might require investing some more brain work, but should outweight the downside of dynamic sql by far.
However, who am I to judge on someone else's approach? I am not interested in convincing someone here of my position and/or opinion. The members here should be professional enough to know what they do and to take accounts for their actions. All that might be possible, is to ask if they have considered every aspect of dynamic sql (or cursor). So, if you or someone else decides to use these techniques for whatever reason, that absolutely perfect with me.
To be honest, I don't think Dr. Codd really cared about dynamic sql or cursor. He did invent the relational model, not sql, which are different things.
I do agree, in the end everything *could* be viewed as some kind of databases. However, SQL and the relational model do a great job on structured data (as the S in SQL implies), but there are other kinds of data, that require a different handling like geographical data, spatial data....
Yes, I've also heard these rumours on the future way of SQL Server and Windows, but I also heard rumours, that Elvis is still alive
(Sorry, couldn't resist 
). I don't believe rumours, until they become hard fact.Maybe we should focus on answering questions again?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
OK, OK, short reply just to keep you happy.
The only reason I did not respond before is that I am not well versed in all this theory and would produce a lot of waffle or drivel. I am a developer, thru and thru. I found the articles listed interesting but, as yet, not rushed off to see if waht I am doing is right or wrong according to Codd. I never had formal training or help in database design, all I ever did was SQL6.5 Admin and that was practically useless. I use what I have learned over the years, the mistakes I have made. I first started on a Mainframe database, then on a Mini (small version of Mainframe), then back to Mainframe. All this before I started on PC's and Access.
As to the question of TQSL, dynamic sql and cursors. I use what works within the timescale and tools available.
TSQL is very powerful in a lot of things it does but also very poor in other areas. It is, after all, a language for updating a database not a full programming language. By levering its power and the power of the server I have had remarkable performance improvement by replacing applications in VB with TSQL. I have production systems that use dynamic sql to access a database using a 3rd party ODBC driver and as yet not found a more efficient way of doing it. Virtually all my systems use stored procedures to keep maintenance to a minimum.
On one system I have more than 394 procs, of which approx 181 are run daily and of those approx 55 use cursors. I did it this way to prevent having to write and maintain masses of VB code but had to use cursors to overcome logic problems. I have done testing to replace them with set based sql with some success and performance gains.
Overall I use what works within the constraints of timing and performance and I don't think there is right way or wrong way only a better way. I have seen systems written to someone's exact rules and ended up with a rigid, inflexable system that is impossible to maintain.
Far away is close at hand in the images of elsewhere.
Anon. -
Dave, yes, your reply makes me happy
As for the theory behind. There is the relational model (Dr. Codd), then there is SQL (Donald Chamberlin et al. ) and then there is T-SQL (Microsofts' version of SQL). So far, so good. Someone might be interested in that theory or not.
Btw, Access exposure can be treated, and maybe some day you will get your normal life back.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
"Btw, Access exposure can be treated, and maybe some day you will get your normal life back."
Yeh but I hated those padded cells.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply