SQLite Forum

Help please importing data
Login

Help please importing data

(1) By adrian762 on 2020-11-20 15:55:28 [link] [source]

Hi

i am new to this and want to find the difference between two tables. The first table I envisage as just being numbers 1 to 4000, I was going to either use a loop or import but I cannot do either. I need this table to be import/created to a copy of a database I cannot corrupt.

In summary I will create a table with numbers in from the original database and compare with this created table of numbers 1 to 4000.

I have the logic cracked but i need to create the 1 to 4000 table each time. As I see it I have two choices. Either attach another database with numbers 1 to 4000 in or import a csv file to a new table.

The second option would be best but have failed both ways.

Sorry if it is too basic or if it has been answered already

Any help would be appreciated

Adrian

(2) By anonymous on 2020-11-20 16:13:27 in reply to 1 [link] [source]

Why would you want to create a table only containing numbers 1 to 4000?
You could use the series extension (https://www.sqlite.org/src/file?name=ext/misc/series.c):

select value from generate_series(1,4000,1);

(3) By adrian762 on 2020-11-21 00:31:05 in reply to 2 [link] [source]

Absolutely brilliant. Very many thanks

(4) By Keith Medcalf (kmedcalf) on 2020-11-21 00:42:02 in reply to 1 [link] [source]

Or, without using an extension:

with series(value)
  as (
                values(1)
      union all
         select value + 1
           from series
          limit 4000
     )
select value from series;

(5) By Simon Slavin (slavin) on 2020-11-21 12:15:24 in reply to 1 [link] [source]

Another solution to those already given: Create just once another database containing the numbers 1 to 4000 and use the SQL command ATTACH to make it look like this is part of the database you're checking.

https://www.sqlite.org/lang_attach.html

(6) By adrian762 on 2020-11-21 12:23:50 in reply to 5 [link] [source]

Wow, 3 different solutions and i only needed one. Thanks so much

(7) By adrian762 on 2020-11-21 12:49:01 in reply to 4 [source]

May I ask one subsidiary question please. If i wanted to change the 4000 to a figure that I would obtain from the following how would I do it

SELECT MAX(Details) FROM EventTable WHERE EventType = 35

Again, thanks

(8) By Larry Brasfield (LarryBrasfield) on 2020-11-21 13:07:00 in reply to 7 [link] [source]

In code Keith provided, substitute the literal 4000 with (SELECT the_count_I_want FROM what_I_say WHERE blah_blah) Or, substitute that inner query with whatever floats your boat.

(9.1) By Keith Medcalf (kmedcalf) on 2020-11-21 13:20:28 edited from 9.0 in reply to 7 [link] [source]

One way would be:

with series(value)
  as (
         select 1
          where exists (
                        select *
                          from EventTable
                         where EventType = 35
                       )
      union all
         select value + 1
           from series
          where value < (
                         select max(Details)
                           from EventTable
                          where EventType = 35
                        )
     )
select value from series;

or, if using the generate_series (series.c) extension:

select value
  from generate_series
 where start = 1
   and stop = (
               select max(Details)
                 from EventTable
                where EventType = 35
              );

(10) By Keith Medcalf (kmedcalf) on 2020-11-21 13:46:51 in reply to 8 [link] [source]

That will have a problem if the subselect returns NULL (as in there are none) because an error will be thrown (datatype mismatch).

Putting the subquery in a where clause will work except that it will always return a single row (1) even if there are no rows in the subquery.

So both halves of the recursive CTE need to be fixed in order to get nothing returned if there is nothing to return.

generate_series does this automatically because the result of the test is always false (value <= stop) because anything compared to null is null (false) so nothing is returned.

(11) By Keith Medcalf (kmedcalf) on 2020-11-21 14:10:55 in reply to 9.1 [link] [source]

You could also "recurse backwards" to get the result so that you do not need to duplicate the subquery:

with series(value)
  as (
         select max(Details)
           from EventTable
          where EventType = 35
      union all
         select value - 1
           from series
          where value > 1
     )
select value from series where value is not null order by value;

(12) By adrian762 on 2020-11-21 22:34:20 in reply to 8 [link] [source]

Larry, again thanks, just what i wanted

(13) By adrian762 on 2020-11-21 22:36:54 in reply to 11 [link] [source]

Keith, thanks for looking again. Your solution may work but unfortunately it is too complicated for a beginner like me but i appreciate your time. Many thanks