SELECT FROM TABLES NAME LIKE

Question

I have 1000 tables in a SQL Server database (SSMS) and want to bring selected columns from 1000 tables into 1 new table.

I want to do something like this

Create a new table with columns a, b, c
Identify 1000 tables from which data is to be selected - table name to be selected based on something like wildcard.

In the database, there are many many tables and name are following similar pattern e.g. ABC_0001, ABC_0002 ... ABC_1000, XYZ_0001, XYZ_0002, ... XYZ_1000 etc... I want only tables starting with ABC to be selected..
Select columns a, b, c from those 1000 tables and insert the values into table created in step #1

I am doing it like this:

SELECT a, b, c FROM ABC_0001 UNION_ALL
SELECT a, b, c FROM ABC_0002 UNION_ALL
SELECT a, b, c FROM ABC_1000 
INSERT INTO NEW_TABLE

And want to do something like

SELECT a, b, c FROM TABLE LIKE ABC_% 
INSERT INTO NEW_TABLE

Are you just going to be running this, as a one-off, within SQL Management Studio? If so, probably you're easiest approach is going to be to build the select statement, dynamically, for each result from a select against "sys.sysobjects" - eg. SELECT [name] FROM sys.sysobjects WHERE [name] LIKE 'ABC_%' AND [type] = 'U' — Craig, Apr 08 '21 at 04:35
Thanks Craig for your response, I am newbie to SQL so cant follow completely.. however I have list of all the tables i.e. ABC_0001... ABC_1000.. so when type SELECT * FROM sys.tables WHERE ( name LIKE 'ABC_%' ) AND type = 'U' I get table name and object id and so on.. however I would like the data from all table in one.. — HKJ, Apr 08 '21 at 05:08
there isn't really a single statement that can determine all of the possible table names AND do the selection from all of those tables in one hit. My suggestion was to query "sysobjects" to determine the available table names matching your criteria, but then you'd have to loop through each of those records and incrementally add the relevant select statement for each table to a variable, and then you can execute that statement at the end ..... however, if you are completely new to SQL, then that may be a challenge for you to attempt. — Craig, Apr 08 '21 at 05:32
Perhaps use Excel (or similar, eg. Google Sheets) to generate the running number sequence, and then use a formula to generate each 'SELECT a, b, c FROM ABC_ UNION ALL' statement - then you can just copy and paste into Management Studio and execute the query. That's an approach that I often take if I have a one-off task like this, that needs a common SQL statement generated, but there's some kind of numerical sequence that Excel can do easily — Craig, Apr 08 '21 at 05:34

Annamalai D · Accepted Answer · 2021-08-04T15:12:30.233

You can use Dynamic SQL and CURSOR OR WHILE LOOP to achieve this, I implemented it using CURSOR. use UNION instead of UNION ALL if you don't want to insert duplicate entries into the new table.


DROP TABLE IF EXISTS TABLE_NEW
CREATE TABLE TABLE_NEW (A VARCHAR,B VARCHAR, C VARCHAR) -- Modify according to your need
DECLARE @Sql NVARCHAR(MAX) = ''
,@TableName VARCHAR(500)
,@Id INT

DECLARE Table_Cursor CURSOR FOR 
SELECT 
ROW_NUMBER() OVER (ORDER BY TABLE_NAME ASC) Id
,TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE' 
AND TABLE_NAME LIKE 'ABC_%'

OPEN Table_Cursor  
FETCH NEXT FROM Table_Cursor INTO @Id,@TableName 

WHILE @@FETCH_STATUS = 0  
BEGIN  
     IF(@Id = 1)
        BEGIN
            
            SET @Sql = @Sql + 'SELECT A, B, C FROM '+@TableName ----Modify the columns based on your column names
            SELECT @SQL
        END
     ELSE
        BEGIN
            SET @Sql = @Sql + ' UNION ALL SELECT A, B, C FROM '+@TableName --Modify the columns based on your column names
        END

     FETCH NEXT FROM Table_Cursor INTO @Id,@TableName
END 

CLOSE Table_Cursor  
DEALLOCATE Table_Cursor

INSERT INTO NEW_TABLE 
EXEC (@Sql)

Added the below answer as per your new requirement. you can check whether a column present in a table or not and then take decision based on the result you get. I have used IIF() and COL_LENGTH() functions to achieve this.

DROP TABLE IF EXISTS TABLE_NEW
CREATE TABLE TABLE_NEW (A VARCHAR,B VARCHAR, C VARCHAR) -- Modify according to your need
DECLARE @Sql NVARCHAR(MAX) = ''
,@TableName VARCHAR(500)
,@Id INT

DECLARE Table_Cursor CURSOR FOR 
SELECT 
ROW_NUMBER() OVER (ORDER BY TABLE_NAME ASC) Id
,TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE' 
AND TABLE_NAME LIKE 'Temp%'

OPEN Table_Cursor  
FETCH NEXT FROM Table_Cursor INTO @Id,@TableName 

WHILE @@FETCH_STATUS = 0  
BEGIN  
     
     --Declare variables for all your columns here and assign the column name as the value

     DECLARE @A VARCHAR(100) = 'A', @B VARCHAR(100) = 'B',@C VARCHAR(100) = 'C'

     SELECT @A = IIF(COL_LENGTH(@TableName, @A)>0,@A,'NULL')
     SELECT @B = IIF(COL_LENGTH(@TableName, @B)>0,@B,'NULL')
     SELECT @C = IIF(COL_LENGTH(@TableName, @C)>0,@C,'NULL')

     IF(@Id = 1)
        BEGIN
            
            SET @Sql = @Sql + 'SELECT '+@A+' AS A, '+@B+' AS B, '+@C+' AS C FROM '+@TableName ----Modify the columns based on your column names
            SELECT @A AS A
        END
     ELSE
        BEGIN
            SET @Sql = @Sql + ' UNION ALL SELECT '+@A+' AS A, '+@B+' AS B, '+@C+' AS C FROM '+@TableName --Modify the columns based on your column names
        END

     FETCH NEXT FROM Table_Cursor INTO @Id,@TableName
END
CLOSE Table_Cursor  
DEALLOCATE Table_Cursor

INSERT INTO TABLE_NEW
SELECT @SQL

Thanks Annamalai.. the query works as expected.. however now I came across specific table which doest have selected variable and sql gives error that variable is missing. how can we overcome this? I am fine taking null values for these variables in final output. — HKJ, Jul 29 '21 at 08:50
If it is just one table, you can write a condition inside the loop to check the table name and select null in the place of missing column — Annamalai D, Jul 29 '21 at 11:09
There is new set of tables introduced and many of which doesnt have selected variables. is there a quick way to just check if variables are present in table, if not, assign null value? — HKJ, Jul 30 '21 at 02:41
Yes, there are ways to check whether a column present in a table or not. does the new set of tables miss more than one selected columns? — Annamalai D, Jul 30 '21 at 08:15
Yes.. some tables miss 1 columns and some miss 2 or more. The columns which is missing from each table also differs.. — HKJ, Jul 30 '21 at 14:04

Jeffrey Mesa · Answer 2 · 2021-04-08T05:33:18.267

0

You can do the following:

-- use 'UNION' to select only distinct values, and UNION ALL if you want to select all rows

SELECT * 
    INTO [NEW_TABLE]
        FROM ABC_001
            UNION ALL
        SELECT * FROM ABC_002
            UNION ALL
        SELECT * FROM ABC_003
            UNION ALL
        -- and so on...
        SELECT * FROM ABC_999
        -- there's no need to use UNION on the last select

If you really don't want to type all 999+ tables, you could write a stored procedure to select from tables where name matches your desired pattern.

References:
https://www.w3schools.com/sql/sql_ref_union.asp
SELECT INTO USING UNION QUERY
SQL Server select data from 100+ tables

edited Apr 08 '21 at 05:33

answered Apr 08 '21 at 05:26

Jeffrey Mesa

21
4

@HKJ - Jeffrey's suggestion here is similar to mine about selecting from "sysobjects" to find all tables matching your criteria. His suggestion of a stored procedure makes sense - because, as I said, you're going to need multiple lines of different statement to build up the dynamic SQL, and then execute it. A stored procedure becomes an object in the database that you can re-use, if this is a task that you're going to perform again in the future – Craig Apr 08 '21 at 05:37
Thanks Craig and Jefferey, I am trying the suggest code.. my tables have 500+ column and I have to select 100+ column from each table.. so writing SELECT [100+] column to FROM TABLE is not feasible.. can we store the required 100+ column as alias and use it at each line? – HKJ Apr 09 '21 at 01:46
what to do in case where we want to create a new column called "num" which contains the value of the last string of file name, for ex: if the filename is ABC_002 the column "num" will contain 002 @Annamalai D – nikki Oct 06 '22 at 13:20

SELECT FROM TABLES NAME LIKE

2 Answers2