Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL Query - Check if Column Exists

Posted on 2011-03-15
5
1,007 Views
Last Modified: 2012-05-11
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
Comment
Question by:Kamilek0617
5 Comments
 
LVL 32

Expert Comment

by:ewangoya
ID: 35142029


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
 

Author Comment

by:Kamilek0617
ID: 35142036
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
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35142586
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
 
LVL 40

Expert Comment

by:Sharath
ID: 35142829
Can you explain more on what exactly you are looking for?
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 35142920
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

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question