Many table to union by loop function-CodePudding

I have 1000 tables and I need to union all tables

table_name like feature_20220503, feature_20220504 but it's not continuous numbers

It escape holiday

I want to use a loop function, but I don't know how to do it.

Does anyone could help.

CodePudding user response：

You just need to make dynamic query like this

DECLARE @QUERY NVARCHAR(MAX) = ''

SELECT @QUERY = @QUERY 'UNION ALL SELECT * FROM ' TABLE_NAME CHAR(13)
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME LIKE 'feature_%'

SET @QUERY = SUBSTRING(@QUERY,11,LEN(@QUERY))

INSERT INTO NewTable
EXEC(@QUERY)

Just Create new new table with the same schema as all the other tables and make sure you take only those tables from INFORMATION_SCHEMA