Qn on moving data from Temp Table to Main Table

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

Qn on moving data from Temp Table to Main Table

R S-2
Hi,
The Temp Table (obviously) mimics the Main Table. The Main Table has a
column, recordNo which is a integer primary key (which autoincrements). Now
I periodically move Data from the Temp into the Main Table (and then delete
records from the Temp Table). It succeeds the first time, but fails
subsequently because of duplicate recordNo values (I presume).
Do I have to maintain the last recordNo value in the Main Table and pass it
to the Temp Table so that it starts using those values during insertion to
avoid a clash? Is there a better way of working around this problem?
Thanks!
Reply | Threaded
Open this post in threaded view
|

Re: Qn on moving data from Temp Table to Main Table

Clark Christensen
If you insert null into the main table as the value for recordNo (instead of the value from the temp table) when you transfer, you'll get a new recordNo value from main.  Of course, that means you'll have to change "select * from temp" in your insert stmt to a select that explicitly names each column (insert into main select null, col1, col2... from temp;)
 
  -Clark

----- Original Message ----
From: R S <[hidden email]>
To: [hidden email]
Sent: Saturday, October 08, 2005 15:24:28
Subject: [sqlite] Qn on moving data from Temp Table to Main Table

Hi,
The Temp Table (obviously) mimics the Main Table. The Main Table has a
column, recordNo which is a integer primary key (which autoincrements). Now
I periodically move Data from the Temp into the Main Table (and then delete
records from the Temp Table). It succeeds the first time, but fails
subsequently because of duplicate recordNo values (I presume).
Do I have to maintain the last recordNo value in the Main Table and pass it
to the Temp Table so that it starts using those values during insertion to
avoid a clash? Is there a better way of working around this problem?
Thanks!



Reply | Threaded
Open this post in threaded view
|

Re: Qn on moving data from Temp Table to Main Table

R S-2
Thanks Clark.

Another question, what if my Temp Table gets full (i.e. I am out of memory).
Would I get a SQLITE_NOMEM error?

If I am worried about running out of memory would a better approach be to
write into a Table in another DB and then move the contents of this table
into my Main Table by attaching the 2 DBs?
Thanks


On 10/9/05, Clark Christensen <[hidden email]> wrote:

>
> If you insert null into the main table as the value for recordNo (instead
> of the value from the temp table) when you transfer, you'll get a new
> recordNo value from main. Of course, that means you'll have to change
> "select * from temp" in your insert stmt to a select that explicitly names
> each column (insert into main select null, col1, col2... from temp;)
>
> -Clark
>
> ----- Original Message ----
> From: R S <[hidden email]>
> To: [hidden email]
> Sent: Saturday, October 08, 2005 15:24:28
> Subject: [sqlite] Qn on moving data from Temp Table to Main Table
>
> Hi,
> The Temp Table (obviously) mimics the Main Table. The Main Table has a
> column, recordNo which is a integer primary key (which autoincrements).
> Now
> I periodically move Data from the Temp into the Main Table (and then
> delete
> records from the Temp Table). It succeeds the first time, but fails
> subsequently because of duplicate recordNo values (I presume).
> Do I have to maintain the last recordNo value in the Main Table and pass
> it
> to the Temp Table so that it starts using those values during insertion to
> avoid a clash? Is there a better way of working around this problem?
> Thanks!
>
>
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Qn on moving data from Temp Table to Main Table

D. Richard Hipp
In reply to this post by R S-2
R S <[hidden email]> wrote:
> Thanks Clark.
>
> Another question, what if my Temp Table gets full (i.e. I am out of memory).
> Would I get a SQLITE_NOMEM error?
>

If you do PRAGMA temp_store=file then the TEMP tables are stored
on disk.  Actually, that's the default with the builds I put on
the website.  Though I just checked and it seems that if you build
SQLite yourself using the configure script, then TEMP tables
are stored in memory by default.  You will need to use the pragma to
store TEMP tables on disk.  This should probably be considered
a bug in the configure script.  (Later: fixed using check-in [2748])

If you use the --enable-tempstore=no option on the configure script,
it seems to make TEMP tables go to disk by default. (Later: This is
now the default.)

--
D. Richard Hipp <[hidden email]>