Avatar of Amour22015
Amour22015
 asked on

SQL Server 2008 R2 - Creating a random dynamic Table View

Hi experts and thanks for any help,

I would like to create a random Table View based on parameters passed.

I am new to all this.

I have 3 tables * etc....

CLAIM_2014_01
CLAIM_FACILITY_DETAIL_2014_01
CLAIM_ITEM_2014_01

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

Avatar of undefined
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.
ASKER CERTIFIED SOLUTION
ste5an

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
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.
ask a question
Amour22015

ASKER
What I am doing now:

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'

This is a PURGE process that I am developing..

Thanks
CreateClaimPurgeView.docx
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Harish Varghese

So, have you achieved what you wanted? Or still need to do something more?
Amour22015

ASKER
I have not been able to test all this yet, looks like tomorrow.

I have to put all the joins into the SP and test

Thanks
Amour22015

ASKER
Great thanks
Your help has saved me hundreds of hours of internet surfing.
fblack61