CODEWITHSUNDEEP
mysqlsql
JD Gamboa
JD Gamboa

Reputation: 382

Loop through different tables with variable names

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!

Upvotes: 2

Views: 1732

Answers (4)

John Cappelletti
John Cappelletti

Reputation: 81930

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

Upvotes: 2

Sean Lange
Sean Lange

Reputation: 33571

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

Upvotes: 2

Snowlockk
Snowlockk

Reputation: 451

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

Upvotes: 1

B. Witter
B. Witter

Reputation: 644

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!

Upvotes: 1

Related Questions