Import data into a temporary table

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Import data into a temporary table

Eric Tsau
Hi,
Is it possible to add the option of importing data into a temporary table?
Currently you have to create a temporary table first before importing to
it, or having to drop the table afterwards.
.import dump.csv temp.table
or
.import dump.csv attach.table

Regards
Eric
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Import data into a temporary table

Simon Slavin-3
On 7 Mar 2019, at 9:45pm, Eric Tsau <[hidden email]> wrote:

> Is it possible to add the option of importing data into a temporary table?

Can you rephrase your request ?  You can import data into a temporary table

> .import dump.csv temp.table

Does this command not work correctly ?

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Import data into a temporary table

Keith Medcalf
In reply to this post by Eric Tsau

On Thursday, 7 March, 2019 14:45, Eric Tsau <[hidden email]> asked:

>Is it possible to add the option of importing data into a temporary
>table?

>Currently you have to create a temporary table first before importing
>to it, or having to drop the table afterwards.

>.import dump.csv temp.table
>or
>.import dump.csv attach.table

Apparently not ;)  It would appear that the .import shell command does not know how to create tables in schema's other than "main"

However, you can create a virtual table in the temp database using the csv extension (since .import only works from the sqlite3 shell) ...

create virtual table temp.tablename using csv(filename=filename.csv, header=yes);

The virtual table will be deleted when the connection is closed ... since it is only a temporary thing.  You could then create a duplicate materialized table if you wished (but since you have a table attached to a csv file why would you want to do that?)

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.





_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Import data into a temporary table

D Burgess
The big downside of the csv extension, is that no matter what you do your
table ends up with every column with a type of text.
A trap if you are you are using union/except/intersect clauses.


On Fri, Mar 8, 2019 at 11:00 AM Keith Medcalf <[hidden email]> wrote:

>
> On Thursday, 7 March, 2019 14:45, Eric Tsau <[hidden email]> asked:
>
> >Is it possible to add the option of importing data into a temporary
> >table?
>
> >Currently you have to create a temporary table first before importing
> >to it, or having to drop the table afterwards.
>
> >.import dump.csv temp.table
> >or
> >.import dump.csv attach.table
>
> Apparently not ;)  It would appear that the .import shell command does not
> know how to create tables in schema's other than "main"
>
> However, you can create a virtual table in the temp database using the csv
> extension (since .import only works from the sqlite3 shell) ...
>
> create virtual table temp.tablename using csv(filename=filename.csv,
> header=yes);
>
> The virtual table will be deleted when the connection is closed ... since
> it is only a temporary thing.  You could then create a duplicate
> materialized table if you wished (but since you have a table attached to a
> csv file why would you want to do that?)
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Import data into a temporary table

Keith Medcalf
In reply to this post by Keith Medcalf

I should have said that "It appears that you are correct, the .import shell command does not know how to create a table in other than the default "main" schema" and that you are requesting this be changed so that the table being imported into does get created in the specified schema if a the table needs to be created.

The csv extension is a work-around.  I can see that you might want to materialize this table so that you can create indexes on it, or so that the query planner can create temporary indexes if they will help speed up a query.  You could do this (for example) as follows:

create virtual table temp.csvimport using csv(filename=test.csv, header=yes);
create temporary table blahblah as select * from temp.csvimport;
drop table temp.csvimport;

You will have to build the csv extension yourself.  It can be found here:

https://www.sqlite.org/src/artifact/7f047aeb68f5802e

(as file csv.c under ext/misc in the source distribution)

SQLite version 3.28.0 2019-03-05 23:49:17
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create virtual table temp.csvimport using csv(filename=test.csv, header=yes);
sqlite> create temporary table blahblah as select * from temp.csvimport;
sqlite> .tables
temp.blahblah   temp.csvimport
sqlite> drop table temp.csvimport;
sqlite> .mode col
sqlite> .head on
sqlite> select * from blahblah;
a           b
----------  ----------
1           2
2           3
sqlite>

where test.csv contains:

a,b
1,2
2,3

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Import data into a temporary table

Dianne Dunn
In reply to this post by Keith Medcalf
Not sure how I got this please remove me

Sent from my iPad

> On Mar 7, 2019, at 4:00 PM, Keith Medcalf <[hidden email]> wrote:
>
>
> On Thursday, 7 March, 2019 14:45, Eric Tsau <[hidden email]> asked:
>
>> Is it possible to add the option of importing data into a temporary
>> table?
>
>> Currently you have to create a temporary table first before importing
>> to it, or having to drop the table afterwards.
>
>> .import dump.csv temp.table
>> or
>> .import dump.csv attach.table
>
> Apparently not ;)  It would appear that the .import shell command does not know how to create tables in schema's other than "main"
>
> However, you can create a virtual table in the temp database using the csv extension (since .import only works from the sqlite3 shell) ...
>
> create virtual table temp.tablename using csv(filename=filename.csv, header=yes);
>
> The virtual table will be deleted when the connection is closed ... since it is only a temporary thing.  You could then create a duplicate materialized table if you wished (but since you have a table attached to a csv file why would you want to do that?)
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.
>
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Import data into a temporary table

Keith Medcalf
In reply to this post by D Burgess
On Thursday, 7 March, 2019 17:12, D Burgess <[hidden email]> wrote:

>The big downside of the csv extension, is that no matter what you do
>your table ends up with every column with a type of text.
>A trap if you are you are using union/except/intersect clauses.

You are more or less but not entirely correct.  If you materialize a csv virtual table for which you have specified appropriate column affinities, then those affinities are applied to the materialized copy (but not the rows in the virtual table).  So not quite "nothing you can do".  Granted, there is "nothing you can do" to make the virtual table not return text, but then again, csv files are text files ...

SQLite version 3.28.0 2019-03-05 23:49:17
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .read source/sqlite/schema.sql
sqlite> create virtual table csv1 using csv(filename=test.csv, header=yes);
sqlite> create table test1 as select * from csv1;
sqlite> create virtual table csv2 using csv(filename=test.csv, header=yes, schema='create table csv2 (a integer, b float)');
sqlite> create table test2 as select * from csv2;
sqlite> .head on
sqlite> select * from syscolumns where ObjectType == 'table';
ObjectType|ObjectName|ColumnID|ColumnName|Type|Affinity|isNotNull|DefaultValue|isPrimaryKey
table|csv1|0|a|TEXT|Text|0||0
table|csv1|1|b|TEXT|Text|0||0
table|csv2|0|a|integer|Integer|0||0
table|csv2|1|b|float|Real|0||0
table|test1|0|a|TEXT|Text|0||0
table|test1|1|b|TEXT|Text|0||0
table|test2|0|a|INT|Integer|0||0
table|test2|1|b|REAL|Real|0||0
sqlite> select a, typeof(a), b, typeof(b) from csv1;
a|typeof(a)|b|typeof(b)
1|text|2|text
2|text|3|text
sqlite> select a, typeof(a), b, typeof(b) from csv2;
a|typeof(a)|b|typeof(b)
1|text|2|text
2|text|3|text
sqlite> select a, typeof(a), b, typeof(b) from test1;
a|typeof(a)|b|typeof(b)
1|text|2|text
2|text|3|text
sqlite> select a, typeof(a), b, typeof(b) from test2;
a|typeof(a)|b|typeof(b)
1|integer|2.0|real
2|integer|3.0|real
sqlite>

>On Fri, Mar 8, 2019 at 11:00 AM Keith Medcalf <[hidden email]>
>wrote:
>
>>
>> On Thursday, 7 March, 2019 14:45, Eric Tsau <[hidden email]>
>asked:
>>
>> >Is it possible to add the option of importing data into a
>temporary
>> >table?
>>
>> >Currently you have to create a temporary table first before
>importing
>> >to it, or having to drop the table afterwards.
>>
>> >.import dump.csv temp.table
>> >or
>> >.import dump.csv attach.table
>>
>> Apparently not ;)  It would appear that the .import shell command
>does not
>> know how to create tables in schema's other than "main"
>>
>> However, you can create a virtual table in the temp database using
>the csv
>> extension (since .import only works from the sqlite3 shell) ...
>>
>> create virtual table temp.tablename using
>csv(filename=filename.csv,
>> header=yes);
>>
>> The virtual table will be deleted when the connection is closed ...
>since
>> it is only a temporary thing.  You could then create a duplicate
>> materialized table if you wished (but since you have a table
>attached to a
>> csv file why would you want to do that?)
>>
>> ---
>> The fact that there's a Highway to Hell but only a Stairway to
>Heaven says
>> a lot about anticipated traffic volume.
>>
>>
>>
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Import data into a temporary table

Hick Gunter
In reply to this post by Dianne Dunn
Follow the link found at the bottom of every message and remove yourself from the list

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Dianne Dunn
Gesendet: Freitag, 08. März 2019 01:22
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] Re: [sqlite] Import data into a temporary table

Not sure how I got this please remove me

Sent from my iPad

> On Mar 7, 2019, at 4:00 PM, Keith Medcalf <[hidden email]> wrote:
>
>
> On Thursday, 7 March, 2019 14:45, Eric Tsau <[hidden email]> asked:
>
>> Is it possible to add the option of importing data into a temporary
>> table?
>
>> Currently you have to create a temporary table first before importing
>> to it, or having to drop the table afterwards.
>
>> .import dump.csv temp.table
>> or
>> .import dump.csv attach.table
>
> Apparently not ;)  It would appear that the .import shell command does not know how to create tables in schema's other than "main"
>
> However, you can create a virtual table in the temp database using the csv extension (since .import only works from the sqlite3 shell) ...
>
> create virtual table temp.tablename using csv(filename=filename.csv,
> header=yes);
>
> The virtual table will be deleted when the connection is closed ...
> since it is only a temporary thing.  You could then create a duplicate
> materialized table if you wished (but since you have a table attached
> to a csv file why would you want to do that?)
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.
>
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

where did my data go ? Re: Import data into a temporary table

Luuk
In reply to this post by Eric Tsau
On 7-3-2019 22:45, Eric Tsau wrote:

> Hi,
> Is it possible to add the option of importing data into a temporary table?
> Currently you have to create a temporary table first before importing to
> it, or having to drop the table afterwards.
> .import dump.csv temp.table
> or
> .import dump.csv attach.table
>
> Regards
> Eric
> C:\TEMP>del test.sqlite


C:\TEMP>type abc.csv
a,b,c
1,2,3
4,5,6
7,8,9

C:\TEMP>sqlite3 test.sqlite
SQLite version 3.27.1 2019-02-08 13:17:39
Enter ".help" for usage hints.
sqlite> .import abc.csv test
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(
   "a,b,c" TEXT
);
INSERT INTO test VALUES('1,2,3');
INSERT INTO test VALUES('4,5,6');
INSERT INTO test VALUES('7,8,9');
COMMIT;
sqlite>
sqlite> .import abc.csv temp.test
Error: no such table: temp.test
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(
   "a,b,c" TEXT
);
COMMIT;
sqlite> .quit


Where did my data go (see above)?

Luckily it's there when i restart sqlite3.exe:



C:\TEMP>sqlite3 test.sqlite
SQLite version 3.27.1 2019-02-08 13:17:39
Enter ".help" for usage hints.
sqlite> select * from test;
1,2,3
4,5,6
7,8,9
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(
   "a,b,c" TEXT
);
INSERT INTO test VALUES('1,2,3');
INSERT INTO test VALUES('4,5,6');
INSERT INTO test VALUES('7,8,9');
COMMIT;
sqlite>


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] where did my data go ? Re: Import data into a temporary table

Hick Gunter
Are you aware of the fact that your csv file describes a table containting three columns (name a, b, and c) whereas your SQL describes a single column named a,b,c with embedded commas in the values too?

Also please note that a temp table is disposed of when the connection is closed.

So what you are seeing is an empty temp table shadowing a main table of the same name. Try to avoid giving tables in different databases the same names. The exact same statement will mean different things depending on the order of attaching the databases unless all names are qualified.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Luuk
Gesendet: Samstag, 09. März 2019 10:32
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] where did my data go ? Re: Import data into a temporary table

On 7-3-2019 22:45, Eric Tsau wrote:

> Hi,
> Is it possible to add the option of importing data into a temporary table?
> Currently you have to create a temporary table first before importing
> to it, or having to drop the table afterwards.
> .import dump.csv temp.table
> or
> .import dump.csv attach.table
>
> Regards
> Eric
> C:\TEMP>del test.sqlite


C:\TEMP>type abc.csv
a,b,c
1,2,3
4,5,6
7,8,9

C:\TEMP>sqlite3 test.sqlite
SQLite version 3.27.1 2019-02-08 13:17:39 Enter ".help" for usage hints.
sqlite> .import abc.csv test
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(
   "a,b,c" TEXT
);
INSERT INTO test VALUES('1,2,3');
INSERT INTO test VALUES('4,5,6');
INSERT INTO test VALUES('7,8,9');
COMMIT;
sqlite>
sqlite> .import abc.csv temp.test
Error: no such table: temp.test
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(
   "a,b,c" TEXT
);
COMMIT;
sqlite> .quit


Where did my data go (see above)?

Luckily it's there when i restart sqlite3.exe:



C:\TEMP>sqlite3 test.sqlite
SQLite version 3.27.1 2019-02-08 13:17:39 Enter ".help" for usage hints.
sqlite> select * from test;
1,2,3
4,5,6
7,8,9
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(
   "a,b,c" TEXT
);
INSERT INTO test VALUES('1,2,3');
INSERT INTO test VALUES('4,5,6');
INSERT INTO test VALUES('7,8,9');
COMMIT;
sqlite>


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] where did my data go ? Re: Import data into a temporary table

Hick Gunter
In reply to this post by Luuk
And omitting

.mode csv

is probably messing up the .import

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Luuk
Gesendet: Samstag, 09. März 2019 10:32
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] where did my data go ? Re: Import data into a temporary table

On 7-3-2019 22:45, Eric Tsau wrote:

> Hi,
> Is it possible to add the option of importing data into a temporary table?
> Currently you have to create a temporary table first before importing
> to it, or having to drop the table afterwards.
> .import dump.csv temp.table
> or
> .import dump.csv attach.table
>
> Regards
> Eric
> C:\TEMP>del test.sqlite


C:\TEMP>type abc.csv
a,b,c
1,2,3
4,5,6
7,8,9

C:\TEMP>sqlite3 test.sqlite
SQLite version 3.27.1 2019-02-08 13:17:39 Enter ".help" for usage hints.
sqlite> .import abc.csv test
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(
   "a,b,c" TEXT
);
INSERT INTO test VALUES('1,2,3');
INSERT INTO test VALUES('4,5,6');
INSERT INTO test VALUES('7,8,9');
COMMIT;
sqlite>
sqlite> .import abc.csv temp.test
Error: no such table: temp.test
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(
   "a,b,c" TEXT
);
COMMIT;
sqlite> .quit


Where did my data go (see above)?

Luckily it's there when i restart sqlite3.exe:



C:\TEMP>sqlite3 test.sqlite
SQLite version 3.27.1 2019-02-08 13:17:39 Enter ".help" for usage hints.
sqlite> select * from test;
1,2,3
4,5,6
7,8,9
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(
   "a,b,c" TEXT
);
INSERT INTO test VALUES('1,2,3');
INSERT INTO test VALUES('4,5,6');
INSERT INTO test VALUES('7,8,9');
COMMIT;
sqlite>


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users