tables all ARE NOT the same structure - FACILITY does not have File_Id
And yes there is a group for each month So there could be (12 * 3 = 36) Tables, depending on how many times the purge is run could even be more or less.
The View name = CLAIM_PURGE
I pass a parameter from SSIS
TBL_NMS
To:
The new SP I am going to create, this is the reason for this post?
This parameter is based on a SQLStatement that checks for matching File_Ids
Where a condition causes the parameters. For this post there is only ONE parameter "TBL_NMS".
I have seen something close to what I am looking for (note this is just an example and this post is going to be even more dynamic):
@TBLNM varchar(40),
SELECT @SQL1 = 'CREATE VIEW dbo.CLAIM_PURGE AS' + CHAR(10)
+ STUFF( -- Gets rid of first UNION ALL
( --=== Finds the correct table names and concatenates the
-- the necessary syntax for each table name to build
-- view using a "blank" untagged XML Path
SELECT 'UNION ALL SELECT '
*
FROM @TBLNM
--===== Conditionally drop the view
IF OBJECT_ID('dbo.CLAIM_PURGE ','V') IS NOT NULL
DROP VIEW dbo.CLAIM_PURGE
--===== Create the view using the generated syntax
EXEC (@SQL1)
--===== Not required, but show the syntax we just executed
PRINT @SQL1
Please help and thanks
Microsoft SQL ServerSSISSQL
Last Comment
Amour22015
8/22/2022 - Mon
ste5an
You should consider using a partitioned table.
What do you exactly want as result?
Amour22015
ASKER
There is a select statement From SSIS SQLStatement:
SELECT DISTINCT
FILE_ID,
TBL_NM
FROM DATABASE_TABLES
Where
TBL_STAT = 'purge_elig' And
TBL_Typ = 'Claim' And
TBL_NM not in (Select Distinct TBL_NM From DATABASE_TABLES Where TBL_TYP = 'CLAIM' and TBL_STAT = 'ACTIVE')
Go
SELECT DISTINCT
FILE_ID,
TBL_NM
FROM DATABASE_TABLES
Where
TBL_STAT = 'purge_elig' And
TBL_Typ = 'CLAIM_ITEM' And
TBL_NM not in (Select Distinct TBL_NM From DATABASE_TABLES Where TBL_TYP = 'CLAIM_ITEM' and TBL_STAT = 'ACTIVE')
Go
SELECT DISTINCT
FILE_ID,
TBL_NM
FROM DATABASE_TABLES
Where
TBL_STAT = 'purge_elig' And
TBL_Typ = 'CLAIM_FACILITY_DETAIL' And
TBL_NM not in (Select Distinct TBL_NM From DATABASE_TABLES Where TBL_TYP = 'CLAIM_FACILITY_DETAIL' and TBL_STAT = 'ACTIVE')
For this post what this does is sends a parameter "TBL_NM"
"TBL_NM" is really TBLNMS
and can have multible tables
I am trying to combine all tables into one table so that in Stored Procedure it can be:
FROM CLAIM_PURGE
CLAIM_PURGE = Multible Table(s)
It does not have to be a Table View, I just thought that Table View will let you have multible table(s) into one table.
Maybe I can do this with Left Joins in the SP?
Thanks
Amour22015
ASKER
Hi,
You See:
@TBLNM (Table Name) could = "Claim_20014_01" Or "Claim_20014_02" Or "Claim_20014_03" Or "Claim_20014_04" Or "Claim_ITEM_20014_01" OR "Claim_ITEM_20014_02" Or "Claim_ITEM_20014_03" Or "Claim_ITEM_20014_04" Or "Claim_ITEM_20014_01" Or "CLAIM_FACILITY_DETAIL_2014_01" Or "CLAIM_FACILITY_DETAIL_2014_02" Or "CLAIM_FACILITY_DETAIL_2014_02" Or "CLAIM_FACILITY_DETAIL_2014_03" Or "CLAIM_FACILITY_DETAIL_2014_04"
Then multiple all that by 12
Or Etc, etc...etc..
There are 3 sets of Tables * (times) an endless number.....
Looks like I am going to have to set up 3 Table Views and then join them?
Please help and thanks
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Harish Varghese
Hello,
Looks like your requirement is still not clear. Can you explain youre requirement in detail?
1. You have 36 (or even 100) tables.
2. Are you going to pass a string of these table names ("Table1, Table2, Table3") to a stored proc?
3. What should that stored procedure do?
-Harish
ProjectChampion
Hi Amour22015,
I see what you're trying to achieve. For multiple reasons including security, performance, maintainability and simply better organisation I suggest:
1. Create a few stored procs to create the basic objects that you need, i.e. one store proc per type of object with each store proc requiring only one parameter for the name. When you've completed developing and testing all stored procs move to the next step. As a shortcut you can create one instance of each object and then let management Studio generate the "Drop and Create Script" for that object, which you can then modify and convert to a stored proc giving it a parameter for the object name.
2.Inside the package remove all the dynamic scripts and replace them with calls to the stored procs you've created in the previous step simply passing one parameter for the name.
Not only it'll simplify developing and debugging your package, but also enhance the readability and maintainability of the package and more importantly eliminate all the potential risks (accidental or malicious) associated with using dynamic SQL scripts.
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
I created 3 Stored Procedures that take care of the Table Views
'CLAIM',
'CLAIM_ITEM',
'CLAIM_FACILITY_DETAIL'
Within each I do a UNION ALL to take care of all the 2014_01 - 2014_12
Where:
TBL_TYP = 'CLAIM'
TBL_TYP = 'CLAIM_ITEM'
TBL_TYP ='CLAIM_FACILITY_DETAIL'
See Attachment...
This is based off "Database_Table" Table
Then within the Main SP I just Left Join based on CLM_KY
That way I don't have to pass parameters
ste5an
is very close to what I have come up with. Only instead of: TBL_STAT = 'ACTIVE'
it would be TBL_STAT = 'PURGE_ELIG'
What do you exactly want as result?