Query from a dynamic list of tables


I have a need to select two columns from a list of views that may change over time, but the
view names all have the same 5 characters - FType.  Below is the code I'm trying to use, but
I get an error stating that the @varTN variable needs to be declared.  Can someone provide
a few hints on what I need to change to make this work - or perhaps suggest an alternate approach?

  FID int ,
  IIN int
DECLARE @varTN varchar(50);
DECLARE TestCur CURSOR FOR select name from sys.Views where name like 'FType%';
OPEN TestCur
    INSERT INTO @IDTable(FID,IIN) Select ID_A,ID_B from @varTN

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongSoftware Team Lead, ex-Business Systems Analyst , ex-Senior Application EngineerCommented:
I guess it's wrong for statement >>

INSERT INTO @IDTable(FID,IIN) Select ID_A,ID_B from @varTN

try replace it with:

INSERT INTO @IDTable(FID,IIN) values (@varTN, @varTN);
Ryan ChongSoftware Team Lead, ex-Business Systems Analyst , ex-Senior Application EngineerCommented:
but you seems grabbed chars, hence you may redesign the variable table's fields from int to char?
cbrydonAuthor Commented:
Thanks for the suggestions Ryan,  but @varTN should contain the name of the view that I want to get data from.
I want to get the fields ID_A and ID_B from whatever view is stored in the @varTN variable and then place
the value of fields ID_A and ID_B into @IDTable.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

cbrydonAuthor Commented:
Its true the issue is with the following line...
INSERT INTO @IDTable(FID,IIN) Select ID_A,ID_B from @varTN

The problem is that @varTN in this line is seen as a table variable, but I need the value of that variable to be used.
Hope someone has other suggestions how to accomplish this.

Ryan ChongSoftware Team Lead, ex-Business Systems Analyst , ex-Senior Application EngineerCommented:
i don't quite get the idea...

what info you need to get from sys.Views, and where you want it to be inserted to?

and why you need cursor in this case?
cbrydonAuthor Commented:
There is a series of views in the database which may increase or decrease in number over time.  Each one of those views has two fields I need combined into one view or table.  So I am selecting all views from sys.views that start with 'FType'.

I need the cursor to loop through each of the views that start with FType and retrieve the values from the ID_A and ID_B fields.
I then need the values from the ID_A and ID_B fields inserted into another table.  The result I am looking for would be similar
to using a bunch of union statements similar to the following...

select ID_A,ID_B from Ftype_One
select ID_A,ID_B from Ftype_Two
select ID_A,ID_B from Ftype_Three

The problem is that I won't know how many views there are so can't manually create a bunch of UNION statements.

Does this help to explain the problem?
cbrydonAuthor Commented:
I managed to find a solution - listed here... https://dba.stackexchange.com/questions/149335/create-a-view-that-dynamically-unions-new-tables.

DECLARE @TableName nvarchar(400)
DECLARE @DynSQL nvarchar(MAX)
Set @DynSQL = ''

    select name
    from sys.views
    where name like 'FType%'

OPEN cursor1

FETCH NEXT FROM cursor1 INTO @TableName

    -- Add the select code.
    Set @DynSQL = @DynSQL + 'Select ID_A,ID_B from ' + @TableName
    FETCH NEXT FROM cursor1
    INTO @TableName

    -- If the loop continues, add the UNION ALL statement.
    If @@FETCH_STATUS = 0
        Set @DynSQL = @DynSQL + ' UNION ALL '


CLOSE cursor1


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cbrydonAuthor Commented:
I found a solution on another website.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.