Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Aug 20, 2012 06:04 PM by b471code3
Aug 18, 2012 04:31 PM|LINK
I need make a query for 3 tables, but I want the result in one column, example:
Select Id from Table%
Where id exist in 3 tables: Table1, table2 and tbl3. In my real problem i have more 1000 tables
Aug 19, 2012 04:36 AM|LINK
You could generate your SQL dynamically in a loop and just use UNION
Aug 19, 2012 06:09 AM|LINK
The problem con this code, is that are many tables and the name of tables are distinct. Example: TablenMed, TablenMedTrans, TablenMedRigid, etc. The loop no apply. Thanks
Aug 19, 2012 02:23 PM|LINK
Doesn't sound like a problem, sounds like an opportunity to have a lookup table that has all the table names you need to query and you can then create a look around the results of those table names create your UNION query like I showed before.
TableName table could have columns: TableID PD int, TblName nvarchar(50), CreateDate datetime, IsActive bit (I didn't put the TblName field as a PK because you couldn't change it without a lot of extra work)
Then your simple query would be: SELECT UNIQUE TblName FROM TableName WHERE IsActive = 1. Then loop through the results of that query and create your UNION statement OR just create the query, run it, and merge the results into your existing datatable in
Either way, no problem, sounds like it might be a processing hog though.
Aug 20, 2012 05:54 PM|LINK
It's a bit messy, but you could also use something like this:
Declare @MyQuery varchar(max);
WITH Cte AS (
SELECT Cast('' AS varchar(max)) Name, Cast(0 as int) RowNum
SELECT cte.name + ' UNION ALL SELECT Id FROM ' + a.Name, a.RowNum
SELECT Cast(Tables.Name AS varchar(max)) Name, Cast(Row_Number() OVER (ORDER BY Tables.Name) as int) RowNum
WHERE object_Id IN (
WHERE name = 'Id'
AND Tables.Name LIKE 'Table%'
INNER JOIN Cte
ON a.RowNum = Cte.RowNum + 1
SELECT Substring(Name, CharIndex('SELECT', Name), 9999999)
WHERE Cte.RowNum = (SELECT Max(RowNum) FROM Cte);
Aug 20, 2012 06:04 PM|LINK
I was thinking of that as well Matts...only downfall is getting the table names. If there are 1000+ tables my guess is there probably isn't a determining factor to tell which tables the OP needs to get. Maybe if there was a naming convention that could
be queryable like Products.ProductDesc, Products.Product, Product.ProductImages, etc. you could search for all Products.% schema. Looking for a column name of "ID" could return unexpected results, not saying it couldn't work, just probably need additional
verification you are getting the correct tables.
Or if there were a property you could set on the table and query that but then again I don't know if that can even be done.