Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Query - Check if Column Exists

Posted on 2011-03-15
5
Medium Priority
?
1,020 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: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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

876 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