Merging two tables which have same and new columns

1
votes

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?

2
c#sqlsql-servermerge
yes, assuming cols 1,2,3 always equal each other.. what did you try – BugFinder
yes they are equal, I tried some queries like CREATE TABLE Test_table as SELECT col1, col2, col3, col4, col5 FROM table1 UNION SELECT col1, col2, col3, col5, col6, col7 FROM table2; – Umm E Habiba Siddiqui
then show what you tried, what issues you had - this site is not about doing all the work for you – BugFinder
Union is not what you want here. You need to join. Do we have a key value to join? I do not think you are looking for cartesian join. – I_am_Batman
You probably want a view – Hogan

2 Answers

2
votes

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.

0
votes

If you don't want to use a VIEW, then you can create a table via:

    WITH    Data
              AS ( SELECT   col1 ,
                            col2 ,
                            col3 ,
                            col4 ,
                            NULL AS col5 ,
                            NULL AS col6 ,
                            NULL AS col7 ,
                            col8
                   FROM     table1
                   UNION ALL
                   SELECT   col1 ,
                            col2 ,
                            col3 ,
                            NULL AS col4 ,
                            col5 ,
                            col6 ,
                            col7 ,
                            NULL AS col8
                   FROM     table2
                 )
        SELECT  *
        INTO    table3
        FROM    Data;