CODEWITHSUNDEEP
sql-servert-sql
DShook
DShook

Reputation: 15654

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

I have a situation where I have many tables that are similar data structures but not exactly the same along these lines:

Table 1

Table 2

What I want to end up with after doing some data transformation is:

Table 3

There are several more tables that have varied but similar schemas so I am working on a script that will import to Table 3 from a variety of tables that are missing some columns.

Based on this question I've tried a query like this:

SELECT A
      ,B
      ,case 
        when COL_LENGTH('t2', 'C') IS NULL 
        then NULL 
        ELSE C
        end as C
  FROM t2

But it throws an error of "Invalid column name C" even though it would be selecting null.

Is there another way to select a column only if it exists and NULL if it doesn't?

Upvotes: 1

Views: 1710

Answers (2)

Devart
Devart

Reputation: 121902

This script generate dynamic SQL for any table structure.

Query:

SET NOCOUNT ON

IF OBJECT_ID (N'dbo.Table_1') IS NOT NULL
   DROP TABLE dbo.Table_1

IF OBJECT_ID (N'dbo.Table_2') IS 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 INTO dbo.Table_1 (A, B, C)
VALUES (1, 1, 1), (2, 2, 2) 

INSERT INTO dbo.Table_2 (A, B)
VALUES (6, 1), (8, 2) 

DECLARE @SQL NVARCHAR(2000)

;WITH cte AS 
(
    SELECT 
          column_name = '[' + c.name + ']'
        , table_name = '[' + s.name + '].[' + o.name + ']'
    FROM sys.columns c WITH (NOLOCK)
    JOIN sys.objects o WITH (NOLOCK) ON c.[object_id] = o.[object_id]
    JOIN sys.schemas s WITH (NOLOCK) ON o.[schema_id] = s.[schema_id]
    WHERE o.name IN ('Table_1', 'Table_2')
        AND s.name = 'dbo'
        AND o.[type] = 'U'  
), cols AS 
(
    SELECT DISTINCT column_name 
    FROM cte    
), tbl AS 
(
    SELECT DISTINCT table_name
    FROM cte
), rs AS 
(
    SELECT 
          tbl.table_name
        , column_name = ISNULL(cte.column_name, cols.column_name + ' = NULL')
    FROM cols
    CROSS JOIN tbl
    LEFT JOIN cte ON cols.column_name = cte.column_name AND cte.table_name = tbl.table_name
), rs2 AS (
    SELECT uni = ' UNION ALL' + CHAR(13) + 'SELECT ' + STUFF((
        SELECT ', ' + rs.column_name
        FROM rs
        WHERE tbl.table_name = rs.table_name
        GROUP BY rs.column_name
        ORDER BY rs.column_name
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + 
        ' FROM ' + table_name
    FROM tbl
) 
SELECT @SQL = 'SELECT 
' + STUFF((
    SELECT CHAR(13) + ', ' + cols.column_name
    FROM cols
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ' ')
 + ' 
FROM 
(' + STUFF((
    SELECT CHAR(10) + uni
    FROM rs2
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 11, '') + CHAR(13) + 
    ') t'

PRINT @SQL

EXECUTE sys.sp_executesql @SQL

Output:

SELECT 
      [A]
    , [B]
    , [C] 
FROM 
(
    SELECT [A], [B], [C] FROM [dbo].[Table_1]
     UNION ALL
    SELECT [A], [B], [C] = NULL FROM [dbo].[Table_2]
) t

Results:

A           B           C
----------- ----------- -----------
1           1           1
2           2           2
6           1           NULL
8           2           NULL

Upvotes: 1

Abe Miessler
Abe Miessler

Reputation: 85036

Can you just use a UNION?

select A, B, C FROM Table1
UNION
Select A, B, null FROM Table2

Upvotes: 0

Related Questions