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: , , , ,

Comments are closed.