CODEWITHSUNDEEP
sqljoin
Pankaj
Pankaj

Reputation: 2744

Join two tables with different column counts

I am trying to create a table fom two different tables

Table 1 has 

ID   Place      Value   Date  
--------------------------------
1    Place     Value1   Date1
2    Place1    Value2   Date2
4    Place2    Value1   Date1
7    Place3    Value2   Date1

and Table2 has 

ID    Name        Values
------------------------
3    ColName1    Value2
5    ColName2    Value1
6    ColName3    Value2

Now I want to create a new table with ID, Table1.Place, Table1.Date and Table2.Value

I tried using 

INSERT INTO [dbo].[tblNewTable] 
SELECT tbl1.ID, tbl1.Place, tbl1.Date
FROM [Tabble1] tbl1 
UNION
SELECT tbl2.Value 
FROM [Table2] tbl2

but it says that All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Please Note that ID is unique in both the tables.

Upvotes: 0

Views: 102

Answers (2)

sgeddes
sgeddes

Reputation: 62841

Not exactly sure what you're asking for. If you want to use a UNION statement, the same number of columns have to be in each query (with the same data types). So in your case, if you're desired results are to get a list of ids 1 to 7, then you'd need something like this:

INSERT INTO [dbo].[tblNewTable] 
SELECT tbl1.ID, tbl1.Place, tbl1.Date, tbl1.Value
FROM [Tabble1] tbl1 
UNION
SELECT tbl2.ID, NULL, NULL, tbl2.Value 
FROM [Table2] tbl2

This will result in:

ID   Place     Value    Date  
--------------------------------
1    Place     Value1   Date1
2    Place1    Value2   Date2
3    NULL      Value2   NULL
4    Place2    Value1   Date1
5    NULL      Value1   NULL
6    NULL      Value2   NULL
7    Place3    Value2   Date1

Alternatively, you are looking to JOIN your tables to get a common result set:

INSERT INTO [dbo].[tblNewTable] 
SELECT tbl1.ID, tbl1.Place, tbl1.Date, tbl2.Value
FROM [Tabble1] tbl1  
    JOIN [Table2] tbl2 ON tbl1.ID = tbl2.ID

In your supplied data, this will result in no records though.

Upvotes: 2

Cahit Gungor
Cahit Gungor

Reputation: 1497

Union should be on two tables both which have same number and type of columns. The query should be as follows. What you need is a join as follows:

INSERT INTO [dbo].[tblNewTable] 
SELECT tbl1.ID, tbl1.Place, tbl1.Date, tbl2.Value 
FROM [Tabble1] tbl1,  [Table2] tbl2
WHERE tbl1.value = tbl2.value

Upvotes: 1

Related Questions