2 Datatables to 1

Question

Hello,

Let's say i have Datatable1 that has "Colum1" , "colum2" and another table, Datatable2 with "Colum3" , "colum4"

I need to create Datatable3 that will contain all the columns "Colum1" , "colum2", "Colum3" , "colum4"

I need something smart, like the DefaultView.ToTable() method that does it for one table.

Thanks

Answer 1

As you didn't mention any language or DB server type, here's something that might work (not tested really) on SQL Server 2005:

CREATE TABLE #temp (col1 <type>, col2 <type>, col3 <type>, col4 <type>)
INSERT INTO #temp
(
    SELECT t1.Column1, t1.Column2, t2.Column3, t2.Column4
    FROM Datatable1 t1 INNER JOIN Datatable2 t2 ON t2.<PrimaryKeyField> = t1.<PrimaryKeyField>

    UNION

    SELECT t1.Column1, t1.Column2, t2.Column3, t2.Column4
    FROM Datatable2 t2 INNER JOIN Datatable1 t1 ON t1.<PrimaryKeyField> = t2.<PrimaryKeyField>
)

SELECT * FROM #temp