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:
WHEN EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table1' AND COLUMN_NAME = 'EngineDesignation')
ELSE '0' END
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.