Two methods to updated data from one table to another table using sql and TSQL,
T1=Table1
T2=Table2
F1=Field1
F2=Field2
F3=Filed3
F4=Field4
Method one, Update date using subquery from one table to another table
update T2 set
T2.F1=isnull(T1.F1,”),
T2.F2= T1.F2,
T2.F3= T1.F3 from T1
inner join T2 on T2.F2= T1.F4
where
T2.F2<> T1.F2 and T2.F3<> T1.F3
and isnull(T2.F1,”)<>isnull(T1.F1,”)
Method 2 Use While Loop and iterate throught whole table to update data using a temporary or temp table, Choice is yours.
DECLARE @myERROR int
Declare @F4 varchar(50)
Declare @F1varchar(255)
Declare @F2bigint
Declare @F3 bigint
declare @counter int;
declare @count int;
–*****************Update Data
–1. Select common records in outputqas4, postcodelookup
select F4,F1, F2,F3 into ##tempEdit from T1 where F4 in(
select F4 from T1
intersect
select F4 from T2 )
–Set Counter
set @counter=1;
–Get total number of records from temp table
Select @count = Count(*) From ##tempEdit
While (@counter) < @count
Begin
Select Top 1 @F4 =F4,@F1=F1,@F2=F2,@F3=F3 From ##tempEdit
if not Exists(select * from T2 where F4 = @F4 and F1=@F1and @F3=@F3 and F2=@F2)
begin
–Update dispaly order
update T1 set F1=@F1,F3=@F3,F2=@F2 Where F4 = @F4
–Error handling
SELECT @myERROR = @@ERROR
print ‘Record updated for post code :’ + @F4
IF @myERROR != 0 GOTO HANDLE_ERROR
end
else
print ‘Record already exists’
–Delete entery from temp table
delete from ##tempEdit where F4=@F4
–Counter
set @counter= @counter +1
End
