mode insert dumps

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

mode insert dumps

Luca Ferrari-2
Hi,
I've got a lot of database files that I would like to dump using
INSERT statements.
unluckily, .mode insert does not work for me, or I'm not able to
understand how it works.

sqlite> select mude_anno, mude_numero from catdaemo;
INSERT INTO table VALUES(2019,1161);

My questions are:
1) why is the insert operating against "table" instead of the real
table I was selecting from (catdaemo)?
2) is there a way to force the INSER to have also the columns I
selected listed? I would like something like:
INSERT INTO catadaemo( mude_anno, mude_numero ) VALUES(2019,1161);

The reason is that I want to exclude automatic keys from my inserts
because I'm going to pump those inserts into another database with
automatic keys.

Thanks,
Luca
_______________________________________________
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: mode insert dumps

Dan Kennedy-4

On 2/8/62 15:04, Luca Ferrari wrote:

> Hi,
> I've got a lot of database files that I would like to dump using
> INSERT statements.
> unluckily, .mode insert does not work for me, or I'm not able to
> understand how it works.
>
> sqlite> select mude_anno, mude_numero from catdaemo;
> INSERT INTO table VALUES(2019,1161);
>
> My questions are:
> 1) why is the insert operating against "table" instead of the real
> table I was selecting from (catdaemo)?

You can add a table name to the ".mode insert":

   sqlite3> .mode insert catdaemo

> 2) is there a way to force the INSER to have also the columns I
> selected listed? I would like something like:
> INSERT INTO catadaemo( mude_anno, mude_numero ) VALUES(2019,1161);

Try doing:

   sqlite3> .headers on

along with the ".mode insert" command.

Dan.


_______________________________________________
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] mode insert dumps

Hick Gunter
In reply to this post by Luca Ferrari-2
You need to

.mode insert <tablename>
SELECT * FROM <tablename>;

Repeat for all your tables.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Luca Ferrari
Gesendet: Freitag, 02. August 2019 10:04
An: SQLite <[hidden email]>
Betreff: [EXTERNAL] [sqlite] mode insert dumps

Hi,
I've got a lot of database files that I would like to dump using INSERT statements.
unluckily, .mode insert does not work for me, or I'm not able to understand how it works.

sqlite> select mude_anno, mude_numero from catdaemo;
INSERT INTO table VALUES(2019,1161);

My questions are:
1) why is the insert operating against "table" instead of the real table I was selecting from (catdaemo)?
2) is there a way to force the INSER to have also the columns I selected listed? I would like something like:
INSERT INTO catadaemo( mude_anno, mude_numero ) VALUES(2019,1161);

The reason is that I want to exclude automatic keys from my inserts because I'm going to pump those inserts into another database with automatic keys.

Thanks,
Luca
_______________________________________________
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: mode insert dumps

Olivier Mascia
In reply to this post by Luca Ferrari-2
> Le 2 août 2019 à 10:04, Luca Ferrari <[hidden email]> a écrit :
>
> I've got a lot of database files that I would like to dump using
> INSERT statements.
> unluckily, .mode insert does not work for me, or I'm not able to
> understand how it works.
>
> sqlite> select mude_anno, mude_numero from catdaemo;
> INSERT INTO table VALUES(2019,1161);

Besides the other answers focused on using .mode insert more precisely, the .dump command might prove useful too.

—  
Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten Grüßen,
Olivier Mascia
https://www.integral.be


_______________________________________________
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: mode insert dumps

Luca Ferrari-2
In reply to this post by Dan Kennedy-4
On Fri, Aug 2, 2019 at 10:17 AM Dan Kennedy <[hidden email]> wrote:
>    sqlite3> .headers on

Yes, but I forgot to mention that I'm using sqlite 3.7.17 (back from
2013), so this option is not working.
However, since I've tested it works on recent versions, I think I
would copy all the databases on a machine with a more recent version
and use such version for the dump.

Thanks,
Luca
_______________________________________________
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: mode insert dumps

Luca Ferrari-2
In reply to this post by Olivier Mascia
On Fri, Aug 2, 2019 at 12:03 PM Olivier Mascia <[hidden email]> wrote:
> Besides the other answers focused on using .mode insert more precisely, the .dump command might prove useful too.

Yes, but dump will dump all the columns, including autoincrement, that
is something I want to avoid in the final output.

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