Two methods to update data from one table to another table using sql and TSQl
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
Tags: fields from one table to another tbale, SQL (Structured Query Language), sql server, tsql, update data
