Query Assistance
-
So, I finally got the job as DBA (BooYah!) and I'm writing a stored procedure that I'm wondering if it could be considerably more efficient/easier. I hope I can make myself clear, I forgot to send my SQL home, but hopefully my question is generic enough for theory.
We have a data warehousing table which I'm trying to fill with several pieces of information; the idea being one row per account, per month. The data is easily obtained, the queries are very easy that is, but they don't seem easy to group into one statement to fill the table with one single Insert statement; it's got such things as CompleteWorkRequests and OpenWorkRequests.
My idea is to grab each piece of information, the first one I'll insert the new rows, and each of the subsequent ones I'll update that row. The only way I can think to do this is with each subsequent query fill a temp table and then run a cursor to update the main table provided the account id in question is there or insert it and put zeros in for the data that isn't there. This seems really convoluted to me. This stored procedure will be run as a job each night, so time sort of isn't an issue.
I was also thinking perhaps make the pieces of information into select statements themselves (I think the term is SubQueries?) but I'm not sure how to write those well, and anyway I don't have my SQL as an example; I'm sorry! I'll post it tomorrow morning when I get to work. But also, two of the pieces of information are percentages based off other pieces of information; I have no idea how to write a SubQuery computing a value based off another value in the same select statement.
If anybody understands what I'm explaining, is there an easier way to go about this? I'm probably making this much more complicated than it needs to be, aren't I?
Thank you very much for any assistance!
em
-
Good luck and congrats on the job.
Typically in this case I've moved data into some staging table to hold it there. Then I'd do an update, joining the staging table to the final table. Then I'd delete the matches from the staging table and insert the rest.
For example:
update final_table
set col1 = col1 + staging.col1
where final_table.pk = staging.pk
update staging
where final_table.pk = staging.pk
insert final_table
select staging.xx, staging.yy, staging.col1, etc.
This make sense?
You don't have to have a staging table. You can just insert the stuff that doesn't match but writing an outer join or a subquery.
-
OMG! You're a genius! That worked perfectly! Thank you soo much.
em
-
Hi Emma,
that's a nice name you have :-). Do you mind if I ask where it came from? Somehow can't imagine why anyone would choose "raw material" as a name
Ar cienu
Vladan
- It's Latvian. I can never remember what it means, but I always tell everybody "sewage: I don't even want to know what my ancestors did for a living."
em
-
It isn't as bad as sewage 🙂 it is rather the other end of production chain - it means raw material, or simply a substance, that is used as starting point in production of something. Well, it still doesn't say what your ancestors did for living... but they could have been interested in chemical research/production or alchemy. Not bad, huh?
Although I know lots of Latvians (my wife is one of them), I've never met anyone with such a name, so I supposed it is a name you chose for some reason to use on these forums. I had no idea that it could be your real name. Actually, it's not a big surprise either - there are much funnier names in Czech (that's what I am), like Skocdopole (Jumponthefield) or Nesnidal (Didnoteatbreakfast).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply