Query to add values from TableA to TableB

Thanks for your help.
How to fit your query in my query mentioned above.

The query he gave you looks right but he left out the WHERE clause.

SET @SQL = 'UPDATE b SET b.Ref = a.Ref, b.Auth = a.Ref FROM TableA a JOIN TableB b ON a.Code = b.Code WHERE ( a.DeleteRecord is null or a.DeleteRecord = 0)'

Select allOpen in new window

Remember the a.Code and b.Code are whatever unique identifiers you are using to join the tables...so you have to change that.

You can use merge function -
merge into tableB B
using tableA A
on (B.code = A.code)
when matched
then set
B.Auth = A.Auth,
B.Ref = A.Ref

Assuming you only want to update B with values in A if, and only if a column in B named DeletedRecord is null or 0.

UPDATE  b 
SET b.Ref = a.Ref, 
b.Auth = a.Ref 
FROM TableB b INNER JOIN TableA ON a.Code = b.Code WHERE ISNULL(b.DeleteRecord, 0) = 0

Select allOpen in new window

Thanks, but how do I seelect other columns/Fields in Table B .
Example

SET @SQL = 'SELECT Ref,Auth,CODE,Name,Surname FROM TABLEB      WHERE ( DeleteRecord is null or DeleteRecord = 0)'

The join does it. You are updating table b from table a. The WHERE clause is selecting to update only those with Deleted the Pres in NULL or = 0.

How do add other columns from Table B in the query

>How do add other columns from Table B in the query
Multiple experts here have answered your basic question.  Is it really that hard to mimic the code we've given you to add other columns?

UPDATE b
SET b.Ref = a.Ref, b.Auth = a.Ref, b.Name = a.Name, b.Surname = a.Surname
FROM TableA a 
   JOIN TableB b ON a.Code = b.Code
WHERE ( DeleteRecord is null or DeleteRecord = 0)

Select allOpen in new window

CODE is the JOIN column between tables, so to pull this off it is already the same value.

Jim,
The columns Name and surname do not belong to Table A.
They are columns present only in Table B.

Please find the attached File
Example6.xlsx

>The columns Name and surname do not belong to Table A.  They are columns present only in Table B.
Ok, but for starters multiple experts have answered your original question, so we seem to be moving beyond that now.

>The columns Name and surname do not belong to Table A.
In this case you'll have to define for us what values you intend to UPDATE in b.Name and b.Surname.

Jim,
Thanks and apologies.
Please find the attached file to see the expected result
Example6--1-.xlsx

Looking at the second Example6.xlsx (which again is different from your original question, which multiple experts have answered), the Name and Surname values in Table B are the same as in your expected result.  

It is not clear what you are asking.  Are you only intending to change values in Table B (aka UPDATE), or generate a set that combines values from Table A and Table B (aka SELECT) ?

generate a set that combines values from Table A and Table B (aka SELECT)

Perfect !!! Thanks !!