Umm E Habiba Siddiqui Umm E Habiba Siddiqui - 6 months ago 11
SQL Question

Merging two tables which have same and new columns - sql

I have two tables table1 with columns col1, col2, col3, col4, col8 and second table table2 with columns col1, col2, col3, col5, col6, col7.
I want to merge those table in one new table where data will be as col1, col2, col3, col4, col5, col6, col7, col8 respectively.

Is there any way in sql or c# I can do?

Answer

You probably want a view -- here is how you would make one on most platforms.

CREATE VIEW twotables AS
SELECT a.col1, a.col2, a.col3, a.col4, b.col5, b.col6, b.col7, a.col8 
FROM table1 a
JOIN table2 b ON a.col1=b.col1 and a.col2=b.col2 and a.col3=b.col3 and a.col4=b.col4

You may or may not want those columns in the join.

Another issue that many people have when they do something like this is they have different records for col1, col2, col3, and col4 in both tables. Then you need to something like this:

CREATE VIEW twotables as
SELECT a.col1, a.col2, a.col3, a.col4, b.col5, b.col6, b.col7, a.col8 
FROM (select distinct col1, col2, col3, col4 from table1
      union 
      select distinct col1, col2, col3, col4 from table2
) as k
LEFT JOIN table1 a ON a.col1=k.col1 and a.col2=k.col2 and a.col3=k.col3 and a.col4=k.col4
LEFT JOIN table2 b ON b.col1=k.col1 and b.col2=k.col2 and b.col3=k.col3 and b.col4=k.col4

Here we first get a list of all "keys" and then we left join to the two tables.