April 24, 2012 at 8:16 am
I have two tables. One is my current Audit table for my ERP system and another is my archived Audit table for my ERP system. Here is a sample:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#AuditTable','U') IS NOT NULL DROP TABLE #AuditTable
IF OBJECT_ID('TempDB..#AuditArchive','U') IS NOT NULL DROP TABLE #AuditArchive
GO
--===== Create the test tables and insert test data
CREATE TABLE #AuditTable (RecordNum INT,
CreateDate DateTime,
AuditRecord NVARCHAR(20))
INSERT INTO #AuditTable (RecordNum, CreateDate, AuditRecord)
SELECT 6, '04/01/2012 08:36:45.000', 'Add Record 35' UNION ALL
SELECT 7, '04/02/2012 09:01:01.000', 'Del Record 35' UNION ALL
SELECT 8, '04/03/2012 07:54:33.000', 'Upd Record 22' UNION ALL
SELECT 9, '04/04/2012 13:03:54.000', 'Del Record 31' UNION ALL
SELECT 10, '04/05/2012 18:25:19.000', 'Upd Record 14'
CREATE TABLE #AuditArchive (RecordNum INT,
CreateDate DateTime,
AuditRecord NVARCHAR(20))
INSERT INTO #AuditArchive (RecordNum, CreateDate, AuditRecord)
SELECT 1, '01/10/2012 12:01:23.000', 'Add Record 10' UNION ALL
SELECT 2, '02/14/2012 06:36:48.000', 'Del Record 9' UNION ALL
SELECT 3, '02/29/2012 19:45:22.000', 'Add Record 11' UNION ALL
SELECT 4, '03/17/2012 14:20:23.000', 'Upd Record 13' UNION ALL
SELECT 5, '03/23/2012 15:33:31.000', 'Del Record 17'
--==== SELECT the records
SELECT RecordNum, CreateDate, AuditRecord FROM #AuditTable
UNION ALL
SELECT RecordNum, CreateDate, AuditRecord FROM #AuditArchive
ORDER BY RecordNum
I am trying to create code that will pull from both tables, but only if I need data from both tables. In our example, if I only need data for April I just want to pull from AuditTable. However, if I want data from January through April, I need to pull from both tables. Ideally, I would like to be able to pull from just AuditArchive if I need data older than April.
I know I can create a series of IF statements and include the complete code for each, but for my real life tables I would end up with hundreds of lines of code. If there any way I can do this with much less code?
If you need any extra information, please let me know.
Thanks,
Steve
April 24, 2012 at 8:26 am
this looks like an ideal candidate for redesigning the tables into a single and using range partitioning for each month
MVDBA
April 24, 2012 at 8:26 am
Maybe you should create a total of 3 procs. One is the main "driver" proc which has your conditional check(s).
create procA
if (somecondition)
exec procB
else
exec procC
---------------------
create procB
get data from #AuditTable
---------------------
create procC
get data from #AuditArchive
This gives you the advantage of decent execution plans for the different queries while still providing a single proc to call.
Not sure if that is what you are looking for but based on your description that sounds like a good fit.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 24, 2012 at 8:27 am
michael vessey (4/24/2012)
this looks like an ideal candidate for redesigning the tables into a single and using range partitioning for each month
+1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 24, 2012 at 8:44 am
Sean, I like your suggestion for my situation. I like the other one, but the original AuditTable is part of a proprietary ERP system, and if I try to make modifications to it I will probably break it, not to mention the fact that our ERP supplier would not be very happy with us modifying their stuff.
Thank you all for your input.
Steve
April 24, 2012 at 8:48 am
sdownen05 (4/24/2012)
Sean, I like your suggestion for my situation. I like the other one, but the original AuditTable is part of a proprietary ERP system, and if I try to make modifications to it I will probably break it, not to mention the fact that our ERP supplier would not be very happy with us modifying their stuff.Thank you all for your input.
Steve
I had thought about partitioning too but didn't since you said this was an ERP so I assumed it was third party. It is amazing how often those of us in the field come up with far better solutions than the vendors come up with. Of course the stuff they come up with is so often so crappy it is hard to not to find something better.
Hope the multi proc idea works for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 24, 2012 at 8:59 am
I agree with you. The code they use in most cases is definitely like using a sledge hammer to insert a thumb tack.
April 24, 2012 at 8:59 am
...
It is amazing how often those of us in the field come up with far better solutions than the vendors come up with. Of course the stuff they come up with is so often so crappy it is hard to not to find something better.
...
Successful vendor is not the one who employs the best IT guys, but the one who managed to have the best marketing agents, and, even more important, best sales persons...
That is the life!:-D
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply