0

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.

johnny
  • 19,272
  • 52
  • 157
  • 259
  • 1
    a loop is a loop - your artificial cursor is not an improvement if you need to loop over rows. So perhaps the better question is what do you need to do with each row? If you need to build a dynamic sql statement that is specific to each row for whatever reason, you might have little choice. Doing for every table is more than a little suspicious. – SMor Jun 25 '21 at 22:15
  • Ask yourself this, can you construct a static SQL statement which pulls the required data from a static number of tables i.e. say 3. If you can do that, you can therefore do the same thing dynamically for N tables. It looks like you are pulling a computed value for all rows of all tables. So statically you could do this using a `union all` to create a single statement which pulls all the data in one hit. And you could build such a statement using a string concatenation approach such as `string_agg` or the XML equivalent if `string_agg` is not available. – Dale K Jun 25 '21 at 22:31
  • 2
    You really need to provide the high level requirements of what you are trying to achieve though, i.e. sample data and desired results. Only then can anyone confidently tell you if there is a better approach. – Dale K Jun 25 '21 at 22:32
  • 1
    Are you just trying to get all the `app_name` values from all of these tables? As @DaleK has said, you could just build a `union all` query dynamically and then execute it. But needing to do this at all is itself a code smell, why do so many tables have the same column? – Charlieface Jun 27 '21 at 02:15
  • @Charlieface I could but I'd have to type in 40 table names. I wanted to build that statement dynamically. – johnny Jun 28 '21 at 13:57
  • It's fine to build it dynamically, but can you not give us an example of how it would look, say for two or three tables? Are all the columns the same in each table? – Charlieface Jun 28 '21 at 14:14
  • @Charlieface I added it. Sorry. I essentially want to know how to take the table names from sysobject and do a dynamic query using those table names, without a cursor, or if it is possible. If I do select name from sysobjects where type = 'U', I want to take that "name" value and put it in another table select * from [value from the sysobjects table]. – johnny Jun 28 '21 at 16:07
  • When I say how would it look, I meant how would a *query* look with `union all`. I'm really not clear what your end result would be: do you just want a single column result? How do you identify the row with the `appname` (remember that tables have no inherent ordering) – Charlieface Jun 28 '21 at 18:37

1 Answers1

1

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
LONG
  • 4,490
  • 2
  • 17
  • 35