asked on

Splicing Table name with variable

Hi,

Looking to splice table name with another data pull

One table I have
table test
ID NO
123 55
124 57

select * from XXNOXX a, test b
where a.id = b.id (Matched only on 123)
in other words the actual query would be
select * from XX55XX a, test b
where a.id = b.id

Eventually want to union the queries

ie
select * from XX55XX a, test b
where a.id = b.id
union
select * from XX57XX a, test b
where a.id = b.id

Tables XX55XX and XX57XX exist of course.
Any help would be much appreciate.

Cheers,
Paul

Lee Wadwell

how may different tables do you have?

other than generating dynamic SQL on the fly and executing it, another approach may be to make a VIEW,
e,g,
CREATE VIEW XXNOXX AS
SELECT '55' as no, a.* FROM XX55XX a
UNION ALL
SELECT '57' as no, a.* FROM XX57XX a;

and then join to the view, e.g.
select * from XXNOXX a, test b
where a.id = b.id
AND a.no = b.no

Franck Pachot

Hi,
I don't know if you can change the design, and you have Entreprise Edition, but your table looks like a godd candidate for partitioning: same logical structure, different physical storage.
Regards,
Franck.

Göran Andersson

If the tables have the same layout and only differs by name, they shouldn't be separate tables. The data should be in the tables, not part of the table name. If possible, make a single table out of them, with one added field that contains that number from the table name. That way you can easily query the table without creating the SQL dynamically.

If you can't change the database design, you have to create the query dynaimcally.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

B_rider

ASKER

Great thanks