I want to iterate over a temp table that contains rows of table names I retrieved from sysobjects with:
select s.name from sysobjects s where s.type='U'
My temp table might have:
#tab_name
myTable1
myTable2
myTable3
...
With those table names I want to select from each of those tables dynamically:
select table_name from @myTable --each row's value
Each value from #tab_name
In other words, from my set of rows with the table names, I have another statement that uses that table name dynamically in a separate select statement. Is this possible to do without a cursor or a second stored procedure? I thought I could do it directly using sysobjects, but couldn't without a temp table.
I have this but I see no way to traverse the results of my select of the temp table:
create table #mytablename (app_name varchar(255))
insert into #mytablename
select s.name from sysobjects s where s.type='U'
declare @end int, @current int
declare @my_app nvarchar(255)
declare @sql nvarchar(max)
set @current=0
select app_name from #mytablename
select @end = @@ROWCOUNT from #mytablename
select @end --so I can see it
while @current < @end --need a cursor with fetch
begin
set @current=@current+1
--no way to traverse the rows one at a time, so it will pick the last row.
select @my_app = app_name from #mytablename
select @sql = 'select trim(substring(left(app_name,len(app_name)-7),10,100)) from ' + @my_app
exec(@sql)
end
It is not relevant per se, but do not be concerned with "app_name" vs "tab_name." In my case, the table is the name of a real app, so it works for what I need. That is why I am manipulating strings in my SQL. The table names contain data I need.
I have read many answers, but I specifically want to know if it is possible to do this without a cursor or second stored procedure.
Edit: The original values are Excel spreadsheet tab names. When I did a bulk import it made each tab a table name. Each of the sheets (now tables) has the same data in it, namely the name of an application that each one of those sheets/tables has statistics on.
My plan is to query all the tables, get that application name and put that application name next to the the correct statistic to identify it.
For example,
Sheet 1 has the app name "My Application" in a row that was imported from a worksheet. Each spreadsheet has this. Therefore, each imported table has this. If I can read each table, then I get get each application name.
If I did a Union, then I have to select from 40+ tables manually, but I wanted to use the table names dynamically.
When the import happened it took the first few rows of the worksheet that had pseudo header information (not real headers that can become column names, just data like the app name, date, time and so on. The statistics themselves have headers but they are a few rows down, and I have to fix that too.
It makes tables and rows look like:
Table1:
Columns: F1, F2, F3...
Rows: appname: my app, null, null...
1/1/2019, null, null
some other metadata, null, null
stat header, null, null
thedata, stat1, stat2...
thedata
thedata
Table2:
Columns: F1, F2, F3...
Rows: appname: app, null, null...
1/1/2018, null, null
some other metadata, null, null
stat header, null, null
thedata, stat1, stat2...
thedata
thedata
...
Table40:
Columns: F1, F2, F3...
Rows: appname: app, null, null...
1/1/2018, null, null
some other metadata, null, null
stat header, null, null
thedata, stat1, stat2...
thedata
thedata
I have no control over the spreadsheet. F1, F2, etc. are given by the import process. Table1, 2, etc. are the names of the worksheet the import gave, and they have spaces, dollar signs, dashes, tick marks, and so on.
It might be a better thing to handle in SSIS.
Edit:
For simplicity here, the end result would be a single table with the app_name in it and the column called "app_name"
New table:
app_name
myapp0
myapp1
myapp2
...
In reality, I will be updating tables with that app_name, doing a lot of deletions and so on, so I do not want to complicate it. If I can see how to use table names dynamically from another tables results (sysobjects), then I can do the rest. I don't know how to do the dynamic table name part, unless a cursor is used.
You could include identity column in your temp table for the alternative way of CURSOR. modified little based on your query as below:
create table #mytablename (app_name varchar(255), count_loop int identity(1,1))
insert into #mytablename
select s.name from sysobjects s where s.type='U'
declare @max int, @current int
declare @my_app nvarchar(255)
declare @sql nvarchar(max)
set @current=1
select @max = MAX(count_loop) from #mytablename
while @current < @max
begin
select @my_app = app_name from #mytablename where count_loop = @current
select @sql = 'select trim(substring(left(app_name,len(app_name)-7),10,100)) from ' + @my_app
exec(@sql)
set @current=@current+1
end