tsql - Sql Server: How do you select a column only if it exists? -

i have situation have many tables similar data structures not same along these lines:

table 1

• a int not null
• b int not null
• c int not null

table 2

• a int not null
• b int not null

what want end after doing data transformation is:

table 3

• a int not null
• b int not null
• c int null

there several more tables have varied similar schemas working on script import table 3 variety of tables missing columns.

based on this question i've tried query this:

select       ,b       ,case          when col_length('t2', 'c') null          null          else c         end c   t2 

but throws error of "invalid column name c" though selecting null.

is there way select column if exists , null if doesn't?

this script generate dynamic sql table structure.

query:

set nocount on  if object_id (n'dbo.table_1') not null    drop table dbo.table_1  if object_id (n'dbo.table_2') not null    drop table dbo.table_2  create table dbo.table_1 (a int not null, b int not null, c int not null) create table dbo.table_2 (a int not null, b int not null)  insert dbo.table_1 (a, b, c) values (1, 1, 1), (2, 2, 2)   insert dbo.table_2 (a, b) values (6, 1), (8, 2)   declare @sql nvarchar(2000)  ;with cte  (     select            column_name = '[' + c.name + ']'         , table_name = '[' + s.name + '].[' + o.name + ']'     sys.columns c (nolock)     join sys.objects o (nolock) on c.[object_id] = o.[object_id]     join sys.schemas s (nolock) on o.[schema_id] = s.[schema_id]     o.name in ('table_1', 'table_2')         , s.name = 'dbo'         , o.[type] = 'u'   ), cols  (     select distinct column_name      cte     ), tbl  (     select distinct table_name     cte ), rs  (     select            tbl.table_name         , column_name = isnull(cte.column_name, cols.column_name + ' = null')     cols     cross join tbl     left join cte on cols.column_name = cte.column_name , cte.table_name = tbl.table_name ), rs2 (     select uni = ' union all' + char(13) + 'select ' + stuff((         select ', ' + rs.column_name         rs         tbl.table_name = rs.table_name         group rs.column_name         order rs.column_name         xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, '') +          ' ' + table_name     tbl )  select @sql = 'select  ' + stuff((     select char(13) + ', ' + cols.column_name     cols     xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, ' ')  + '   (' + stuff((     select char(10) + uni     rs2     xml path(''), type).value('.', 'nvarchar(max)'), 1, 11, '') + char(13) +      ') t'  print @sql  execute sys.sp_executesql @sql 

output:

select        [a]     , [b]     , [c]   (     select [a], [b], [c] [dbo].[table_1]      union     select [a], [b], [c] = null [dbo].[table_2] ) t 

results:

a           b           c ----------- ----------- ----------- 1           1           1 2           2           2 6           1           null 8           2           null