Iteration or loop trough select result

Transact-SQL
1

Hello
I have no experience about CURSOR or LOOP in SQL (I usually do such in C#)
Here is my problem in one Database I have a set of identic design tables automaticaly created by an UpdateProcess
As
NewPMP_20220503
NewPMP_20220509
NewPMP_20220510
NewPMP_20220513 ...

I want to get rid of this accumulation of table and append All in one single table
Below my Query to retrieve all tables name

SELECT 
name
from Sys.tables where name like 'NewPMP_%'
 order by name

My question is how can I iterate the result to process an
INSERT INTO ... SELECT * FROM @TableName
For each name
Of course I can that from CSharp but I would like to learn how to do it directly from SQL

Thanks for any help

2

Hello
I've tried something like that (just to try and familiarize with a loop template)
But the problem is that I can't use a variable as a tablename
So it looks live I have to dive deeper in complexity :slight_smile:

DECLARE @MyCursor CURSOR;
DECLARE @MyField varchar(50);
BEGIN
    SET @MyCursor = CURSOR FOR
 SELECT 
name
from Sys.tables where name like 'NewPMP_%'
 order by name

    OPEN @MyCursor 
    FETCH NEXT FROM @MyCursor 
    INTO @MyField

    WHILE @@FETCH_STATUS = 0
    BEGIN
      SELECT COUNT(*) FROM @MyField
      FETCH NEXT FROM @MyCursor 
      INTO @MyField 
    END; 

    CLOSE @MyCursor ;
    DEALLOCATE @MyCursor;
END;

Again, thanks for any help

3

You can use d-sql for this.

Dynamic SQL in SQL Server (sqlshack.com)

4

Thank you RogierPronk

Indeed below is my working test
Now I have to better familiarize and understand the FETCH and CURSOR instruction
Never use this before :slight_smile:

DECLARE @MyCursor CURSOR;
DECLARE @MyField varchar(50);
DECLARE @SQLT nvarchar(120)=N'SELECT COUNT(*) FROM ' ;
DECLARE @SQL nvarchar(120);

BEGIN
SET @MyCursor = CURSOR FOR
SELECT
name
from Sys.tables where name like 'NewPMP_%'
order by name

OPEN @MyCursor 
FETCH NEXT FROM @MyCursor 
INTO @MyField

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @SQL=Concat(@SQLT,' ',@MyField);
Exec sp_executesql @SQL
  FETCH NEXT FROM @MyCursor 
  INTO @MyField 
END; 

CLOSE @MyCursor ;
DEALLOCATE @MyCursor;

END;

5

If you just need the total number of rows in those tables:

SELECT t.name, p.rows
FROM sys.tables t
INNER JOIN sys.partitions p ON t.object_id=p.object_id
WHERE t.name LIKE 'NewPMP_%' and p.index_id<2

That should be significantly faster as well if your tables have millions of rows, as it doesn't need to count anything.

6

Thank you robert_volk
But as explained in my first and second post, the goal was not to get the total number of record
But to append each table in one single table
The issue was my inexperience on CURSOR, LOOP and Iteration topic in SQL
So the count(*) was just temporarily used to simplify the model to test the loop syntax :grinning:

7

some thing like this

SELECT 
   'INSERT INTO ... SELECT  * FROM @'+name+'''
from 
   Sys.tables 
where 
   name like 'NewPMP_%'
order by 
   name
8

Thanks to all

This is one solution

DECLARE @MyCursor CURSOR;
DECLARE @MyField varchar(50);
DECLARE @SQLT nvarchar(120)=N'INSERT INTO NEWPMP_All SELECT * FROM ' ;
DECLARE @SQLDROP nvarchar(120)=N'DROP TABLE ' ;
DECLARE @SQL nvarchar(120);


BEGIN
 --TRUNCATE TABLE NewPMP_All;

    SET @MyCursor = CURSOR FOR
 SELECT 
name
from Sys.tables where name like 'NewPMP_%'
 order by name


    OPEN @MyCursor 
    FETCH NEXT FROM @MyCursor 
    INTO @MyField

    WHILE @@FETCH_STATUS = 0
    BEGIN
      SET @SQL=Concat(@SQLT,' ',@MyField);
	  Exec sp_executesql @SQL
	  SET @SQL=Concat(@SQLDROP,' ',@MyField);
	  Exec sp_executesql @SQL
      FETCH NEXT FROM @MyCursor 
      INTO @MyField 
    END; 

    CLOSE @MyCursor ;
    DEALLOCATE @MyCursor;
END;
9

Ah, I missed that. To build on @harishgg1 's suggestion:

DECLARE @sql nvarchar(max)=N'';
SELECT @sql+=N' UNION ALL SELECT * FROM ' + name 
FROM sys.tables 
WHERE name LIKE 'NewPMP_%';
SET @sql=N'INSERT INTO newTable ' + STUFF(@sql,1,charindex(N' SELECT',@sql),N'');
EXEC(@sql);

You'll need to change the "newTable" to match the actual table name. That will INSERT all rows from those tables in one operation, no need for a loop/cursor.