Click here to Skip to main content
Click here to Skip to main content

Use SQL to generate large data sequence

, 9 Feb 2011
Rate this:
Please Sign up or sign in to vote.
Generate large data series by using Cartesian Product and small data table
The proposed solution demonstrates the concept of generating a large data series by using structured query language (SQL) and relatively small data tables, thus dramatically reducing the overall 'digital footprint' (rather important design consideration for any mobile/compact/embedded solutions).
Sample coding technique to demonstrate the concept utilizes a small table (Table1) with just 10 records, containing integer numbers: 0,1,2,3,...9, and a single SQL query to generate a virtual time line, which spans for 10,000 days, or roughly 27 years moving backward from the current date (Access SQL, Listing 1):
SELECT Now()-CLng([Table1].[Num] & [Table1_1].[Num] & [Table1_2].[Num] & [Table1_3].[Num]) AS TimeSeries
FROM Table1, Table1 AS Table1_1, Table1 AS Table1_2, Table1 AS Table1_3
ORDER BY Now()-CLng([Table1].[Num] & [Table1_1].[Num] & [Table1_2].[Num] & [Table1_3].[Num]) DESC;
The logic behind the solution is rather straightforward. The query creates 2 virtual tables (Table_1, Table_2 and Table_3) in addition to the 'concrete' permanent Table1, and then applies the so-called 'Cartesian product' technique, concatenating every record from all 4 tables (permanent and virtual as well). Then it applies the data type conversion to long integer, which represents the date, and finally, sorts the date series of 10,000 records, going from current date backward in descending order.
For detailed discussion, please refer to the article by the author:
SQL generates large data sequence[^]


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

President Infosoft International Inc
United States United States
Dr. A. Bell has 20+ years of Software and Electrical Engineering experience. He is Win/Web veteran, published 200+ articles and authored 37 inventions, currently focused on: Windows 7/8, HTML5, CSS3, jQuery, SQL, .NET, ASP.NET, WPF, C#, Speech Technology and Mobile apps. He's been among recent App submission winners (The Windows 8* & Ultrabook™ App Innovation Contest 2012). Sample pubs:
  1. Edumatter M12: School Math Calculators and Equation Solvers (contest winner)
  2. Engineering Calculator VOLTA-2013 (contest winner)
  3. HTML5 Best Practices: Table formatting via CSS3
  4. Edumatter-M12 for Windows, app overview
  5. Engineering Calculator VOLTA-814D
  6. CoolPhone: phone numbers-to-text converter
  7. SQL generates large data sequence
  8. Aggregate Product function extends SQL
  9. Top-50 Digital Cameras
  10. WebTV Project: Embedded YouTube Player (Goog #1 YouTube API for ASP.NET)
Dr. Bell is personally credited for 10+ Enterprise level projects (Finance/Investment, Engineering, Edu) w/total code base exceeding 250k lines; doing consulting in NYC for 20 yrs.
Follow on   Twitter

Comments and Discussions

GeneralFYI: short URL to the full article: PinmemberDrABELL4-Jan-11 9:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140821.2 | Last Updated 10 Feb 2011
Article Copyright 2010 by DrABELL
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid