Re: [sqlite] copy data from one db to another

[Vivien Malerba]

On 9 June 2010 20:44, Rich Shepard  wrote:
> On Wed, 9 Jun 2010, Vivien Malerba wrote:
>
>>> I forgot to mention, the source is a PostgreSQL db, not SQLite, so
>>> there's no source file to copy.  Though a backup might be
>>> interesting ...
>
>> You can use Libgda's gda-sql tool in which you can:
>> * open a connection to the PostgreSQL db (for example named db1)
>> * open a connection to the SQLite db (for example named db2)
>> * bind those 2 connections into a 3rd one, and execute statements like
>> "insert into db2.table_one_name select * from db1.table_one_name ;"
>
>   Why not do a database dump from postgres, then read the .sql file into
> SQlite? As long as you use standard SQL in the data development language
> (DDL) you'll get ASCII SQL files for each table's schema with INSERT
> statements for each row of each table.

Because it's easier to set up, you won't have any problem with dates,
and you can do much more complicated statements (for example to do
comparisons, partial updates, ...)

Vivien
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] copy data from one db to another

[Rich Shepard]

On Wed, 9 Jun 2010, Vivien Malerba wrote:

>> I forgot to mention, the source is a PostgreSQL db, not SQLite, so
>> there's no source file to copy.  Though a backup might be
>> interesting ...

> You can use Libgda's gda-sql tool in which you can:
> * open a connection to the PostgreSQL db (for example named db1)
> * open a connection to the SQLite db (for example named db2)
> * bind those 2 connections into a 3rd one, and execute statements like
> "insert into db2.table_one_name select * from db1.table_one_name ;"

   Why not do a database dump from postgres, then read the .sql file into
SQlite? As long as you use standard SQL in the data development language
(DDL) you'll get ASCII SQL files for each table's schema with INSERT
statements for each row of each table.

Rich
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] copy data from one db to another

[Vivien Malerba]

On 9 June 2010 18:58, Scott Frankel  wrote:
>
> On Jun 9, 2010, at 12:22 AM, Vivien Malerba wrote:
>
>> On 8 June 2010 22:02, Scott Frankel  wrote:
>>>
>>> On Jun 8, 2010, at 12:51 PM, Jean-Christophe Deschamps wrote:
>>>

> What's the best way to copy data from one db to another?
>
> Given 2 databases with identical schemas, one full of data and the
> other empty, the brute force way would be to perform selects on the
> source db, then for each row, perform an insert into the
> destination
> db.  Is there a more efficient way?

 The easiest is either to simply copy the file as Igor suggested or
 use
 the backup API (very easy too).
>>>
>>> I forgot to mention, the source is a PostgreSQL db, not SQLite, so
>>> there's no source file to copy.  Though a backup might be
>>> interesting ...
>>
>> You can use Libgda's gda-sql tool in which you can:
>> * open a connection to the PostgreSQL db (for example named db1)
>> * open a connection to the SQLite db (for example named db2)
>> * bind those 2 connections into a 3rd one, and execute statements like
>> "insert into db2.table_one_name select * from db1.table_one_name ;"
>
> While Libgda looks very interesting, I need a solution that's
> accessible from common Linux, OSX, and Windows base installs.

Libgda (and associated tools) is available for those 3 OSes.

Regards,

Vivien
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] copy data from one db to another

[Black, Michael (IS)]

Assuming postgres can load SQL from a file you should be able to use named 
pipes on all 3.
 
Here's windows:
sqlite> create table t (i integer);
sqlite> insert into t values(1);
sqlite> insert into t values(2);
sqlite> .output \\.\pipe\foo
sqlite> .dump
 
2nd window:
sqlite> .read \\.\pipe\foo
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t (i integer);
INSERT INTO "t" VALUES(1);
INSERT INTO "t" VALUES(2);
COMMIT;
 
OSX and LInux named pipes are probably more familair to people just using 
"mkfifo".
http://www.macosxhints.com/article.php?story=20041025103920992
 
so:
mkfifo foo
sqlite> create table t (i integer);
sqlite> insert into t values(1);
sqlite> insert into t values(2);
sqlite> .output foo  
sqlite> .dump
 
2nd window:
sqlite> .read foo  

 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Scott Frankel
Sent: Wed 6/9/2010 11:58 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] copy data from one db to another




On Jun 9, 2010, at 12:22 AM, Vivien Malerba wrote:

> On 8 June 2010 22:02, Scott Frankel <fran...@circlesfx.com> wrote:
>>
>> On Jun 8, 2010, at 12:51 PM, Jean-Christophe Deschamps wrote:
>>
>>>
>>>> What's the best way to copy data from one db to another?
>>>>
>>>> Given 2 databases with identical schemas, one full of data and the
>>>> other empty, the brute force way would be to perform selects on the
>>>> source db, then for each row, perform an insert into the 
>>>> destination
>>>> db.  Is there a more efficient way?
>>>
>>> The easiest is either to simply copy the file as Igor suggested or 
>>> use
>>> the backup API (very easy too).
>>
>> I forgot to mention, the source is a PostgreSQL db, not SQLite, so
>> there's no source file to copy.  Though a backup might be
>> interesting ...
>
> You can use Libgda's gda-sql tool in which you can:
> * open a connection to the PostgreSQL db (for example named db1)
> * open a connection to the SQLite db (for example named db2)
> * bind those 2 connections into a 3rd one, and execute statements like
> "insert into db2.table_one_name select * from db1.table_one_name ;"

While Libgda looks very interesting, I need a solution that's 
accessible from common Linux, OSX, and Windows base installs.  Looks 
like the record-by-record approach is the best option for now.

Thanks
Scott



>
> If you want more info, tell me.
>
> Regards,
>
> Vivien
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] copy data from one db to another

[Scott Frankel]

On Jun 9, 2010, at 12:22 AM, Vivien Malerba wrote:

> On 8 June 2010 22:02, Scott Frankel  wrote:
>>
>> On Jun 8, 2010, at 12:51 PM, Jean-Christophe Deschamps wrote:
>>
>>>
 What's the best way to copy data from one db to another?

 Given 2 databases with identical schemas, one full of data and the
 other empty, the brute force way would be to perform selects on the
 source db, then for each row, perform an insert into the  
 destination
 db.  Is there a more efficient way?
>>>
>>> The easiest is either to simply copy the file as Igor suggested or  
>>> use
>>> the backup API (very easy too).
>>
>> I forgot to mention, the source is a PostgreSQL db, not SQLite, so
>> there's no source file to copy.  Though a backup might be
>> interesting ...
>
> You can use Libgda's gda-sql tool in which you can:
> * open a connection to the PostgreSQL db (for example named db1)
> * open a connection to the SQLite db (for example named db2)
> * bind those 2 connections into a 3rd one, and execute statements like
> "insert into db2.table_one_name select * from db1.table_one_name ;"

While Libgda looks very interesting, I need a solution that's  
accessible from common Linux, OSX, and Windows base installs.  Looks  
like the record-by-record approach is the best option for now.

Thanks
Scott



>
> If you want more info, tell me.
>
> Regards,
>
> Vivien
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] copy data from one db to another

[Vivien Malerba]

On 8 June 2010 22:02, Scott Frankel  wrote:
>
> On Jun 8, 2010, at 12:51 PM, Jean-Christophe Deschamps wrote:
>
>>
>>> What's the best way to copy data from one db to another?
>>>
>>> Given 2 databases with identical schemas, one full of data and the
>>> other empty, the brute force way would be to perform selects on the
>>> source db, then for each row, perform an insert into the destination
>>> db.  Is there a more efficient way?
>>
>> The easiest is either to simply copy the file as Igor suggested or use
>> the backup API (very easy too).
>
> I forgot to mention, the source is a PostgreSQL db, not SQLite, so
> there's no source file to copy.  Though a backup might be
> interesting ...

You can use Libgda's gda-sql tool in which you can:
* open a connection to the PostgreSQL db (for example named db1)
* open a connection to the SQLite db (for example named db2)
* bind those 2 connections into a 3rd one, and execute statements like
"insert into db2.table_one_name select * from db1.table_one_name ;"

If you want more info, tell me.

Regards,

Vivien
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] copy data from one db to another

[Simon Slavin]

On 8 Jun 2010, at 10:47pm, Scott Frankel wrote:

> On Jun 8, 2010, at 2:28 PM, Simon Slavin wrote:
> 
>> Either write a program to read record-by-record and write record-by- 
>> record, or use PostgreSQL functions to write to SQL commands then  
>> execute those commands to create a new SQLite database.
> 
> Before heading down the path of issuing record-by-record statements, I  
> thought there might be some hope with generic SQL commands or bulk  
> importing ... a newbie's hope ;)

SQLite includes a command-line tool which can be used to dump a database to a 
text file of SQL commands, or read the text-file and use it to create a new 
database.  If PostgreSQL has an equivalent tool, you can do it all without 
writing a line of code.  Better still, you'll have the text file which will act 
as an excellent backup of your data in case something goes wrong.  (You may 
have to use a text editor to make minor changes to the format of the SQL 
commands.)

When I backup any SQL database for long-term archive purposes I always back it 
up as SQL commands, never in its native file format.  This means I don't have 
to worry about trying to find some software to read an obsolete file format.  
(Assuming that there will be software which can read a .zip file for the 
foreseeable future.)

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] copy data from one db to another

[Scott Frankel]

On Jun 8, 2010, at 2:28 PM, Simon Slavin wrote:

>
> On 8 Jun 2010, at 9:02pm, Scott Frankel wrote:
>
>> On Jun 8, 2010, at 12:51 PM, Jean-Christophe Deschamps wrote:
>>
 What's the best way to copy data from one db to another?

 Given 2 databases with identical schemas, one full of data and the
 other empty, the brute force way would be to perform selects on the
 source db, then for each row, perform an insert into the  
 destination
 db.  Is there a more efficient way?
>>>
>>> The easiest is either to simply copy the file as Igor suggested or  
>>> use
>>> the backup API (very easy too).
>>
>> I forgot to mention, the source is a PostgreSQL db, not SQLite, so
>> there's no source file to copy.
>
> Then you have to use the functions of one library to read your data  
> and the functions of another to write your data.  There's no way to  
> do it inside one SQL command since the SQLite library can't read a  
> PostgreSQL database and the PostgreSQL library can't write a SQLite  
> database.

Right.


> Either write a program to read record-by-record and write record-by- 
> record, or use PostgreSQL functions to write to SQL commands then  
> execute those commands to create a new SQLite database.

Before heading down the path of issuing record-by-record statements, I  
thought there might be some hope with generic SQL commands or bulk  
importing ... a newbie's hope ;)

Thanks for the info!
Scott



>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] copy data from one db to another

[Simon Slavin]

On 8 Jun 2010, at 9:02pm, Scott Frankel wrote:

> On Jun 8, 2010, at 12:51 PM, Jean-Christophe Deschamps wrote:
> 
>>> What's the best way to copy data from one db to another?
>>> 
>>> Given 2 databases with identical schemas, one full of data and the
>>> other empty, the brute force way would be to perform selects on the
>>> source db, then for each row, perform an insert into the destination
>>> db.  Is there a more efficient way?
>> 
>> The easiest is either to simply copy the file as Igor suggested or use
>> the backup API (very easy too).
> 
> I forgot to mention, the source is a PostgreSQL db, not SQLite, so  
> there's no source file to copy.

Then you have to use the functions of one library to read your data and the 
functions of another to write your data.  There's no way to do it inside one 
SQL command since the SQLite library can't read a PostgreSQL database and the 
PostgreSQL library can't write a SQLite database.

Either write a program to read record-by-record and write record-by-record, or 
use PostgreSQL functions to write to SQL commands then execute those commands 
to create a new SQLite database.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] copy data from one db to another

[Adam DeVita]

The db that you open your initial connection to is called main by default. I
haven't had the occasion to use a temp or memory db so I can't comment.

The attach statement works as normal SQL.
attach 'path to your db' as 'some_alias_name'

like
attach 'c:\temp dir\db2.db' as 'db2'

Suppose both files have a table named 'some_table'.

select * from db2.some_table ; /*refers to the attached db*/
select * from main.some_table ; /*refers to the db you first made a
connection with.*/

I find the command line tool wonderful for testing out syntax.

Happy Computing

Adam


On Tue, Jun 8, 2010 at 4:09 PM, Scott Frankel  wrote:

>
> On Jun 8, 2010, at 12:07 PM, Adam DeVita wrote:
>
> > start by doing an open db1 (as main)
> > then attach path to db2 as 'db2'
> >
> > insert into main.table_one_name select * from db2.table_one_name ;
> >
> > This selects all records from db2 and puts them into db1 in one
> > statement.
>
> I've been reading about the ATTACH DATABASE cmd, but was confused by
> the documentation's warnings about main and temp dbs, namings, and
> transactions using :memory:.  I'll take a closer look.
>
> Thanks!
> Scott
>
>
>
>
> >
> > Adam
> > On Tue, Jun 8, 2010 at 3:02 PM, Scott Frankel
> >  wrote:
> >
> >>
> >> Hi all,
> >>
> >> What's the best way to copy data from one db to another?
> >>
> >> Given 2 databases with identical schemas, one full of data and the
> >> other empty, the brute force way would be to perform selects on the
> >> source db, then for each row, perform an insert into the destination
> >> db.  Is there a more efficient way?
> >>
> >> Thanks in advance!
> >> Scott
> >>
> >>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> >
> >
> > --
> > VerifEye Technologies Inc.
> > 905-948-0015x245
> > 7100 Warden Ave, Unit 3
> > Markham ON, L3R 8B5
> > Canada
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] copy data from one db to another

[Scott Frankel]

On Jun 8, 2010, at 12:07 PM, Adam DeVita wrote:

> start by doing an open db1 (as main)
> then attach path to db2 as 'db2'
>
> insert into main.table_one_name select * from db2.table_one_name ;
>
> This selects all records from db2 and puts them into db1 in one  
> statement.

I've been reading about the ATTACH DATABASE cmd, but was confused by  
the documentation's warnings about main and temp dbs, namings, and  
transactions using :memory:.  I'll take a closer look.

Thanks!
Scott




>
> Adam
> On Tue, Jun 8, 2010 at 3:02 PM, Scott Frankel  
>  wrote:
>
>>
>> Hi all,
>>
>> What's the best way to copy data from one db to another?
>>
>> Given 2 databases with identical schemas, one full of data and the
>> other empty, the brute force way would be to perform selects on the
>> source db, then for each row, perform an insert into the destination
>> db.  Is there a more efficient way?
>>
>> Thanks in advance!
>> Scott
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> -- 
> VerifEye Technologies Inc.
> 905-948-0015x245
> 7100 Warden Ave, Unit 3
> Markham ON, L3R 8B5
> Canada
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] copy data from one db to another

[Scott Frankel]

On Jun 8, 2010, at 12:51 PM, Jean-Christophe Deschamps wrote:

>
>> What's the best way to copy data from one db to another?
>>
>> Given 2 databases with identical schemas, one full of data and the
>> other empty, the brute force way would be to perform selects on the
>> source db, then for each row, perform an insert into the destination
>> db.  Is there a more efficient way?
>
> The easiest is either to simply copy the file as Igor suggested or use
> the backup API (very easy too).

I forgot to mention, the source is a PostgreSQL db, not SQLite, so  
there's no source file to copy.  Though a backup might be  
interesting ...



>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] copy data from one db to another

[Jean-Christophe Deschamps]

>What's the best way to copy data from one db to another?
>
>Given 2 databases with identical schemas, one full of data and the
>other empty, the brute force way would be to perform selects on the
>source db, then for each row, perform an insert into the destination
>db.  Is there a more efficient way?

The easiest is either to simply copy the file as Igor suggested or use 
the backup API (very easy too).


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] copy data from one db to another

[Igor Tandetnik]

Scott Frankel  wrote:
> What's the best way to copy data from one db to another?
> 
> Given 2 databases with identical schemas, one full of data and the
> other empty

Why not just copy the whole file over?
-- 
Igor Tandetnik


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] copy data from one db to another

[Adam DeVita]

start by doing an open db1 (as main)
then attach path to db2 as 'db2'

insert into main.table_one_name select * from db2.table_one_name ;

This selects all records from db2 and puts them into db1 in one statement.

Adam
On Tue, Jun 8, 2010 at 3:02 PM, Scott Frankel  wrote:

>
> Hi all,
>
> What's the best way to copy data from one db to another?
>
> Given 2 databases with identical schemas, one full of data and the
> other empty, the brute force way would be to perform selects on the
> source db, then for each row, perform an insert into the destination
> db.  Is there a more efficient way?
>
> Thanks in advance!
> Scott
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] copy data from one db to another

[Scott Frankel]

Hi all,

What's the best way to copy data from one db to another?

Given 2 databases with identical schemas, one full of data and the  
other empty, the brute force way would be to perform selects on the  
source db, then for each row, perform an insert into the destination  
db.  Is there a more efficient way?

Thanks in advance!
Scott


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users