upagna upagna - 6 months ago 10
SQL Question

Create table as select with join

I need to create table with all un matching id's between table A and table B. Both the tables have some matching fields , but I would need all the columns from table A only

Table A

ID NAME address Phone
12 xyz some street 1234566
22 xyz some street 1234566
3 xyz some street 1234566


Table B

ID Vendor_NAME address Phone email
1 xyz street 1234566 xyz@gmail.com
2 xyz street 1234566
3 xyz street 1234566


Resulting table- Table C

ID NAME address Phone
12 xyz some street 1234566
22 xyz some street 1234566


I am using this query to get all the un-matching values from table A

select * from A left join B on A.ID=B.ID where B.ID is null


This gives me all the fields from table A and Table B. How do I create table C so that it would have only the un-matching values and fields from table A.

Updating a Table B with Table C

Table B

ID Vendor_NAME street_address Phone email
1 xyz street 1234566 xyz@gmail.com
2 xyz street 1234566
3 xyz street 1234566


Table C

ID NAME address Phone
12 xyz some street 1234566
22 xyz some street 1234566


Update table B

ID Vendor_NAME street_address Phone email
1 xyz street 1234566 xyz@gmail.com
2 xyz street 1234566
3 xyz street 1234566
12 xyz some street 1234566
22 xyz some street 1234566


Table B has the same data , but with different column names and would have few extra columns. I would like to insert all the data from table C table B.

Answer

Use SELECT * INTO will help in your case. No need to create the table schema. It automatically create the table along with give column's data type

CREATE Table TableC as SELECT A.* 
FROM TableA 
LEFT JOIN TableB ON A.ID = B.ID 
WHERE B.ID IS NULL

Update answer as per OP's new request:

INSERT INTO TableB (ID, Vendor_NAME, street_address, Phone, email)
SELECT ID, NAME, address, Phone, NULL
FROM TableC

Note: If the email is not null column, then instead of NULL place two single quotes ''