Click here to Skip to main content
14,430,765 members
Rate this:
Please Sign up or sign in to vote.
See more:
How to concadenate two tables into single table ?

Table 1
Name    city
Raja_1    Newyork_1
Guna_2    claifornia_2
Guna_3    claifornia_3


Table 2
DoorNo
1
2
3
4
5
7


Table 3 (expected Result)

Name    city               DoorNo
Raja_1    Newyork_1           1
Guna_2    claifornia_2        2
Guna_3    claifornia_3        3
                              4
                              5
                              6
                              7

I am using
dt1= DataTable
dt2= DataTable
dt3= DataTable

Repeater1.DataSource = dt3;
Repeater1.DataBind();


What I have tried:

string QueryStringData = "SELECT Name,city from Table1;
                    QueryStringData += "SELECT DoorNo from Table2;
                    
using (SqlCommand com_Find = new SqlCommand(QueryStringData, con_Find))
{
   com_Find.CommandType = CommandType.Text;
   con_Find.Open();
   SqlDataAdapter con_Find_adpr = new SqlDataAdapter(com_Find);
   DataSet connection_ds_con_ds = new DataSet();
   con_Find_adpr.Fill(connection_ds_con_ds);
   DataTable dt1 = new DataTable();
   DataTable dt2 = new DataTable();
   DataTable dt3 = new DataTable();
   dt1 = connection_ds_con_ds.Tables[0];
   dt2 = connection_ds_con_ds.Tables[1];
                    
   if (dt1.Rows.Count == dt2.Rows.Count)
      for (int i = 0; i < dt1.Rows.Count; i++)
         dt3.Rows.Add(dt1.Rows[i]["Name"], dt1.Rows[i]["city"], dt2.Rows[i]["DoorNo"]);
   Repeater1.DataSource = dt3;
   Repeater1.DataBind();
}
Posted
Updated 7-Oct-19 2:57am
v2
Rate this:
Please Sign up or sign in to vote.

Solution 1

That's a silly requirement - the two data tables are unrelated, which means there is nothing to "tie" Raja_1 from one table with the "DoorNo" from the other table.

Which means that you need to look more closely at the source data - yoru database - and identify the "common factor" in Table1 and Table2, then instead of doing two SELECT commands to return two sets of unrelated data, you would use a JOIN query to return a single combined Data set: SQL Joins[^]

Any other way to do it does not guarantee that the same query(s) executed at slightly different times will give you the same results - and that's bad news!
Remember, unless you specify an order in which to return data as part of your query ("SELECT ... ORDER BY criteria") SQL is at liberty to return rows in any order it finds suitable - and that can change due to circumstances beyond your control. It may not happen, but it can - and then the info presented to your users changes, which can have drastic results.
   
Comments
Maciej Los 7-Oct-19 7:43am
   
5ed!
Rate this:
Please Sign up or sign in to vote.

Solution 2

Quote:
How to concadenate two tables into single table ?
...
string QueryStringData = "SELECT Name,city from Table1;
                    QueryStringData += "SELECT DoorNo from Table2";

You CAN'T "concatenate" (join) tables by concatenating strings!

I completely agree with OriginalGriff's statements. There's no relationship between your tables! So, you need to define relationship between them to be able to get data from both tables. At this moment, you can workaround this by using below query (NOT recommended):
DECLARE @table1 TABLE ([Name] VARCHAR(50),City VARCHAR(50))
INSERT INTO @table1 ([Name], City)
VALUES('Raja_1', 'Newyork_1'),
('Guna_2', 'claifornia_2'),
('Guna_3', 'claifornia_3')

DECLARE @table2 TABLE(DoorNo INT)
INSERT INTO @table2 (DoorNo)
VALUES(1), (2), (3), 
(4), (5), (7)

SELECT t1.*, t2.*
FROM @table1 t1 
	INNER JOIN @table2 t2 ON CONVERT(INT, RIGHT(t1.Name, 1)) = t2.DoorNo


Result:
Name	City			DoorNo
Raja_1	Newyork_1		1
Guna_2	claifornia_2	2
Guna_3	claifornia_3	3


For further details, please see:
Joins (SQL Server) - SQL Server | Microsoft Docs[^]
Visual Representation of SQL Joins[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100