Query from a dynamic list of tables

Hello,

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?

DECLARE @IDTable TABLE
(
  FID int ,
  IIN int
)
DECLARE @varTN varchar(50);
DECLARE TestCur CURSOR FOR select name from sys.Views where name like 'FType%';
OPEN TestCur
FETCH NEXT FROM TestCur INTO @varTN;
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO @IDTable(FID,IIN) Select ID_A,ID_B from @varTN
    FETCH NEXT FROM TestCur INTO @varTN;
END
SELECT * FROM @IDTable

Thanks,
Carl
cbrydonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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 ChongBusiness 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);
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
but you seems grabbed chars, hence you may redesign the variable table's fields from int to char?
0
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.
0
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.

Thanks.
0
Ryan ChongBusiness 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?
0
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
UNION
select ID_A,ID_B from Ftype_Two
UNION
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?
0
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 = ''

DECLARE cursor1 CURSOR FOR
    select name
    from sys.views
    where name like 'FType%'

OPEN cursor1

FETCH NEXT FROM cursor1 INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 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
    BEGIN
        Set @DynSQL = @DynSQL + ' UNION ALL '
    END

END

CLOSE cursor1
DEALLOCATE cursor1

EXEC(@DynSQL)
0

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.
0
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.