Combining multiple sql tables into one

Olgun

Member
Joined
Sep 10, 2019
Messages
9
Programming Experience
Beginner
I have an sql database with multiple tables. I am using all of them in a vb.net program.
All of them has the same field names. But i want one of them to have whole data which the other 4 tables have.
Any suggestions please...
 

JuggaloBrotha

VB.NET Forum Moderator
Staff member
Joined
Jun 3, 2004
Messages
4,522
Location
Lansing, MI; USA
Programming Experience
10+
Create a query, then copy it and paste it the multiple times you need it and put the word "Union" between them, be sure to do and Into NewTableName so it's all saved to a new table.
 

Olgun

Member
Joined
Sep 10, 2019
Messages
9
Programming Experience
Beginner
I created this query
It worked succesfully, but dbo didnot change...

SELECT * FROM maintable AS f
INNER JOIN table1 AS a ON a.IDNo = f.IDNo
INNER JOIN table2 AS b ON b.IDNo = f.IDNo
INNER JOIN table3 AS c ON c.IDNo = f.IDNo
INNER JOIN table4 AS d ON d.IDNo = f.IDNo
 

JuggaloBrotha

VB.NET Forum Moderator
Staff member
Joined
Jun 3, 2004
Messages
4,522
Location
Lansing, MI; USA
Programming Experience
10+
That's not at all what I suggested.
Your query will be something like this:
Code:
Select a.[Field1]
      ,a.[Field2]
      ,a.[Field3]
Into [dbo].[YourNewTable]
From (
  Select [Field1]
        ,[Field2]
        ,[Field3]
  From [dbo].[Table1]
  Union
  Select [Field1]
        ,[Field2]
        ,[Field3]
  From [dbo].[Table2]
  Union
  Select [Field1]
        ,[Field2]
        ,[Field3]
  From [dbo].[Table3]
  Union
  Select [Field1]
        ,[Field2]
        ,[Field3]
  From [dbo].[Table4]
) a;
 

Olgun

Member
Joined
Sep 10, 2019
Messages
9
Programming Experience
Beginner
Thank you, but i am getting this error

The data type text cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.
 

Olgun

Member
Joined
Sep 10, 2019
Messages
9
Programming Experience
Beginner
Thank you jmcilhinney,
I used varchar instead of text, then problem is ok...
Regards
 

Olgun

Member
Joined
Sep 10, 2019
Messages
9
Programming Experience
Beginner
I have one more question please.
Is it possible to execute this query in button clic
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,127
Location
Sydney, Australia
Programming Experience
10+
Any code that can be executed, can be executed in the Click event handler of a Button. If you don't know how to work with databases in VB.NET, do some research on ADO.NET, which is the standard .NET data access technology. There's a link in my signature below that leads to a popular tutorial that includes working with databases.
 

Olgun

Member
Joined
Sep 10, 2019
Messages
9
Programming Experience
Beginner
Ok, thank you for reply, those links are really so usefull. Now i can execute .sql file from a button.
Now i need to change the table name in .sql file within vb.net.
Is it possible?
 

Olgun

Member
Joined
Sep 10, 2019
Messages
9
Programming Experience
Beginner
Ok, I found another solution, please dont take into account...
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,127
Location
Sydney, Australia
Programming Experience
10+

Olgun

Member
Joined
Sep 10, 2019
Messages
9
Programming Experience
Beginner
You are right, please disregard my wrong sentences since English is not my first language:)
 
Top Bottom