Combining multiple sql tables into one

[Olgun]

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]

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]

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]

That's not at all what I suggested.
Your query will be something like this:

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]

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.

 

[jmcilhinney]

You shouldn't be using that data type in SQL Server these days anyway. If it's at all possible, change the existing data types to varchar or nvarchar first.

 

[Olgun]

Thank you jmcilhinney,
I used varchar instead of text, then problem is ok...
Regards

 

[Olgun]

I have one more question please.
Is it possible to execute this query in button clic

 

[jmcilhinney]

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]

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]

Ok, I found another solution, please dont take into account...

 

[jmcilhinney]

Ok, I found another solution

Cool.

please dont take into account...

Presumably English is not your first language so I thought that it may interest you that the usual expression would be "please disregard".

 

[Olgun]

You are right, please disregard my wrong sentences since English is not my first language