Re: [sqlite] INSERT INTO with SELECT

[Pete]

Hi SImon,
Sorry if my replies seem a little disjointed - I get a daily digest of the
list so I don't see any responses to my posts until the next day.

Anyway, turns out this solution doesnt work - the INSERT fails because
there are two columns in t3 but only 1 column named in the SELECT.  The
solution is per Michael's later reply which is :

INSERT into t3 (Col1) SELECT Col1 FROM t2;

Thanks to everyone for the solution.

Pete

On Thu, Apr 5, 2012 at 9:00 AM, <sqlite-users-requ...@sqlite.org> wrote:

> Message: 10
> Date: Thu, 5 Apr 2012 01:38:57 +0100
> From: Simon Slavin <slav...@bigfraud.org>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Subject: Re: [sqlite] INSERT INTO with SELECT
> Message-ID: <bb6e260a-182b-4fac-a95d-fb9b115cd...@bigfraud.org>
> Content-Type: text/plain; charset=us-ascii
>
>
> On 5 Apr 2012, at 1:36am, Pete <p...@mollysrevenge.com> wrote:
>
> > Here's my test:
> >
> > CREATE TABLE t2 (Col1 text,Col2 text);
> > insert into t2 (Col1) values('xxx');
> > select * from t2
> > xxx|
> >
> > CREATE TABLE t3 (Col1 text,Col2 text default 'abc');
> > insert into t3 SELECT * FROM t2;
> > select * from t3;
> > xxx|
> >
> > Why does t3.Col2 not have it's default value of 'abc'?
>
> Because you fed it a value for the second column: NULL.  If you want the
> second column to have a default value you might find that
>
> insert into t3 SELECT Col1 FROM t2;
>
> works.
>
> Simon.
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT INTO with SELECT

[Igor Tandetnik]

On 4/5/2012 1:09 PM, Simon Slavin wrote:


On 5 Apr 2012, at 6:05pm, Simon Slavin  wrote:


I don't understand what you think is wrong with

insert into t3 SELECT Col1 FROM t2;


Oh, now I do.  Okay, SQLite insists on the number of columns matching when you 
use a sub-SELECT


So you could just do

insert into t3(Col1) SELECT Col1 FROM t2;
--
Igor Tandetnik

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

Re: [sqlite] INSERT INTO with SELECT

[Simon Slavin]

On 5 Apr 2012, at 6:05pm, Simon Slavin  wrote:

> I don't understand what you think is wrong with
> 
> insert into t3 SELECT Col1 FROM t2;

Oh, now I do.  Okay, SQLite insists on the number of columns matching when you 
use a sub-SELECT even though it doesn't when you use the bracketed form where 
you define the columns.  Okay, ignore that and look at the rest of what I 
posted.

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

Re: [sqlite] INSERT INTO with SELECT

[Simon Slavin]

On 5 Apr 2012, at 5:33pm, Pete  wrote:

> Thank you Igor and Simon, I think I understand this now.  On reading the
> docs more closely, it looks like the scond test case (NOT NULL with a
> DEFAULT) could be solved by using INSERT OR REPLACE.  I'm wondering if
> there might be a way to solve the first test case by using a trigger?

I don't understand what you think is wrong with

insert into t3 SELECT Col1 FROM t2;

You obviously don't the second column from t2, so don't SELECT it.
If you have some kind of logic which states

NULL values are perfectly legal in t2.
But NULL values are not legal in t3, where they should always be replaced with 
'abc', but all non-NULL values must be preserved.

Then you could do all your INSERTs, then follow up with a

UPDATE t3 SET Col2='abc' WHERE Col2 IS NULL

(which will go faster if Col2 is indexed) or you can use SQLite's 
coalesce(X,Y,...) function as documented in



something like

insert into t3 SELECT Col1,coalesce(Col2,'abc') FROM t2;

Which you do depends on how many NULLs you have, how easy it is to make an 
appropraite index, and how many rows there are in the table overall.

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

Re: [sqlite] INSERT INTO with SELECT

[Igor Tandetnik]

On 4/5/2012 12:33 PM, Pete wrote:

Thank you Igor and Simon, I think I understand this now.  On reading the
docs more closely, it looks like the scond test case (NOT NULL with a
DEFAULT) could be solved by using INSERT OR REPLACE.


I don't see how.


I'm wondering if
there might be a way to solve the first test case by using a trigger?


Possibly. I don't quite understand the nature of the problem, nor what 
would constitute a solution.


Would something like this help?

insert into t2 SELECT Col1, coalesce(Col2, 'abc') from t1;

--
Igor Tandetnik

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

Re: [sqlite] INSERT INTO with SELECT

[Pete]

Thank you Igor and Simon, I think I understand this now.  On reading the
docs more closely, it looks like the scond test case (NOT NULL with a
DEFAULT) could be solved by using INSERT OR REPLACE.  I'm wondering if
there might be a way to solve the first test case by using a trigger?

Pete

On Thu, Apr 5, 2012 at 9:00 AM, <sqlite-users-requ...@sqlite.org> wrote:

> Message: 10
> Date: Thu, 5 Apr 2012 01:38:57 +0100
> From: Simon Slavin <slav...@bigfraud.org>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Subject: Re: [sqlite] INSERT INTO with SELECT
> Message-ID: <bb6e260a-182b-4fac-a95d-fb9b115cd...@bigfraud.org>
> Content-Type: text/plain; charset=us-ascii
>
>
> On 5 Apr 2012, at 1:36am, Pete <p...@mollysrevenge.com> wrote:
>
> > Here's my test:
> >
> > CREATE TABLE t2 (Col1 text,Col2 text);
> > insert into t2 (Col1) values('xxx');
> > select * from t2
> > xxx|
> >
> > CREATE TABLE t3 (Col1 text,Col2 text default 'abc');
> > insert into t3 SELECT * FROM t2;
> > select * from t3;
> > xxx|
> >
> > Why does t3.Col2 not have it's default value of 'abc'?
>
> Because you fed it a value for the second column: NULL.  If you want the
> second column to have a default value you might find that
>
> insert into t3 SELECT Col1 FROM t2;
>
> works.
>
> Simon.
>
> --
>
> Message: 11
> Date: Wed, 4 Apr 2012 17:48:05 -0700
> From: Pete <p...@mollysrevenge.com>
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Variation on INSERT with SELECT issue
> Message-ID:
>

Re: [sqlite] INSERT INTO with SELECT

[Simon Slavin]

On 5 Apr 2012, at 1:36am, Pete  wrote:

> Here's my test:
> 
> CREATE TABLE t2 (Col1 text,Col2 text);
> insert into t2 (Col1) values('xxx');
> select * from t2
> xxx|
> 
> CREATE TABLE t3 (Col1 text,Col2 text default 'abc');
> insert into t3 SELECT * FROM t2;
> select * from t3;
> xxx|
> 
> Why does t3.Col2 not have it's default value of 'abc'?

Because you fed it a value for the second column: NULL.  If you want the second 
column to have a default value you might find that

insert into t3 SELECT Col1 FROM t2;

works.

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

Re: [sqlite] Insert into with select not working

[Martin.Engelschalk]

Hello Brian,

you do not need a VALUE keyword in this case.

INSERT INTO "subscribers_new"
--VALUES  <- remove this
SELECT id, ip_address, added, 'aa:bb:cc:etc' from subscribers;

See the syntax diagram for the insert command: 
http://www.sqlite.org/lang_insert.html

Martin


Brian Zambrano schrieb:
> Can anyone explain to me why an insert with select statement isn't working?
> I know in my my example below I just have an extra column which I could have
> added, but my actual tables are a bit more complex and this below is just to
> demonstrate the problem.
>
> -- Original table
> CREATE TABLE "subscribers" (
> "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
> "ip_address" char(15) NOT NULL UNIQUE,
> "added" integer NOT NULL
> );
>
> -- New table
> CREATE TABLE "subscribers_new" (
> "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
> "ip_address" char(15) NOT NULL UNIQUE,
> "added" integer NOT NULL,
> "mac_address" char(32) NOT NULL
> );
> SQL error: near ""mac_address"": syntax error
>
> -- insert a couple of rows into the original
> INSERT INTO "subscribers" VALUES (1, '1.2.3.4', 123456);
> INSERT INTO "subscribers" VALUES (2, '11.22.33.44', 111);
>
> -- Now try to insert into new table with select, and see failure
> INSERT INTO "subscribers_new"
> VALUES
> SELECT id, ip_address, added, 'aa:bb:cc:etc' from subscribers;
> SQL error: near "SELECT": syntax error
>
> -- Note, the select statement by itself works just fine:
>
> SELECT id, ip_address, added, 'aa:bb:cc:etc' from subscribers;
> id|ip_address|added|'aa:bb:cc:etc'
> 1|1.2.3.4|123456|aa:bb:cc:etc
> 2|11.22.33.44|111|aa:bb:cc:etc
> ___
> 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] INSERT INTO with SELECT

[Jay Sprenkle]

On 11/16/06, RB Smissaert <[EMAIL PROTECTED]> wrote:

My text file is only an intermediate and I can make the way I want.
I need to move data from Interbase to SQLite. Fastest method sofar is:
IB > ADO recordset
ADO recordset > text file
Import text file with SQLite .import command

Problem with .import is that it doesn't like double quotes.


I put together my own import program. You're welcome to try it if you
don't get it fixed.
You can find it at the link below. I would think a loop inside your program
would be faster though.


--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] INSERT INTO with SELECT

[RB Smissaert]

My text file is only an intermediate and I can make the way I want.
I need to move data from Interbase to SQLite. Fastest method sofar is:
IB > ADO recordset
ADO recordset > text file
Import text file with SQLite .import command

Problem with .import is that it doesn't like double quotes.

RBS

-Original Message-
From: Jay Sprenkle [mailto:[EMAIL PROTECTED] 
Sent: 16 November 2006 04:24
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] INSERT INTO with SELECT

You should create your database and your table first.
Do that in sqlite3.exe using your create table statement below.
What format is your text file in? What separates one field from another?


On 11/14/06, RB Smissaert <[EMAIL PROTECTED]> wrote:
> Thanks and in fact I had a look at your text importer about an hour ago.
> Couldn't figure out the parameters though.
> How would these be if I wanted to do this.
>
> Import a text file called ReadCode.txt to a database called ReadCode.db
> Database has one table called ReadCode with these fields:
> SUBJECT_TYPE, READ_CODE, TERM30 , TERM60
>
> SQL to create the table is like this:
>
> create table ReadCode
>   (
> SUBJECT_TYPE   varchar(5),
> READCODE   varchar(5),
> TERM30   varchar(30),
> TERM60varchar(60)
>   );
>
--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] INSERT INTO with SELECT

[Jay Sprenkle]

You should create your database and your table first.
Do that in sqlite3.exe using your create table statement below.
What format is your text file in? What separates one field from another?


On 11/14/06, RB Smissaert <[EMAIL PROTECTED]> wrote:

Thanks and in fact I had a look at your text importer about an hour ago.
Couldn't figure out the parameters though.
How would these be if I wanted to do this.

Import a text file called ReadCode.txt to a database called ReadCode.db
Database has one table called ReadCode with these fields:
SUBJECT_TYPE, READ_CODE, TERM30 , TERM60

SQL to create the table is like this:

create table ReadCode
  (
SUBJECT_TYPE   varchar(5),
READCODE   varchar(5),
TERM30   varchar(30),
TERM60varchar(60)
  );


--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] INSERT INTO with SELECT

[RB Smissaert]

Thanks and in fact I had a look at your text importer about an hour ago.
Couldn't figure out the parameters though.
How would these be if I wanted to do this.

Import a text file called ReadCode.txt to a database called ReadCode.db
Database has one table called ReadCode with these fields:
SUBJECT_TYPE, READ_CODE, TERM30 , TERM60

SQL to create the table is like this:

create table ReadCode
  (
SUBJECT_TYPE   varchar(5),
READCODE   varchar(5),
TERM30   varchar(30),
TERM60varchar(60)
  );

Tried all sorts of constructions, but apart from making the .db file and the
table nil happened, so no data moved.


RBS


-Original Message-
From: Jay Sprenkle [mailto:[EMAIL PROTECTED] 
Sent: 14 November 2006 14:06
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] INSERT INTO with SELECT

On 11/13/06, RB Smissaert <[EMAIL PROTECTED]> wrote:
> OK, thanks for the reply.
> I am using the same construction to write directly from Interbase to
Access
> and that works fine.

I'm surprised. Learn something every day!

> I can make an ADO recordset first from the Interbase
> data and write that to SQLite in a (double) loop, but it is a bit slow.
> Maybe I should write to text first (which is quite fast) and then write to
> SQLite. I am not sure though how to import text into SQLite.

If you write a loop to extract it you can insert it through the sqlite
odbc on a separate
connection. If you write CSV format files the command line program can read
it,
or you can download my importer (the source code is available for both too).

--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] INSERT INTO with SELECT

[Jay Sprenkle]

On 11/13/06, RB Smissaert <[EMAIL PROTECTED]> wrote:

OK, thanks for the reply.
I am using the same construction to write directly from Interbase to Access
and that works fine.


I'm surprised. Learn something every day!


I can make an ADO recordset first from the Interbase
data and write that to SQLite in a (double) loop, but it is a bit slow.
Maybe I should write to text first (which is quite fast) and then write to
SQLite. I am not sure though how to import text into SQLite.


If you write a loop to extract it you can insert it through the sqlite
odbc on a separate
connection. If you write CSV format files the command line program can read it,
or you can download my importer (the source code is available for both too).

--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] INSERT INTO with SELECT

[RB Smissaert]

OK, thanks for the reply.
I am using the same construction to write directly from Interbase to Access
and that works fine. I can make an ADO recordset first from the Interbase
data and write that to SQLite in a (double) loop, but it is a bit slow.
Maybe I should write to text first (which is quite fast) and then write to
SQLite. I am not sure though how to import text into SQLite.
Trouble with the SQLite ODBC driver is that documentation is a bit sparse.
Maybe I should use a transaction, but not sure how that works either.

RBS

-Original Message-
From: Jay Sprenkle [mailto:[EMAIL PROTECTED] 
Sent: 14 November 2006 03:24
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] INSERT INTO with SELECT

On 11/13/06, RB Smissaert <[EMAIL PROTECTED]> wrote:
> Trying to move data from Interbase to SQLite via the ODBC driver and ADO
and
> having trouble to get the right syntax for the INSERT INTO statement.
>
> This is what I have now, but it fails with the error: only one SQL
statement
> allowed.

I notice your code creates this sql:

 INSERT INTO  READCODE (SUBJECT_TYPE, READ_CODE, TERM30, TERM60)
 SELECT R.SUBJECT_TYPE, R.READ_CODE, R.TERM30, R.TERM60
 FROM READCODE R
IN " [  ? ] "

Which isn't valid.  The IN clause is part of WHERE, not a separate
qualifier.
It works like this:

select x as prime  from mytable  where x in ( 1,2,3,5,7 );

Since the select is reading from READCODE you're inserting values
into the same table you're selecting them from. There's no way that I know
of to use odbc to copy from one database to another unless they're both
of the same type.

I would do it by selecting all the values to copy in one statement and
writing
them to memory or a disk file. Then import to the other database in a second
step.




--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] INSERT INTO with SELECT

[Jay Sprenkle]

On 11/13/06, RB Smissaert <[EMAIL PROTECTED]> wrote:

Trying to move data from Interbase to SQLite via the ODBC driver and ADO and
having trouble to get the right syntax for the INSERT INTO statement.

This is what I have now, but it fails with the error: only one SQL statement
allowed.


I notice your code creates this sql:

INSERT INTO  READCODE (SUBJECT_TYPE, READ_CODE, TERM30, TERM60)
SELECT R.SUBJECT_TYPE, R.READ_CODE, R.TERM30, R.TERM60
FROM READCODE R
IN " [  ? ] "

Which isn't valid.  The IN clause is part of WHERE, not a separate qualifier.
It works like this:

select x as prime  from mytable  where x in ( 1,2,3,5,7 );

Since the select is reading from READCODE you're inserting values
into the same table you're selecting them from. There's no way that I know
of to use odbc to copy from one database to another unless they're both
of the same type.

I would do it by selecting all the values to copy in one statement and writing
them to memory or a disk file. Then import to the other database in a second
step.




--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-