SQL Server - If columns not exist then create column

Question

I have an example table(#temp) like:

Account    ABC      DEF     GHI     
-----------------------------------
A001       1000.00  NULL    NULL    
A002       NULL     500.00  800.00  
A003       NULL     700.00  NULL    
A004       1100.00  NULL    NULL

The headers ABC, DEF, GHI are the result of a pivot. I'd like to:

select 
    *,
    case 
       when ABC is not NULL and JKL is not NULL 
          then 1 
          else 0 
    end as newColumn 
from 
    #temp

This errors out, because JKL doesn't exist. However, it can exist and has to be accounted for when it does. How can I edit my query statement to check if the columns ABC and JKL exist > if they do not exist then 0 value > if they do exist and either are null then 0 value > if they are both not null then 1 value in a newColumn.

columns are bound at compile time, so the only way I can suggest you do this is via dynamic SQL. — pmbAustin
– pmbAustin, Commented Jan 7, 2020 at 20:32
The fact that you have a table that doesn't have a defined definition seems odd. Why not just ensure you have the column JKL, even if it isn't needed? — Thom A
– Thom A ♦, Commented Jan 7, 2020 at 20:33
@Larnu - I think one potential solution is to create a new column if the column doesn't exist. However, I'm not sure how to implement that route. — dcrowley01
– dcrowley01, Commented Jan 7, 2020 at 20:38
Your database design is flawed. You absolutely should not have this kind of problem. Which means you should read up on proper database design and change your database layout, instead of trying to work around this problem. — Tomalak
– Tomalak, Commented Jan 7, 2020 at 20:47

Thom A · Accepted Answer · 2020-01-07 21:37:17Z

1

As you're using a temporary table, this is a bit ugly, but perhaps something like this would work:

IF NOT EXISTS (SELECT 1 
               FROM tempdb.sys.tables t
                    JOIN tempdb.sys.columns c ON t.object_id = c.object_id
               WHERE t.name LIKE N'#temp[_]%'
                 AND c.[name] = N'JKL')
    ALTER TABLE #Temp ADD JKL int;

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(13);
--Need dynamic SQL, as the batch will fail still otherwise
SET @SQL = N'SELECT *,' + @CRLF + 
           N'CASE WHEN ABC IS NOT NULL AND JKL IS NOT NULL THEN 1 ELSE 0 END AS newColumn' + @CRLF + 
           N'FROM #temp;'

EXEC sp_executesql @SQL;

If JKL should have a non-NULL value then use the following for the ALTER:

    ALTER TABLE #Temp ADD JKL int DEFAULT 0 WITH VALUES;

edited Jan 7, 2020 at 21:37

answered Jan 7, 2020 at 20:43

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

dcrowley01 Over a year ago

The top section I was unable to run, but it did lead me to the below solution.

Thom A Over a year ago

I see, I had written dbo here instead of sys, my bad. Fixed. (It's getting late here)

dcrowley01 Over a year ago

That works. Question: Why join tables t to columns c instead of just a simple search like: SELECT [name] FROM tempdb.sys.columns WHERE [object_id] = Object_id(N'tempdb..#temp') AND [name] LIKE 'JKL'

Thom A Over a year ago

Habit more than anything, @dcrowley01 .

dcrowley01 · Accepted Answer · 2020-01-07 21:31:59Z

0

I was able to complete with:

IF NOT EXISTS (select 1
    from tempdb.sys.tables t
        inner join tempdb.sys.columns c
        on t.object_id = c.object_id
    where t.Name like '%temp%' AND
        c.Name = 'JKL')
ALTER TABLE #temp ADD JKL int;

select * from #temp

answered Jan 7, 2020 at 21:31

dcrowley01

1512 silver badges13 bronze badges

2 Comments

Thom A Over a year ago

What happened to the CASE expression you need?

dcrowley01 Over a year ago

@larna - Once the column is added, I can create a secondary temp table if necessary to include the case. I posted a more complicated question here: stackoverflow.com/questions/59632295/…

Collectives™ on Stack Overflow

SQL Server - If columns not exist then create column

2 Answers 2

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related