CODEWITHSUNDEEP
sql-server-2008temp-tables
Zerotoinfinity
Zerotoinfinity

Reputation: 6530

Update column with default values from temporary table

I have stored a result set on a temporary table which has 

Column_Name, Data_Type, Default_Value
Col1         varchar      abc
Col2         varchar      abc
Col3         int          999
Col4         decimal      0.0
Col5         datetime     20120101

Now I have to build a statement which will Compare the column name with the column name of the table and based on the datatype of the temporary table it will assign the default value to it whenever column value is NULL.

I have no clue how to do that

This is what I have done till now

  CREATE TABLE #ColumnData (Column_Name varchar(500), Data_Type varchar(20),

Default_Value varchar(50))

 DECLARE @query1 NVARCHAR(MAX) = '' 
  DECLARE @SchemaName VARCHAR(20)
  DECLARE @TableName VARCHAR(50)

SET @SchemaName = 'dbo'
SET @TableName = 'tblCustomer'





     select @query1 = @query1 + 'UPDATE ' + @schemaName + '.' + @tableName + ' SET ' + 
     c.COLUMN_NAME + ' = ' + c.Default_Value+ ' WHERE ' +   c.COLUMN_NAME 
     + ' = ''NULL'';' FROM #ColumnData c  
     WHERE C.TABLE_NAME = @tableName     

      EXECUTE sp_executesql @query1

Upvotes: 0

Views: 3830

Answers (1)

Chris Gessler
Chris Gessler

Reputation: 23113

This should do the trick. Tested it out in SQL 2008.

update t
set t.col1 = isnull(t.col1, tr.col1) 
    t.col2 = isnull(t.col1, tr.col2)
    etc.
from my_table t
cross join temp_table tr

Update:

-- create your temp table like this, then set all the default values in a single row (note the _temp on the end of the temp table name).

select * into #MyTable_temp from dbo.MyTable where 1=1

DECLARE @query NVARCHAR(MAX) = 'update t set ' 
DECLARE @SchemaName VARCHAR(20) 
DECLARE @TableName VARCHAR(50) 

SET @SchemaName = 'dbo' 
SET @TableName = 'MyTable' 

set @query = @query + ( 
        select stuff((
            select ', ' + c.column_name + ' = isnull(t.' + c.column_name + ', tt.' + c.column_name + ')'
            from INFORMATION_SCHEMA.COLUMNS c
            where c.TABLE_NAME = @TableName
            for xml path('')), 1, 2, ''))

set @query = @query + ' from ' + @SchemaName + '.' + @TableName + ' t cross join #' + @TableName + '_temp tt'

select @query

Your query should look like this:

update t 
    set Col1 = isnull(t.Col1, tt.Col1), 
        Col2 = isnull(t.Col2, tt.Col2), 
        Col3 = isnull(t.Col3, tt.Col3), 
        Col4 = isnull(t.Col4, tt.Col4), 
        etc... 
from dbo.MyTable t 
cross join #MyTable_temp tt

Upvotes: 1

Related Questions