• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1036
  • Last Modified:

SQL Query - Check if Column Exists

I have a table that has about 10 columns.  I am trying to get this data reformatted into another table with about 25 columns.  So what I want to do is check if the column in the first table exists and if it does, take that value and if the column doesn't exists, put a default 0 for the column.  Without doing the insert into yet, I am just trying to display the results to see if I am building the query correctly.  Just trying this for one column, I a getting error.  My query is:

select
CASE
WHEN EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table1' AND COLUMN_NAME = 'EngineDesignation')
THEN EngineDesignation
ELSE '0' END
as EngineDesignation

FROM table1

I am getting error stating that "Invalid column name 'EngineDesignation'."

What am I doing wrong?  Obviously this column doesn't exist in table1 and in that case I want to display a 0 value for it.

0
Kamilek0617
Asked:
Kamilek0617
1 Solution
 
Ephraim WangoyaCommented:


select
CASE
WHEN EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table1' AND COLUMN_NAME = 'EngineDesignation')
THEN 'EngineDesignation'
ELSE '0' END
as EngineDesignation
0
 
Kamilek0617Author Commented:
Nope, because when I use a column that actually exists in the table, it doesn't give the the value of the column but simply the word I have in parenthesis after THEN
0
 
8080_DiverCommented:
This is liable to sound a bit radical, but here goes:

Why not select the column names from the Information_Schema.Columns for the source table into a temporary, in-memory table and then select the column names from the Information_Schema.Columns for the target table into another temporary, in-memory table?  At that point, you can build the SQL Statement to SQL statement that you will need to use to insert the data into the target table in a User Variable and then use an Expression to provide the SQL statement to the the Execute SQL Task that you will use to do the work.

Builing the SQL Statement would simply involve selecting the column names in both the Source and Target in-memory tables.
0
 
SharathData EngineerCommented:
Can you explain more on what exactly you are looking for?
0
 
SharathData EngineerCommented:
I assume that you want to consider the columns if those are exist in another table. If that is true you can try like this. Check the example as well.
declare @sql nvarchar(max)
select @sql = 'select '
set @sql = @sql + (select rtrim(substring(isnull((select ',' + isnull(c2.COLUMN_NAME,'NULL') + ' as ' + c1.COLUMN_NAME 
  from INFORMATION_SCHEMA.COLUMNS c1 left join INFORMATION_SCHEMA.COLUMNS c2 
    on c1.COLUMN_NAME = c2.COLUMN_NAME and c2.TABLE_NAME = 'table2'
   where c1.TABLE_NAME = 'table1' for xml path('')),' '),2,2000)))
select @sql = @sql + ' from table1' 
print (@sql)
exec (@sql)

Open in new window

--create table table1 (col1 int, col2 int, col3 int, col4 int)
--create table table2 (col3 int, col4 int, col5 int, col6 int)
-- col3 and col4 are common columns, when you query table1 you should have SELECT clause with col3 and col4. and NULLs for col1, col2.
declare @sql nvarchar(max)
select @sql = 'select '
set @sql = @sql + (select rtrim(substring(isnull((select ',' + isnull(c2.COLUMN_NAME,'NULL') + ' as ' + c1.COLUMN_NAME 
  from INFORMATION_SCHEMA.COLUMNS c1 left join INFORMATION_SCHEMA.COLUMNS c2 
    on c1.COLUMN_NAME = c2.COLUMN_NAME and c2.TABLE_NAME = 'table2'
   where c1.TABLE_NAME = 'table1' for xml path('')),' '),2,2000)))
select @sql = @sql + ' from table1' 
print (@sql)
-- select NULL as col1,NULL as col2,col3 as col3,col4 as col4 from table1
exec (@sql)

/*
drop table #table1
drop table #table2
*/

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now