Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Query - Check if Column Exists

Posted on 2011-03-15
5
Medium Priority
?
1,019 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
[X]
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
5 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
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 41

Expert Comment

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

Accepted Solution

by:
Sharath earned 2000 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

719 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