0

The goal is to select all from all tables and store it in a dataset. I have an MS access database that appears to have independent (not related) tables. My select query in c# currently looks like this:

"SELECT * FROM tableA" + "SELECT * FROM tableB" + ... etc. but I keep getting "error in FROM clause" message.

How can I select from all tables in a database without using JOIN ? I could make an independent SELECT statement and store in an independent dataset for each table but there must be a better way ?

I have seen this achieved with a stored procedure because the procedure can just be

Create Procedure myProc
as
Begin
    SELECT * FROM tableA

    SELECT * FROM tableB

    SELECT * FROM tableC
END

How can I achieve this with my query string in C#

Thanks in advance.

  • look up `union all` – juergen d Mar 08 '17 at 16:43
  • Do all these tables have the same schema?Are the columns you are trying to fetch of the same datatype and are you fetching same number of columns from these tables? – BipinR Mar 08 '17 at 16:50
  • Will this not return 1 result set though ? I want to be able to store all of the tables in a dataset to iterate through later –  Mar 08 '17 at 16:53
  • You can use Union All only if you are fetching the column as same data type and the number of columns you fetch from each table should be the same. This will return the result in same dataset – BipinR Mar 08 '17 at 16:55
  • Tables are all different. Different number of columns, different column names etc. –  Mar 08 '17 at 16:57
  • Please look this answer maybe is useful. http://stackoverflow.com/questions/7239450/returning-multiple-tables-from-a-stored-procedure – Sergio Camillo Jr Mar 08 '17 at 17:36

2 Answers2

1

Try this:

How to fill Dataset with multiple tables?

The code you want to look at is:

SqlDataAdapter adapter = new SqlDataAdapter(
      "SELECT * FROM Customers; SELECT * FROM Orders", connection);
adapter.TableMappings.Add("Table", "Customer");
adapter.TableMappings.Add("Table1", "Order");

adapter.Fill(ds);
Community
  • 1
  • 1
Bill Roberts
  • 1,127
  • 18
  • 30
  • btw, if the dataset will be used with an .Update(), you need to be sure that you TableMappings tie back exactly to the table you load. The DataAdapter can't infer the table names from the select, so the Update() won't be able to push changes back to the database. – Bill Roberts Mar 08 '17 at 18:26
  • Also, the Update() can modify a table even if the select statement is a join of two or more tables, provided you still name the output table per the table you want to modify, and only modify the columns within the output table that correspond to the TableMappings name you assign. That functionality is pretty sweet :) – Bill Roberts Mar 08 '17 at 18:30
  • I've tried this before. I cant seem to do a select this way. OleDbDataAdapter oAdapter = new OleDbDataAdapter("SELECT * FROM tableA;SELECT * FROM tableB", oCon); I get a "Characters found after end of SQL statement". –  Mar 09 '17 at 10:03
  • Depending on the provider, I suspect the SQL terminator character is the issue. Also, the way you provide parameters can also be an issue. What are you connecting to? – Bill Roberts Mar 09 '17 at 16:37
  • The other thing here is that the ";" only serves as a separator - the example doesn't show one as a terminator to the last statement. And yes I run into this as well when appending a ";" on a single like SQL statement. – Bill Roberts Mar 09 '17 at 16:43
-1

You can do this, but this may not be the ideal solution.Put the table with largest number of columns in the first select and substitute the rest with null, also convert all the column to the same datatype

select 1,'2016-01-05','TestA','1500.00',2500,150
union all 
select 1,'2016-01-05','TestA','1200.00',null,null
union all
select 1,'2016-01-05','TestA','1300.00',null,null
BipinR
  • 473
  • 7
  • 19