Clever Geek Handbook

SQL: how to dynamically loop and add N number of null columns to temp table

Due to some requirement I need to create two temp tables in Stored Procedure, after processing some data in them, I need to concatenate two temp tables to show as one result set and generate in excel. So I think to use UNION

it when I want to show the final set of results.

The problem is that the first temporary table (table A) is fixed to 20 columns and the second temporary table has 50 columns (table B). My plan is that before processing the data for table A, I want to add 30 nullable columns and insert data for the first 20 columns and the rest is everythingNULL

After processing the data for table B, I use UNION

to concatenate tables A and B so that they appear as one result set.

Now I can think of how to hard-code some columns that are destined to have null values ​​when I declare the temp table:

Declare @tmpTableA table (
  ....
  ProcessDate datetime,
  Mode int,
  Col21 varchar(10)
  Col22 varchar(10)
  ....
  Col50 varchar(50)
)

When I insert data into table A, I need to manually add null from Col21 onwards

Insert into(.... Col21, Col22, Col23....)
Values (.... NULL, NULL, NULL....)

After finishing processing the data for tables A and B, I use UNION

to join tables A and B

Select *....Col49,Col50 From Table A
Union
Select *....CompleteDate,ContactPerson From Table B

Instead of hardcoding Col21-Col50 into table A, is there any neat way to achieve this, for example using a loop while

to dynamically add N number of columns to table A?

EDIT: According to the last requirement, table B contains not only 50 columns, but 100 columns too! I really need a way to dynamically loop through these columns, not hardcoding for more than 80 columns.

+3


source to share


2 answers


I think you can just do

select * into #tableA from #tableB where 1=2


both these tables will have the same columns

+1


source


You don't need to add columns to table A, just add 30 NULLs to select from table A.

Select *,NULL,...,NULL,NULL From Table A 
Union
Select * From Table B


You can add aliases to make the result cleaner

Select *,...,NULL CompleteDate, NULL ContactPerson From Table A
Union
Select * From Table B
0


source






More articles:


All Articles