50 Tables(Urgent)
-
HI all I have 50 tables in my database In 50 tables the fields are same(Name,year,date these 3 fields only) Now i want write query to retrive all 50 tables information in 1 QUERY Can any one plz help me THX asdfasdfasdfasdfasdfasdfasdfasdfasdfasdf
-
HI all I have 50 tables in my database In 50 tables the fields are same(Name,year,date these 3 fields only) Now i want write query to retrive all 50 tables information in 1 QUERY Can any one plz help me THX asdfasdfasdfasdfasdfasdfasdfasdfasdfasdf
Will a Union Query[^] do the job? Michael CP Blog [^] Development Blog [^]
-
HI all I have 50 tables in my database In 50 tables the fields are same(Name,year,date these 3 fields only) Now i want write query to retrive all 50 tables information in 1 QUERY Can any one plz help me THX asdfasdfasdfasdfasdfasdfasdfasdfasdfasdf
Hi! You can generate the query you need using the following SQL-Statement:
select 'select [name], [year], [date] from '+table_schema+'.'
+table_name+' union all '
from INFORMATION_SCHEMA.TABLESExecute this statement with result type = text, copy the result to a new query windoe and delete the last "union all". It will give you a union all of all your tables -> your 50 tables combined into one. Hope this helps. Regards, Rainer Rainer Stropek Visit my blog at http://www.cubido.at/rainers
-
Hi! You can generate the query you need using the following SQL-Statement:
select 'select [name], [year], [date] from '+table_schema+'.'
+table_name+' union all '
from INFORMATION_SCHEMA.TABLESExecute this statement with result type = text, copy the result to a new query windoe and delete the last "union all". It will give you a union all of all your tables -> your 50 tables combined into one. Hope this helps. Regards, Rainer Rainer Stropek Visit my blog at http://www.cubido.at/rainers
HI Both of u THX for replying My problum is i have an query to search and display so i given query like this select name from '" & textbox1.text & "' where date = ''.... table name come from textbox which is enter by client. now i want to do is to i have to remove the client textbox. And by date or any thing i have search the names which is in 50 tables 50 table names i have to display PLZ help me THX asdfasdfasdfasdfasdfasdfasdfasdfasdfasdf
-
HI Both of u THX for replying My problum is i have an query to search and display so i given query like this select name from '" & textbox1.text & "' where date = ''.... table name come from textbox which is enter by client. now i want to do is to i have to remove the client textbox. And by date or any thing i have search the names which is in 50 tables 50 table names i have to display PLZ help me THX asdfasdfasdfasdfasdfasdfasdfasdfasdfasdf
Hi! Ok, I think I understand your problem. You have to change my SQL generation query like this:
select 'create view MyHelperView as '
union all
select 'select [name], [year], [date], '
+''''+table_name+''' as tablename from '+table_schema+'.'
+table_name+' union all '
from INFORMATION_SCHEMA.TABLESThis query generates a helper view that you can use in your program. It contains a new colum named "tablename" that you can display. Therefore your query could look like this:
select tablename, name from MyHelperView where date = ...
Hope this helps! Regards, Rainer. Rainer Stropek Visit my blog at http://www.cubido.at/rainers
-
HI Both of u THX for replying My problum is i have an query to search and display so i given query like this select name from '" & textbox1.text & "' where date = ''.... table name come from textbox which is enter by client. now i want to do is to i have to remove the client textbox. And by date or any thing i have search the names which is in 50 tables 50 table names i have to display PLZ help me THX asdfasdfasdfasdfasdfasdfasdfasdfasdfasdf
The solution provided by r.stropek is good. Basically I surprise, why do you maintain 50 tables of same structure?:confused: Can you not combine all the 50 tables into one table (with one additional column as
TableName or Type or Categoryor anything like that)? (r.stropek suggests to do this through a view.) I guess combining tables will solve maintenance problem, and help normalization too!