[sqlite] SQL Syntax To Copy A Table
I suggest to use the CloneTable function Have a look at https://www.gaia-gis.it/fossil/libspatialite/wiki?name=CloneTable It copies the table and all of its triggers, indexes, etc. Maurizio 2015-03-26 16:35 GMT+01:00, John McKown : > On Thu, Mar 26, 2015 at 10:29 AM, Nigel Verity > wrote: >> Hi >> >> I know this must seem a fairly dumb question, but I can't find an easy way >> to create a copy of table using just SQL. >> >> My requirement is to take periodic snapshots of a names and addresses >> table, to be stored in the same database as the master. >> >> The master table has one index - an auto-incrementing integer ID (non >> NULL, primary key). There is no need for the corresponding field in the >> snapshot to be indexed, but the integer ID does need to be copied across. >> >> I'm happy to copy the table structure in one operation and the data in >> another, but if it can be done in a single operation so much the better. > > You could use _two_ statements like: > > DROP TABLE IF EXISTS copy_of_bubba; > CREATE TABLE copy_of_bubba AS SELECT * FROM bubba; > >> >> Thanks >> >> Nige >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > -- > If you sent twitter messages while exploring, are you on a textpedition? > > He's about as useful as a wax frying pan. > > 10 to the 12th power microphones = 1 Megaphone > > Maranatha! <>< > John McKown > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Why is empty string not equal to empty blob?
On 29-3-2015 18:53, Simon Slavin wrote:
>
>> On 29 Mar 2015, at 5:31pm, Luuk wrote:
>>
>> On 19-3-2015 16:02, Simon Slavin wrote:
>>>
>>> One is a string. The other is a BLOB. SQLite doesn't even get as far as
>>> testing the contents, it knows they are of different types.
>>
>> C:\temp>sqlite3
>> SQLite version 3.8.8.3 2015-02-25 13:29:11
>> Enter ".help" for usage hints.
>> Connected to a transient in-memory database.
>> Use ".open FILENAME" to reopen on a persistent database.
>> sqlite> create table test (x string, y blob);
>> sqlite> insert into test values ('x','x');
>> sqlite> select * from test;
>> x|x
>> sqlite> select x,y, x=y from test where x=y;
>> x|x|1
>> sqlite>
>>
>> Can you comment on:
>> "SQLite doesn't even get as far as testing the contents, it knows they are
>> of different types."
>
> You explicitly put strings into both columns. The two values are the same
> even though you declared the columns differently. As I told you before, if
> you want to see what type something is, use typeof(thing).
>
> sqlite> create table testsb (x string, y blob);
> sqlite> insert into testsb values ('x', 'x');
> sqlite> select x, y, typeof(x), typeof(y), x=y from testsb;
> x|x|text|text|1
>
> So far, the same as you did. Now ...
>
> sqlite> insert into testsb values ('x', x'78');
> sqlite> insert into testsb values (x'78', x'78');
> sqlite> select x, y, typeof(x), typeof(y), x=y from testsb;
> x|x|text|text|1
> x|x|text|blob|0
> x|x|blob|blob|1
>
> Column type doesn't matter:
>
> sqlite> create table testbb (x blob, y blob);
> sqlite> insert into testbb values ('x', 'x');
> sqlite> insert into testbb values ('x', x'78');
> sqlite> insert into testbb values (x'78', x'78');
> sqlite> select x, y, typeof(x), typeof(y), x=y from testbb;
> x|x|text|text|1
> x|x|text|blob|0
> x|x|blob|blob|1
>
> It has just occurred to me that you might not know what in SQLite columns
> have affinities, not fixed types. You can store any type of value in any
> column. SQLite will convert one to another only on the route of TEXT -->
> FLOAT --> INTEGER. For more on this see section 2.0 of
>
Thanks for the explanation,
i get it, it works different from what expect,
and different compared to MySQL (and Oracle)
[sqlite] Why is empty string not equal to empty blob?
On 19-3-2015 16:02, Simon Slavin wrote:
>
> On 19 Mar 2015, at 2:56pm, Paul wrote:
>
>> Maybe this question was already asked and explained.
>> Or maybe it is documented somewhere (could not fiund it).
>> Sorry, if this is the case, but why does
>>
>> SELECT '' = x'';
>>
>> yields 0?
>
> One is a string. The other is a BLOB. SQLite doesn't even get as far as
> testing the contents, it knows they are of different types.
>
C:\temp>sqlite3
SQLite version 3.8.8.3 2015-02-25 13:29:11
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table test (x string, y blob);
sqlite> insert into test values ('x','x');
sqlite> select * from test;
x|x
sqlite> select x,y, x=y from test where x=y;
x|x|1
sqlite>
Can you comment on:
"SQLite doesn't even get as far as testing the contents, it knows they
are of different types."
?
[sqlite] Why is empty string not equal to empty blob?
> On 29 Mar 2015, at 5:31pm, Luuk wrote:
>
> On 19-3-2015 16:02, Simon Slavin wrote:
>>
>> One is a string. The other is a BLOB. SQLite doesn't even get as far as
>> testing the contents, it knows they are of different types.
>
> C:\temp>sqlite3
> SQLite version 3.8.8.3 2015-02-25 13:29:11
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table test (x string, y blob);
> sqlite> insert into test values ('x','x');
> sqlite> select * from test;
> x|x
> sqlite> select x,y, x=y from test where x=y;
> x|x|1
> sqlite>
>
> Can you comment on:
> "SQLite doesn't even get as far as testing the contents, it knows they are of
> different types."
You explicitly put strings into both columns. The two values are the same even
though you declared the columns differently. As I told you before, if you want
to see what type something is, use typeof(thing).
sqlite> create table testsb (x string, y blob);
sqlite> insert into testsb values ('x', 'x');
sqlite> select x, y, typeof(x), typeof(y), x=y from testsb;
x|x|text|text|1
So far, the same as you did. Now ...
sqlite> insert into testsb values ('x', x'78');
sqlite> insert into testsb values (x'78', x'78');
sqlite> select x, y, typeof(x), typeof(y), x=y from testsb;
x|x|text|text|1
x|x|text|blob|0
x|x|blob|blob|1
Column type doesn't matter:
sqlite> create table testbb (x blob, y blob);
sqlite> insert into testbb values ('x', 'x');
sqlite> insert into testbb values ('x', x'78');
sqlite> insert into testbb values (x'78', x'78');
sqlite> select x, y, typeof(x), typeof(y), x=y from testbb;
x|x|text|text|1
x|x|text|blob|0
x|x|blob|blob|1
It has just occurred to me that you might not know what in SQLite columns have
affinities, not fixed types. You can store any type of value in any column.
SQLite will convert one to another only on the route of TEXT --> FLOAT -->
INTEGER. For more on this see section 2.0 of
Simon.
[sqlite] Why is empty string not equal to empty blob?
On Sunday, 29 March, 2015 10:31, Luuk inquired:
>On 19-3-2015 16:02, Simon Slavin wrote:
>> On 19 Mar 2015, at 2:56pm, Paul wrote:
>>> Maybe this question was already asked and explained.
>>> Or maybe it is documented somewhere (could not fiund it).
>>> Sorry, if this is the case, but why does
>>> SELECT '' = x'';
>>> yields 0?
>> One is a string. The other is a BLOB. SQLite doesn't even get as far
>> as testing the contents, it knows they are of different types.
>C:\temp>sqlite3
>SQLite version 3.8.8.3 2015-02-25 13:29:11
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> create table test (x string, y blob);
>sqlite> insert into test values ('x','x');
>sqlite> select * from test;
>x|x
>sqlite> select x,y, x=y from test where x=y;
>x|x|1
>sqlite>
>Can you comment on:
>"SQLite doesn't even get as far as testing the contents, it knows they
>are of different types."?
Because it is obvious? The affinity of the column does not control the type of
data stored, it is merely a preference of the datatype which will be used if
and only if it can:
SQLite version 3.8.9 2015-03-23 21:32:50
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table x (x text, y blob);
sqlite> insert into x values ('x', 'x');
sqlite> insert into x values ('x', 0x78);
sqlite> insert into x values ('x', X'78');
sqlite> select x, y, typeof(x), typeof(y), x==y from x;
x|x|text|text|1
x|120|text|integer|0
x|x|text|blob|0
sqlite>
So, the first insert inserts text values into each column (because text is
provided). The second inserts text and an integer (because that is what is
provided). The third text and a blob (because that it what is provided). You
will note that SQLite is well aware of the type of the data stored. You will
also note that the test for equality is only true where the type of the data
compared is the same, even though in all cases the actual data is the single
byte 0x78.
You may further note that you can use cast(thing as type) to do type
conversions which will result in comparable datatypes:
sqlite> select x, cast(y as text), typeof(x), typeof(cast(y as text)),
x==cast(y as text) from x;
x|x|text|text|1
x|120|text|text|0
x|x|text|text|1
sqlite> select cast(x as blob), cast(y as blob), typeof(cast(x as blob)),
typeof(cast(y as blobl)), cast(x as blob)==cast(y as blob) from x;
x|x|blob|blob|1
x|120|blob|blob|0
x|x|blob|blob|1
Do this help your understanding?
---
Theory is when you know everything but nothing works. Practice is when
everything works but no one knows why. Sometimes theory and practice are
combined: nothing works and no one knows why.
