troubleshooting Question

SQL Server 2008 R2 - Creating a random dynamic Table View

Avatar of Amour22015
Amour22015 asked on
Microsoft SQL ServerSSISSQL
10 Comments1 Solution728 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
ste5an
Senior Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros