Tutorial :Use SQL to export Parent/Child rows into a flat file



Question:

With an Orders table:
(OrderID, date, customerID, status, etc)

and an OrderDetails table:
(ParentID, itemID, quantity, price, etc)

I would like to create a SQL Query that will export a CSV flat file with Order and OrderDetail rows interspersed. For instance, output might look like this (H and D indicate "Header" and "Detail" respectively.):

"H",2345,"6/1/09",856,"Shipped"  "D",2345,52,1,1.50  "D",2345,92,2,3.25  "D",2345,74,1,9.99  "H",2346,"6/1/09",474,"Shipped"  "D",2346,74,1,9.99  "D",2346,52,1,1.50  

Not sure where to even start with this. Any ideas? TIA.


Solution:1

You'll want to take advantage of the fact that union all will honor the order by clause at the end on the entire result set. Therefore, if you order by the second column (2!) ascending, and the first column (1!) descending, you'll get the header row, then the detail rows underneath that.

Also, make sure that you have the same number of columns in the two queries. They don't have to be of the same data type, since you're exporting to CSV, but they do have to be the same number. Otherwise, the union all won't be able to pile them onto each other. Sometimes, you'll just have to pad columns with null if you need extra ones, or '' if you don't want the word null in your CSV.

select      'H',      OrderID,      Date,      CustomerID,      Status  from      Headers  union all  select      'D',      ParentID,      ItemID,      Quantity,      Price  from      Details  order by      2 asc, 1 desc  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »