Combine two datatables in one datatable using select query

Question

Hello,

Can anybody help me please how to combine two datatables in one resulting datatable in the same dataset using select statement?

I made three select commands, the first two for filling the first two tables, and the third which combines them for filling a third datatable. These works fine when run in access.
But I don't know how to use the third, combining select command, to fill the third datatable in the dataset.

Then I want to use this third datatable for populating my Crystal Report.

If somebody has an idea, I would appreciate your help a lot.
Thanks in advance.

Herry Ng · Answer

as far as I know , dataset does not support SELECT-SQL, you could use LINQ but why not initially SELECT from the datasource ex: Select * from table1 --> Fill datatable1 in dataset Select * from table2 --> Fill datatable2 in dataset Select * from table1, table2 where table1.key=table2,key --> Fill datatable3 in dataset Best Regards,

Nel · Answer

Yes I want to use SQL select. In fact I have these select queries which work in access. 1.Select query for the first datatable: SELECT NALOG1.GBRV, NALOG1.DATA, Min(IIf([NALOG1.GBRV]=[@gbr1] And ((NOVI.AB)=[@ab1] Or (NOVI.AB)=[@ab2]) And [NALOG1].[POCKM]>0,[NALOG1.POCKM],Null)) AS pockm, Max(IIf([NALOG1.GBRV]=[@gbr1] And ((NOVI.AB)=[@ab1] Or (NOVI.AB)=[@ab2]) And ([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA] FROM NALOG1 INNER JOIN NOVI ON NALOG1.GBRV = NOVI.GBR GROUP BY NALOG1.GBRV, NALOG1.DATA HAVING (((NALOG1.GBRV)=[@gbr1]) AND ((NALOG1.DATA)>=[@data1] And (NALOG1.DATA) 2. Select query for the second datatable: SELECT MAGACIN.GBR, MAGACIN.DATA, NOVI.AB, Sum(IIf([sifra]='0992201',[kol],Null)) AS Addblue, Sum(IIf([sifra]='0999001',[kol],Null)) AS Antifriz, Sum(IIf([sifra]='0991000',[kol],Null)) AS Gorivo, Sum(IIf([sifra]='0993050',[kol],Null)) AS Motmaslo FROM MAGACIN INNER JOIN NOVI ON MAGACIN.GBR = NOVI.GBR GROUP BY MAGACIN.GBR, MAGACIN.DATA, NOVI.AB HAVING (((MAGACIN.GBR)=[@gbr1]) AND ((NOVI.AB)=[@ab1] or (NOVI.AB)=[@ab2]) AND ((MAGACIN.DATA)>=[data1] And (MAGACIN.DATA) ORDER BY MAGACIN.DATA; 3. and the select query for the combination of theboth datatables: SELECT NalogNov.DATA, MagNov.GBR, Max(IIf([MagNov].[DATA]=[NalogNov].[DATA],[MagNov].[Gorivo],0)) AS Gorivo1, Max(IIf([MagNov].[DATA]=[NalogNov].[DATA],[MagNov].[Addblue],0)) AS Addblue1, Max(IIf([MagNov].[DATA]=[NalogNov].[DATA],[MagNov].[Antifriz],0)) AS Antifriz1, Max(IIf([MagNov].[DATA]=[NalogNov].[DATA],[MagNov].[Motmaslo],0)) AS Motmaslo1, NalogNov.pockm, NalogNov.krajkm, [krajkm]-[pockm] AS RAZLIKA, Max(NalogNov.Poslprov) AS MaxOfPoslprov, Max(NalogNov.Poslserv) AS MaxOfPoslserv, Max(NalogNov.KMS) AS MaxOfKMS, Max(NalogNov.KMP) AS MaxOfKMP FROM NalogNov LEFT JOIN MagNov ON NalogNov.GBRV = MagNov.GBR WHERE (((MagNov.GBR)=[NalogNov].[GBRV])) GROUP BY NalogNov.DATA, MagNov.GBR, NalogNov.pockm, NalogNov.krajkm, [krajkm]-[pockm] ORDER BY NalogNov.DATA; So I fill the first two datatables by using oleDBDataAdapter1.SelectCommand = command1; oleDBDataAdapter1.Fill(dataSet2, "NalogNov") and respectively for the other datatable. I don't know how to fill the third datatable.

Muralidharan Deenathayalan · Answer

Do you want to use datatable.select or sql select statement?

Nel · Answer

Thanks, but the columns of bothdata tables are not the same. How to use the select query which works fine when run in access to fill the third datatable?

Muralidharan Deenathayalan · Answer

Please try the following. 1) You can use datatable.merge to merge two datatable into one. DataTable dt1 = new DataTable(); DataTable dt2 = new DataTable(); DataTable dt3 - dt1.Copy(); dt3.Merge(dt2); http://msdn.microsoft.com/en-us/library/system.data.datatable.merge.aspx 2) You can import the data rows from different tables and load into one. http://msdn.microsoft.com/en-us/library/system.data.datatable.loaddatarow(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.data.datatable.importrow(v=vs.100).aspx

Combine two datatables in one datatable using select query

Paul Rajs

Srikant Maruwada