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