How to optimise multiple query into a single query

Question

I have the following queries:

select top 10 'CISPRO_THXCOCD_201906' as tablename,* from HXCOCD_201906
select top 10 'CISPRO_THXCOCL_201906' as tablename,* from HXCOCL_201906
select top 10 'CISPRO_THXICLD_201906' as tablename,* from HXICLD_201906
select top 10 'CISPRO_THXICLM_201906' as tablename,* from HXICLM_201906
select top 10 'CISPRO_THXICND_201906' as tablename,* from HXICND_201906
select top 10 'CISPRO_THXINXT_201906' as tablename,* from HXINXT_201906

The query intent is to provide the sample data from the given table

I have nearly 50 queries like above; is there any way to combine the result into a single resultset?

The problem is each table have different column name and the number of columns also differ.

As far my understanding I want to run each query separately and paste into an Excel sheet.

Is there any easier approach to run the query in bulk rather than running indiviudually?

Answer 1

This gets the results to csv, so may not meet your needs.

So steps as follows:

1. You need to create a (temp) table of all the tables you need to select from
2. Create a cursor to iterate through the list of table
3. Build a dynamic query to select from table

4. Configure query results for text output: Query -> Query Options -> Results -> Text (see screen shot)

   No headings,Separate tab for result status,Custom separator of your choice

5. Set results to output to txt

6. Run query
7. Right click results "Save results as" csv

Example:

--Temp table to hold list of tables
CREATE TABLE #tbls (tblname varchar(20))    

--Add all tables to table list
INSERT INTO #tbls (tblname)
SELECT '1'
UNION ALL SELECT '2'
UNION ALL SELECT '3'
UNION ALL SELECT '4'
UNION ALL SELECT '5'

--Create cursor
DECLARE tblcur CURSOR
    FOR SELECT tblname FROM #tbls
OPEN tblcur

DECLARE @tblname varchar(20)
DECLARE @sql nvarchar(max) = ''

FETCH NEXT FROM tblcur
INTO @tblname

--Output contents of tables
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = @SQL + 'SELECT ['+ @tblname + '_ID] AS tablename,* FROM dbo.[' + @tblname +']'
    execute sp_executesql @SQL

    FETCH NEXT FROM tblcur
    INTO @tblname

END

CLOSE tblcur
DEALLOCATE tblcur
DROP TABLE #tbls

Answer 2

You can define an structure for the final result-set and supply default values for the missing columns of each table.

SELECT TOP 10
    COL1, COL2, COL3, 0 AS COL4, COL5
FROM
    HXCOCD_201906

UNION

SELECT TOP 10
    COL1, COL2, NULL AS COL3, COL4, 0 AS COL5
FROM
    HXCOCL_201906

UNION

....