Solved

SQL Query - Check if Column Exists

Posted on 2011-03-15
5
1,001 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

In this article I will describe the Detach & Attach 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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now