CODEWITHSUNDEEP
sqlsql-serverselectdynamic-sql
Daniel Almeida
Daniel Almeida

Reputation: 362

Select columns whose name is not present in another table

Is there a way to only select the columns whose names are not present on another table? For example

Table A                         Column B
ID  | Name   | Address          Address
-----------------------
1   | Daniel | dummy

In this example my select statement should be like this:

select ID, Name from Column A

I've seen people talking about dynamic SQL but I can't find a decent example to solve my issue, any help is much appreciated.

Upvotes: 0

Views: 3360

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270311

Here is a version of the way you would do this using dynamic SQL:

declare @cols varchar(max);
set @cols = NULL;

select @cols = coalesce(@cols + ', ' + column_name, column_name)
from information_schema.columns ca 
where ca.table_name = 'A' and
      ca.column_name not in (select cb.column_name
                             from information_schema.columns cb
                             where cb.table_name = 'B'
                            );

declare @sql varchar(max);
set @sql = 'select [cols] from A';
set @sql = replace(@sql, '[cols]', @cols);

exec sp_executesql @sql;

This is a bit simplified to show how the information_schema tables can be sued. It will work in many circumstances, but is not maximally general:

  • It doesn't take schema name into account.
  • It assumes all names are simple ASCII.
  • It does not escape the column names (assuming the names do not need to be escaped).

Upvotes: 3

Matt
Matt

Reputation: 15061

Select the other table in the WHERE clause.

SELECT ID, NAME
FROM ColumnA
WHERE NAME NOT IN (SELECT NAME FROM COLUMNB)

Upvotes: -2

Related Questions