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?
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.