Joining Many Tables in Loop

Hello,

I am very new to TSQL however I do have a little programming in my background. I am trying to run a query to select several datafields from about 45 tables within a database. I have written the below code to loop through all of the tables and extract the information however I need to find some way to join the results using PCTimeStamp as the key. Any help anyone can give me is greatly appreciated!

while (@.Table_Number<18240) /*Loop to read data from all Turbine all Tables*/
BEGIN
if (@.Table_Number=17990 or @.Table_Number=18234) /*ERROR AVOIDANCE for Non-Sequential Turbine #'s*/
BEGIN
set @.Table_Number=@.Table_Number+1;
END;
set @.Table_Insert = cast (@.Table_Number as NVARCHAR); /*Converts Table Number to Format usable in String below*/

set @.SQLString = N'SELECT Id, Status_Avg_NacellePos, Status_Avg_BladeAPos, Status_Avg_BladeBPos, Status_Avg_BladecPos, Grid_Avg_ActivePower, Status_Avg_Windspeed1, Status_Avg_Windspeed2, Status_Avg_WindDirection1, Status_Avg_WindDirection1 FROM T_'+@.Table_Insert+'_10MINDATA where PCTimeStamp=(select max(PCTimeStamp) from T_'+@.Table_Insert+'_10MINDATA)';

set @.Table_Number = @.Table_Number+1; /*Increment @.Table_Number to control Loop*/
if (@.Table_Number <18006 or @.Table_Number>18235) /*ERROR AVOIDANCE for Non-Sequential Turbine #'s*/
BEGIN
EXEC(@.SQLString); /*Execute String to retrieve data from table*/
END;
END;

I tried to format that in this text box as best I could but I apologize for the result. In summary Table_Number is used as the key to loop through the sequentially numbered tables (with some numbers missing) and extract the required fields from the most recent entry (PCTimeStamp=max).

Other notes: the code as written outputs all of the required data with no error messages but the data column titles are displayed i.e:

id windspeed direction time

1 12 000 1200

id windspeed direction time

2 12 010 1200

...................

looking for

id windspeed direction time

1 12 000 1200

2 12 010 1200

Thanks again

Consider using a temporary table. You can create the table outside the loop and change your dynamic query into an Insert...Select. You will return the combined results by selecting from the temporary table.|||

Hello,

You do not need to use a temp table for this.

You can use the UNION operator as such:

SELECT TOP 1 * FROM Table1 order by PCTimeStamp DESC

UNION ALL

SELECT TOP 1 * FROM Table2 order by PCTimeStamp DESC

UNION ALL

SELECT TOP 1 * FROM Table3 order by PCTimeStamp DESC

Cheers,

Rob

|||

Thanks to everyone for the help, I looked into the temporary table and here is my revised code:

declare @.SQLString NVARCHAR(500); /*Executable String to Run Query on Multiple Table in Looping Feature*/

declare @.WindLogics_Data TABLE(Status_Avg_NacellePos Int, Status_Avg_BladeAPos SMALLMONEY, Status_Avg_BladeBPos SMALLMONEY, Status_Avg_BladeCPos SMALLMONEY, Grid_Avg_ActivePower SMALLMONEY, Status_Avg_Windspeed1 SMALLMONEY,Status_Avg_Windspeed2 SMALLMONEY, Status_Avg_WindDirection1 SMALLMONEY, Status_Avg_WindDirection2 SMALLMONEY);

Set @.Table_Number = 17965; /*Initialize Value of Turbine Table to 17965*/

/*Practice Insert*/

Insert INTO @.WindLogics_Data (Status_Avg_NacellePos, Status_Avg_BladeAPos, Status_Avg_BladeBPos, Status_Avg_BladeCPos, Grid_Avg_ActivePower, Status_Avg_Windspeed1, Status_Avg_Windspeed2, Status_Avg_WindDirection1, Status_Avg_WindDirection2) SELECT Status_Avg_NacellePos, Status_Avg_BladeAPos,Status_Avg_BladeBPos,Status_Avg_BladecPos,Grid_Avg_ActivePower,Status_Avg_Windspeed1,Status_Avg_Windspeed2, Status_Avg_WindDirection1,Status_Avg_WindDirection2 FROM T_17965_10MINDATA where PCTimeStamp=(select max(PCTimeStamp) from T_17965_10MINDATA);
select * from @.WindLogics_Data

while (@.Table_Number<18240) /*Loop to read data from all Turbine all Tables*/
BEGIN
if (@.Table_Number=17990 or @.Table_Number=18234) /*ERROR AVOIDANCE for Non-Sequential Turbine #'s*/
BEGIN
set @.Table_Number=@.Table_Number+1;
END;
set @.Table_Insert = cast (@.Table_Number as NVARCHAR); /*Converts Table Number to Format usable in String below*/
set @.SQLString = N'Insert INTO @.WindLogics_Data (Status_Avg_NacellePos, Status_Avg_BladeAPos, Status_Avg_BladeBPos, Status_Avg_BladeCPos, Grid_Avg_ActivePower, Status_Avg_Windspeed1, Status_Avg_Windspeed2, Status_Avg_WindDirection1, Status_Avg_WindDirection2) SELECT Status_Avg_NacellePos, Status_Avg_BladeAPos,Status_Avg_BladeBPos,Status_Avg_BladecPos,Grid_Avg_ActivePower,Status_Avg_Windspeed1,Status_Avg_Windspeed2, Status_Avg_WindDirection1,Status_Avg_WindDirection2 FROM T_17965_10MINDATA where PCTimeStamp=(select max(PCTimeStamp) FROM T_'+@.Table_Insert+'_10MINDATA where PCTimeStamp=(select max(PCTimeStamp) from T_'+@.Table_Insert+'_10MINDATA)';
set @.Table_Number = @.Table_Number+1; /*Increment @.Table_Number to control Loop*/
if (@.Table_Number <18006 or @.Table_Number>18235) /*ERROR AVOIDANCE for Non-Sequential Turbine #'s*/
BEGIN
EXEC(@.SQLString); /*Execute String to retrieve data from table*/
END;
END;

**************************************

Practice insert works perfectly so I know structure of @.WindLogics_Data is correct however SQL will not recognize the database name in the SQLString. Says "Must declare @.Wind_Logics", is there a stipulation against table variable name in a string running through the exec command?

Thank you so much for the help, table var was a lifesaver!

|||

I don't see the variable that you mention in the code you posted. Yes, there are restrictions as to what you can do with dynamic SQL. You cannot access variables declared outside the scope of the dynamic SQL for example. Also, using a table variable may not be the best thing to do depending on the number of rows in each table. Performance will worsen as you populate more rows into table variables. It is more suited for smaller results in the range of 1000s of rows or so. So using a temporary table will provide better performance.

In any case, it seems like you should use table partitioning in SQL Server 2005 or partitioned views in SQL Server 2000/70. With either of the approach, you don't need to write such complicated code. You can achieve what you are doing by writing simple SELECT statements. See below links for more information on partitioning. And BOL also has topics that cover this feature.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5partition.asp

http://www.microsoft.com/technet/prodtechnol/sql/2005/spdw.mspx

http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part5/c1861.mspx

|||

Umachandar,

Thank you for replying I should have provided more background, using a temporary table seems to get me closer to working and for informational purposes the results will only be 10columns with 45 rows so in this case efficiency is not a HUGE concern (you probably disagree!) here is the code sample with highlight where problem is. Now I get error message as

Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'select'. (repeated over and over for loop)

Again my test insert into the temporary table is operational so once the above error is resolved I think I am good to go.

I looked into the partitioning and to be honest it would require me a while to absorb the structure I think, I was hoping there would be a quicker fix for the above error.

****************************************************************************************************************************************************

use Windman /*Defines database to be used for commands*/

declare @.Table_Number INT; /*Number of Table in Numerical Format for Looping*/
declare @.Table_Insert NVARCHAR(5); /*Number of Table in Char for String Insertion*/
declare @.SQLString NVARCHAR(500); /*Executable String to Run Query on Multiple Table in Looping Feature*/
create table #WindLogics_Data (Status_Avg_NacellePos Int, Status_Avg_BladeAPos SMALLMONEY, Status_Avg_BladeBPos SMALLMONEY, Status_Avg_BladeCPos SMALLMONEY, Grid_Avg_ActivePower SMALLMONEY, Status_Avg_Windspeed1 SMALLMONEY,Status_Avg_Windspeed2 SMALLMONEY, Status_Avg_WindDirection1 SMALLMONEY, Status_Avg_WindDirection2 SMALLMONEY);

Set @.Table_Number = 17965; /*Initialize Value of Turbine Table to 17965*/

Insert INTO #WindLogics_Data (Status_Avg_NacellePos, Status_Avg_BladeAPos, Status_Avg_BladeBPos, Status_Avg_BladeCPos, Grid_Avg_ActivePower, Status_Avg_Windspeed1, Status_Avg_Windspeed2, Status_Avg_WindDirection1, Status_Avg_WindDirection2) SELECT Status_Avg_NacellePos, Status_Avg_BladeAPos,Status_Avg_BladeBPos,Status_Avg_BladecPos,Grid_Avg_ActivePower,Status_Avg_Windspeed1,Status_Avg_Windspeed2, Status_Avg_WindDirection1,Status_Avg_WindDirection2 FROM T_17965_10MINDATA where PCTimeStamp=(select max(PCTimeStamp) from T_17965_10MINDATA);
select * from #WindLogics_Data

while (@.Table_Number<18240) /*Loop to read data from all Turbine all Tables*/
BEGIN
if (@.Table_Number=17990 or @.Table_Number=18234) /*ERROR AVOIDANCE for Non-Sequential Turbine #'s*/
BEGIN
set @.Table_Number=@.Table_Number+1;
END;
set @.Table_Insert = cast (@.Table_Number as NVARCHAR); /*Converts Table Number to Format usable in String below*/
set @.SQLString = N'INSERT INTO #WindLogics_Data (Status_Avg_NacellePos, Status_Avg_BladeAPos, Status_Avg_BladeBPos, Status_Avg_BladeCPos, Grid_Avg_ActivePower, Status_Avg_Windspeed1, Status_Avg_Windspeed2, Status_Avg_WindDirection1, Status_Avg_WindDirection2) SELECT Status_Avg_NacellePos, Status_Avg_BladeAPos,Status_Avg_BladeBPos,Status_Avg_BladecPos,Grid_Avg_ActivePower,Status_Avg_Windspeed1,Status_Avg_Windspeed2, Status_Avg_WindDirection1,Status_Avg_WindDirection2 FROM T_'+@.Table_Insert+'10MINDATA where PCTimeStamp=(select max(PCTimeStamp) from T_'+@.Table_Insert+'_10MINDATA)';

if (@.Table_Number <18006 or @.Table_Number>18235) /*ERROR AVOIDANCE for Non-Sequential Turbine #'s*/
BEGIN
EXEC(@.SQLString); /*Execute String to retrieve data from table*/
END;

set @.Table_Number = @.Table_Number+1; /*Increment @.Table_Number to control Loop*/
END;

Drop Table #WindLogics_Data

/*END OF QUERY******************************************************************************/

|||My goodness I am so sorry, I realized what it was, I had only made my variable for the string 400 characters and my command had exceeded that, I apologize and thanks for all of the help!