We help IT Professionals succeed at work.

SQL Server 2008 R2 - Creating a random dynamic Table View

722 Views
Last Modified: 2016-02-10
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
Comment
Watch Question

ste5anSenior Developer
CERTIFIED EXPERT

Commented:
You should consider using a partitioned table.

What do you exactly want as result?

Author

Commented:
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

Author

Commented:
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
Harish VargheseProject Leader

Commented:
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
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.
Senior Developer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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
Harish VargheseProject Leader

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

Author

Commented:
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

Author

Commented:
Great thanks
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.