Getting data from Multiple tables

Question

Hi I have Multiple tables in my database nearly 200. I want to get the data from all the tables based on an Order Number. Is it possible?. I need to get all the data related to that order number in all the tables and display in another table. thanks

Bikesh Srivastava · Answer

Its big deal you should use join. otherwise view.

Nigel Fernandes · Answer

Try something like this: declare @sql as varchar ( max ) set @sql = 'select * from table where ordernumber = 12345' ; while(condition) begin set @sql += 'required tables' ; end exec (@sql)

Krishna Kumar · Answer

Hi Nigel Thanks for the reply. Can you please provide me the sample code so that i can understand and try the same.

Nigel Fernandes · Answer

Create the query using a for loop in a string and then use exec (string) dynamically.

Krishna Kumar · Answer

Hi Rajesh and Daniel Thanks for your replies. Is there anyway other than joins to achieve my query. I need to get the data from all the 200 tables and loop through the tables and display them in single table. Thanks

Daniel Jones · Answer

you can use SQL join to get data from multiple tables: http://databases.about.com/od/sqlserver/fl/Retrieving-Data-from-Multiple-Tables-with-SQL-Joins-Inner-Joins.htm https://www.tutorialspoint.com/sql_certificate/get_data_from_multiple_tables.htm

Rajeesh Menoth · Answer

Hi You can retrieve data from multiple table using Sql Joins. Reference : https://www.tutorialspoint.com/sql_certificate/get_data_from_multiple_tables.htm http://www.sql-ex.com/help/select5.php