tsql - How do I UPDATE a field from another table -

i have 2 tables have same data , fields except one. want update table missing field 'sbcmp'. here definition of table want add field , data to:

[dbo].[salesdata](     [sbloc] [varchar](3) null,     [sbcust] [varchar](7) null,     [rmname] [varchar](30) null,     [ifprvn] [varchar](6) null,     [sbitem] [varchar](25) null,     [sbitd1] [varchar](50) null,     [sbdiv] [smallint] null,     [sbcls] [smallint] null,     [sbqshp] [smallint] null,     [avc] [real] null,     [sbeprc] [real] null,     [sbinv] [int] null,     [sbord] [int] null,     [sbtype] [varchar](1) null,     [sbindt] [datetime] null, [rmstat] [varchar](2) null 

the other table has exact same table definitions except has field [sbcmp] [smallint] null

i use new table, old table has other older data new 1 not.

i want know best method of update table add field , data.

first of design table , add new field.

or run

alter table salesdata add sbcmp smallint null 

then may use merge in order data.

merge salesdata target using (select * salesdatanew) source        on (target.idfield = source.idfield ) when matched      update set sbloc = source.sbloc,                sbcust = source.sbcust ,                rmname = source.rmname,                ifprvn = source.ifprvn ,                sbitem = source.sbitem ,                sbitd1 = source.sbitd1 ,                sbdiv = source.sbdiv ,                sbcls = source.sbcls ,                sbqshp = source.sbqshp ,                avc = source.avc,                sbeprc = source.sbeprc,                sbinv = source.sbinv ,                sbord = source.sbord,                sbtype = source.sbtype,                sbindt = source.sbindt,                rmstat = source.rmstat ,                sbcmp = source.sbcmp when not matched        insert (sbloc,                sbcust ,                rmname,                ifprvn ,                sbitem ,                sbitd1 ,                sbdiv  ,                sbcls  ,                sbqshp ,                avc ,                sbeprc ,                sbinv ,                sbord,                sbtype,                sbindt,                rmstat,                sbcmp )     values (source.sbloc,                source.sbcust ,                source.rmname,                source.ifprvn ,                source.sbitem ,                source.sbitd1 ,                source.sbdiv  ,                source.sbcls  ,                source.sbqshp ,                source.avc ,                source.sbeprc ,                source.sbinv ,                source.sbord,                source.sbtype,                source.sbindt,                source.rmstat                source.sbcmp) 

keep in mind have used imaginary field called idfield in on clause of merge. imaginary not clear id of table. if there 2 columns forming id should add them in join statement.

one more thing have named new table salesdatanew didn't know actual name.

the merge full outer joinning 2 tables (called target , source). matched rows performing update , rows not matched on source , not on target performs insert. both update , insert performed on target.

it possible on target when there rows on target not on source (here delete) out of scope believe.

if want update , not insert above ok (though should remove when not matched part. can straight update.

an example be:

update     salesdata set        sbloc = source.sbloc,            sbcust = source.sbcust ,            rmname = source.rmname,            ifprvn = source.ifprvn ,            sbitem = source.sbitem ,            sbitd1 = source.sbitd1 ,            sbdiv = source.sbdiv ,            sbcls = source.sbcls ,            sbqshp = source.sbqshp ,            avc = source.avc,            sbeprc = source.sbeprc,            sbinv = source.sbinv ,            sbord = source.sbord,            sbtype = source.sbtype,            sbindt = source.sbindt,            rmstat = source.rmstat ,            sbcmp = source.sbcmp       salesdata target            join salesdatanew source                on target.idfield = source.idfield