Re: [sqlite] How to skip the first field on .import

[Ted Rolle]

On Fri, 30 Oct 2009 12:17:53 -0700
Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> Ted Rolle wrote:
> > How do I let this start out at the default value and auto increment?
> > My column separator is '|'.
> 
> Create a temporary table and import into that.  Then copy those
> values into your permanent table using something like:
> 
>   insert into perm_table select null,* from temp_table;
> 
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
> 
> iEYEARECAAYFAkrrO90ACgkQmOOfHg372QRNgQCgq/obmjL/Rw862bsqk9GIU4FE
> yQYAoLc3JEugW75ZSGPZADTuZNC5Ruww
> =CZTr
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

I keep baing amazed at SQLite!  The automagically-generated rowid is
what I needed all along.  It comes along for the ride.  I didn't have
to change a thing.

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

Re: [sqlite] How to skip the first field on .import

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Ted Rolle wrote:
> How do I let this start out at the default value and auto increment?
> My column separator is '|'.

Create a temporary table and import into that.  Then copy those values into
your permanent table using something like:

  insert into perm_table select null,* from temp_table;

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkrrO90ACgkQmOOfHg372QRNgQCgq/obmjL/Rw862bsqk9GIU4FE
yQYAoLc3JEugW75ZSGPZADTuZNC5Ruww
=CZTr
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to skip the first field on .import

[Griggs, Donald]

 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ted Rolle
Sent: Friday, October 30, 2009 10:36 AM
To: sqlite-users
Subject: [sqlite] How to skip the first field on .import

The first field in my table is ID primary integer autoincrement.

=> Do you instead mean "INTEGER PRIMARY KEY AUTOINCREMENT" ?

I read that if it is set to NULL it defaults to the maximum value
possible.  Not a Good Thing(tm).

=> Can you post the url where you read this?   From page 
http://www.sqlite.org/autoinc.html
it says something quite different:

If no ROWID is specified on the insert, an appropriate ROWID is
created automatically. The usual algorithm is to give the newly created
row a ROWID that is one larger than the largest ROWID in the table prior
to the insert. If the table is initially empty, then a ROWID of 1 is
used. If the largest ROWID is equal to the largest possible integer
(9223372036854775807) then the database engine starts picking candidate
ROWIDs at random until it finds one that is not previously used. 



How do I let this start out at the default value and auto increment?
My column separator is '|'.

=>  I'm guessing you're using the command-line utility program.
Others may have better answers, but:
  -- If you're restoring a table you've dumped previously, then you
probably want the primary key values to be set explicitly to their
previous values -- i.e., you don't want them to take on new values.
  -- If this is an initial data import, one way to do this is to
".import" to a temporary table withOUT the ID key, then use an sql
command to transfer the data to the permanent table (which DOES use
INTEGER PRIMARY KEY AUTOINCREMENT), e.g.
INSERT INTO myPerm 
  SELECT NULL, * FROM myTemp

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

Re: [sqlite] How to skip the first field on .import

[P Kishor]

On Fri, Oct 30, 2009 at 9:35 AM, Ted Rolle  wrote:
> The first field in my table is ID primary integer autoincrement.

First, change the above to

ID INTEGER PRIMARY KEY


> I read that if it is set to NULL it defaults to the maximum value
> possible.  Not a Good Thing(tm).

I have no idea where you read that, and what the above means, but look
at the following --

SQLite version 3.6.19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE foo (a INTEGER PRIMARY KEY, b TEXT);
sqlite> INSERT INTO foo (a, b) VALUES (1, 'blah');
sqlite> INSERT INTO foo (b) VALUES ('more blah');
sqlite> INSERT INTO foo (a, b) VALUES (NULL, 'even more blah');
sqlite> SELECT * FROM foo;
a   b
--  --
1   blah
2   more blah
3   even more
sqlite>


I didn't get any "maximum value possible"


>
> How do I let this start out at the default value and auto increment?
> My column separator is '|'.
>
> Ted
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users