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?
 
cbrydonConnect With a Mentor Author 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
 
Ryan ChongCommented:
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 ChongCommented:
but you seems grabbed chars, hence you may redesign the variable table's fields from int to char?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
 
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 ChongCommented:
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 found a solution on another website.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.