Loop through different tables with variable names

Question

I know what you'll say "that is one terrible design". I know, I have been coping with it for the last couple of months (again, it was not my design), but instead of scalding me, bear with me and help me with the answer, will you? Thanks!

I have a table that has a list of the names of some other tables:

Table_that_store_the_others_tables_names
------------------
| LIST_OF_TABLES |
------------------
| table_arw      |
------------------
| table_bhj      |
------------------
| table_cde      |
------------------

It is important to know that the tables have no common name, but do have the exact same structure:

each_one_of_those_dumb_tables, e.g. table_arw
--------------------------
| field1     | field2    |
--------------------------
| something  | something |
--------------------------
| something  | something |
--------------------------

So, what I need to do is iterate through such tables and fetch the data they have (I'm pretty sure it is done by procedural language but I don't know much about it) in this some sort of way:

loop through each value found in "Table_that_store_the_others_tables_names":
    select field1, field2 from "whatever_value_was_found"
    insert into "this_new_not_stupid_table_that_will_now_have_the_info"
end loop

So, I think that's about it. Thank you so much!

You can probably find another similar question with an answer on SO. — manderson, Mar 02 '17 at 15:28
You don't need a loop for this at all. You can simply use some dynamic sql. — Sean Lange, Mar 02 '17 at 15:29
The tag `sql-server` contradicts the tag `plsql`. There is no PL/SQL in Microsoft SQL Server. So you either use Microsoft SQL Server, then it would need to be tagged with `tsql` (and `plsql` needs to be removed) or you use Oracle, then you need to remove the tag `sql-server` — , Mar 02 '17 at 15:34
And now it seems it was neither sql server nor oracle....it is mysql. — Sean Lange, Mar 02 '17 at 20:21

score 2 · Answer 1 · answered Mar 02 '17 at 15:42

2

Here is how you can do this with some dynamic sql. There is no need for cursors or loops here. Something like this should point you in a set based direction for this. It is much faster than using a cursor and requires a lot less code too. :)

declare @SQL nvarchar(max) = N'insert into this_new_not_stupid_table_that_will_now_have_the_info(field1, field2) '

select @SQL = @SQL + N'select field1, field2 
from ' + quotename(t.LIST_OF_TABLES) + ' UNION ALL '
from Table_that_store_the_others_tables_names t

set @SQL = left(@SQL, len(@SQL) - 10)

exec sp_executesql @SQL

answered Mar 02 '17 at 15:42

Sean Lange

33,028
3
25
40

Hello. Excuse me for my ineptitude, but this code also does not work for me. Is it SQLSERVER exclusive?, because it turns out I have to do it in mysql. – JD Gamboa Mar 06 '17 at 20:56
Yes this won't work with mysql. A good example of why it is so important to tag correctly. :) I can't spell mysql let alone write anything like this with it. Good luck. – Sean Lange Mar 06 '17 at 20:59
Yeah, I thought I had to do it on sqlserver, it turned out I didn't. – JD Gamboa Mar 06 '17 at 21:06
Also, up until the previous week, I thought all procedural language was called plsql, because you know, the "pl" in it. Now I know better, so, there's also that. But thanks anyway for trying. – JD Gamboa Mar 06 '17 at 21:22

John Cappelletti · Answer 2 · 2017-03-02T20:28:50.230

2

Declare @SQL varchar(max)='Insert Into YourNewTable (Field1,Field2) >>>'
Select @SQL = Replace(@SQL+'Union All Select Field1,Field2 From '+QuoteName(YourFieldWithTableNames) +' where 1=1 ','>>>Union All ','')
 From  YourTable

Exec(@SQL)

The Generated SQL Would look something like this

Insert Into YourNewTable (Field1,Field2) 
Select Field1,Field2 From [table_arw] where 1=1   << Notice I added a WHERE just in case
Union All 
Select Field1,Field2 From [table_bhj] where 1=1 
Union All 
Select Field1,Field2 From [table_cde] where 1=1

edited Mar 02 '17 at 20:28

answered Mar 02 '17 at 15:47

John Cappelletti

79,615
7
44
66

Ok then, but how do I retrieve the List_of_tables (now confessing that I know very very little of tsql/plsql) – JD Gamboa Mar 02 '17 at 19:19
@JuanDavidGamboa I assumed you had a table called LIST_OF_TABLES which contained the desired table names – John Cappelletti Mar 02 '17 at 19:25
I do, yes, but I mean the one in "QuoteName(LIST_OF_TABLES)", or calling that one method does get all the data there? – JD Gamboa Mar 02 '17 at 20:12
@JuanDavidGamboa I wasn't clear on the table / field name. Change QuoteName(LIST_OF_TABLES) to QuoteName(YourFieldName) and From List_Of_Tables to From YourTableName – John Cappelletti Mar 02 '17 at 20:16
@SqlZim Just a flag for the first Union All rather than stuff() – John Cappelletti Mar 02 '17 at 20:19
Sorry for all these days without any further replay, but I had to focus on something else and come back to this later... and now, back to it. Turns out I have to do this on the mysql source and not in msss destination (I know, I know, sorry) and that code doesn't work in a mysql stored procedure. I tried changing some things to no avail. Yet once again, can you help me? – JD Gamboa Mar 06 '17 at 20:37

score 1 · Accepted Answer · edited May 23 '17 at 12:00

1

Quick search pulled up this StackOverflow post. Basically you will want to set the table names into variables that you can then execute a query on using EXEC.

Hope this helps!

edited May 23 '17 at 12:00

Community

1
1

answered Mar 02 '17 at 15:31

B. Witter

564
6
19

score 1 · Answer 4 · answered Mar 02 '17 at 15:35

Do you mean something like this?

DECLARE @dbID INT
DECLARE @dbName VARCHAR(50)
DECLARE @tblID INT
DECLARE @DynamicSQL VARCHAR(MAX)
DECLARE @tblName VARCHAR(100)

DECLARE @DB TABLE(DatabaseID INT,DatabaseName VARCHAR(50))
INSERT INTO @DB
SELECT DatabaseID
     , DatabaseName
FROM Archive..Databases
WHERE ArchiveYN = 1

DECLARE db_cursor CURSOR FOR 
SELECT DatabaseID     
FROM @DB

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @dbID 

WHILE @@FETCH_STATUS = 0   
BEGIN  
      DECLARE @DBTable TABLE(DBTableID INT,DBTableName VARCHAR(100),WeeksToKeep INT,DateColumnName VARCHAR(50))
      INSERT INTO @DBTable
      SELECT DBTableID
            ,DBTableName                
      FROM Archive..DBTables 
      WHERE DatabaseID = @dbID
        AND ArchiveYN = 1
        AND SchemaValidYN = 1

      SET @dbName = (SELECT DatabaseName FROM @DB WHERE DatabaseID = @dbID)

      DECLARE tbl_cursor CURSOR FOR  
      SELECT DBTableID
      FROM @DBTable

      OPEN tbl_cursor   
      FETCH NEXT FROM tbl_cursor INTO @tblID
      WHILE @@FETCH_STATUS = 0   
      BEGIN 

         SELECT @tblName = DBTableName                
         FROM @DBTable
         WHERE DBTableID = @tblID

         DECLARE @conDBName VARCHAR(100) =  @dbName + '_Archive.dbo'

         EXEC [dbo].[DropConstraints] @tblName ,@conDBName       

         IF EXISTS (SELECT * from syscolumns where id = Object_ID(@tblName) and colstat & 1 = 1)
         BEGIN
            SET @DynamicSQL =  'SET IDENTITY_INSERT ' + @dbName + '_Archive.dbo.' + @tblName + ' ON'
            EXEC (@DynamicSQL)
         END

         SET @DynamicSQL =  'INSERT INTO ' + @dbName + '_Archive.dbo.' + @tblName +  ' SELECT * FROM  ' + @dbName + '..' + @tblName
         EXEC (@DynamicSQL)

         SET @DynamicSQL = 'DELETE FROM ' + @dbName + '..' + @tblName
         EXEC (@DynamicSQL)

         IF EXISTS (SELECT * from syscolumns where id = Object_ID(@tblName) and colstat & 1 = 1)
         BEGIN
            SET @DynamicSQL =  'SET IDENTITY_INSERT ' + @dbName + '_Archive.dbo.' + @tblName + ' OFF'
            EXEC (@DynamicSQL)
         END                   

       FETCH NEXT FROM tbl_cursor INTO @tblID   
       END  

       CLOSE tbl_cursor   
       DEALLOCATE tbl_cursor
  FETCH NEXT FROM db_cursor INTO @dbID   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

Loop through different tables with variable names

4 Answers4