Adding a new column with data from the connection

Question

What is the best approach for adding a column to an existing table with values ​​from the join ... For example:

If I join table A to table B ...

Select A.Column1, A.Column2, B.Column1, B.Column2, B.Column3 FROM A INNER JOIN B ON A.Column1 = B.Column2 

Basically, I just want to copy the column that exists in table B to table A, how can I add a new A.Column3 to table A to match B.Column3 based on the join?

Answer 1

Try the following:

 alter table A add column3 datatype update A set column3 = B.column3 from A inner join B on A.Column1 = B.Column2 

Answer 2

Please note that this is probably not the most efficient method.

 alter table A add column3 [yourdatatype]; update A set column3 = (select column3 from B where A.Column1 = B.Column2) where exists (select column3 from B where A.Column1 = B.Column2) 

Answer 3

First use the alter table command to add a new column.

Afterwords uses the update command to put the values ​​of B in the column created in A.

Answer 4

You can do it:

 alter A add column3 datatype; update A inner join (select column2 ,column3, count(*) as cnt from B group by column2) b on A.column1 = b.column2 set A.column3 = b.column3; 

Answer 5

You can create a new table using the INSERT statement:

 INSERT INTO NEW_A SELECT A.*, B.Column3 FROM A INNER JOIN B ON A.Column1 = B.Column2