CODEWITHSUNDEEP
sqlsql-server
Herbert
Herbert

Reputation: 181

Overwrite ONE column in select statement with a fixed value without listing all columns

I have a number of tables with a large number of columns (> 100) in a SQL Server database. In some cases when selecting (using views) I need to replace exactly ONE of the columns with a fixed result value instead of the data from the row(s). Is there a way to use something like 

select table.*, 'value' as Column1 from table

if Column1 is a column name within the table?

Of course I can list all the columns which are expected as result in the select Statement, replacing the one with a value. However, this is very inconvinient and having 3 or 4 those views I have to maintain them all if columns are added or removed from the table.

Upvotes: 7

Views: 11947

Answers (2)

Daniel
Daniel

Reputation: 1837

As other responses have noted, this can't be done in a single statement. There is a workaround, however, which is not perfect but does circumvent the need to list columns manually: save your initial, unmodified query to a temp table, update the column(s) you need to overwrite, then select the results:

--we're going to use a temp table; make sure it doesn't already exist
if (object_id('tempdb..#tmpTbl') is not null)
    drop table #tmpTbl

--initial query to retrieve all the columns
select *
into #tmpTbl
from TblWithManycolumns

--update column(s) from another table or query
update #tmpTbl
set ColToBeReplaced = trv.ColWithReplacementValue
from #tmpTbl t
join TableWithReplacementValue trv
    on trv.KeyCol = t.KeyCol
--where trv.FilterCol = @FilterVal -- if needed

--this select contains the final output data
select * from #tmpTbl
drop table #tmpTbl

This has plenty of drawbacks. Complexity, performance, etc. But it is very flexible and solves the major problem of preventing changes to the main table (TblWithManyColumns) from breaking the query or requiring manual changes. This is particularly important if you're trying to generate SQL.

Upvotes: 0

miracle_the_V
miracle_the_V

Reputation: 1156

Nope, you have to specify columns in this case.
And you have much more serious problems if tables are being changed often. This may be a signal of large architectural defects.
Anyway, listing all columns instead of * is a good practice, because if columns number will change, it may cause cascade errors.

Upvotes: 3

Related Questions