0

If I have two tables, say tabA and tabB, in my test.db which is an sqlite database, is it possible to export the data of both tables in a single myFile.csv?

I know how to export the tables separately, but I was wondering whether it is possible to export them in the same file.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
FranXh
  • 4,481
  • 20
  • 59
  • 78
  • I take it you are using this technique: http://stackoverflow.com/questions/6076984/how-to-export-the-results-of-my-query-to-csv-file-sqlite?rq=1 since you didn't mention a programming language. If you are using a particular programming language (say c# or php) there are probably several ways to do this depending on what you want the output file to look like. – MatthewMartin Apr 21 '13 at 01:05
  • I am just using the terminal in Ubuntu: .output file.csv .mode csv – FranXh Apr 21 '13 at 01:36

2 Answers2

2

The best answer depends on what the relationship between the tables is. Do the rows in the tables contain 1:1 mappings? Is there a foreign key relationship that one table has with another?

The reason that this is important is because it critically determines how you go to the next step, which is to combine (or JOIN) the tables together. Once the tables are joined, you can easily export the data you want from the ResultSet.

For example, if tabB contains items ordered for the orders that are contained in the rows of tabA, then your tabB most likely has a foreign key many-to-one relationship with tabA, and can be joined together like this.

SELECT a.* b.*
FROM tabA AS a
JOIN tabB AS b ON a.primary_key = b.foreign_key

This assumes that your tables are well-formed and have a well defined relationship that you can use to join them together in a way that makes sense.

scottb
  • 9,908
  • 3
  • 40
  • 56
2

If tabA has columns A, B, C and tabB has columns A, B, C (i.e. the one to one mapping)

SELECT A, B, C from tabA 
union  
SELECT A, B, C from tabB

You can use constant for the columns that don't match up

SELECT A, B, NULL from tabA 
union  
SELECT A, NULL, C from tabB
MatthewMartin
  • 32,326
  • 33
  • 105
  • 164