Обсуждение: Inserting data from one table to another

Hi everybody!

I try to insert data from one table to another with:

INSERT INTO L_klienci_wysylka ('id_klienta','data_wys')(SELECT
'ID','data_wys' FROM 'I_klienci')

but I get:

4: Table not found in statement [INSERT INTO L_klienci_wysylka]

I'm sure that table L_klienci_wysylka exist because I can enter the data
manually.
I am using OpenOffice Base 3.2.0 with HSQL engine.

I know that this is PostgreSQL mailing list, but I can't get the answer
from OOBase mailing list.

Krzysztof

On 17 February 2010 13:02, Krzysztof Walkiewicz <bars0@op.pl> wrote:
> Hi everybody!
>
> I try to insert data from one table to another with:
>
> INSERT INTO L_klienci_wysylka ('id_klienta','data_wys')(SELECT
> 'ID','data_wys' FROM 'I_klienci')
>
> but I get:
>
> 4: Table not found in statement [INSERT INTO L_klienci_wysylka]
>
> I'm sure that table L_klienci_wysylka exist because I can enter the data
> manually.
> I am using OpenOffice Base 3.2.0 with HSQL engine.
>
> I know that this is PostgreSQL mailing list, but I can't get the answer from
> OOBase mailing list.
>
> Krzysztof

You've specified the table in the select statement as a value.  Remove
the single quotes.

Regards

Thom

On 17 February 2010 13:02, Krzysztof Walkiewicz <bars0@op.pl> wrote:
> Hi everybody!
>
> I try to insert data from one table to another with:
>
> INSERT INTO L_klienci_wysylka ('id_klienta','data_wys')(SELECT
> 'ID','data_wys' FROM 'I_klienci')
>
> but I get:
>
> 4: Table not found in statement [INSERT INTO L_klienci_wysylka]
>
> I'm sure that table L_klienci_wysylka exist because I can enter the data
> manually.
> I am using OpenOffice Base 3.2.0 with HSQL engine.
>
> I know that this is PostgreSQL mailing list, but I can't get the answer from
> OOBase mailing list.
>
> Krzysztof
>
Actually, I've just noticed you've also used single quotes on the column names.

Use:

INSERT INTO L_klienci_wysylka (id_klienta,data_wys)(SELECT id,data_wys
FROM I_klienci);

If the id column in l_klienci really is in upper-case, you'll have to
put double-quotes around it:

INSERT INTO L_klienci_wysylka (id_klienta,data_wys)(SELECT
"ID",data_wys FROM I_klienci);

So double-quotes (not 2 single quotes in a row) are for specifying
names of columns, tables, sequences etc when they contain spaces,
full-stops (periods to our American cousins) or mixed-case names.
Single-quotes are for specifying values, such as 'Austria', 'Tom Lane
made me cry', '2010-09-13 12:12:11' (although obviously not for
numeric values.

Regards

Thom

W dniu 2010-02-17 14:43, Thom Brown pisze:
> On 17 February 2010 13:02, Krzysztof Walkiewicz<bars0@op.pl>  wrote:
>> Hi everybody!
>>
>> I try to insert data from one table to another with:
>>
>> INSERT INTO L_klienci_wysylka ('id_klienta','data_wys')(SELECT
>> 'ID','data_wys' FROM 'I_klienci')
>>
>> but I get:
>>
>> 4: Table not found in statement [INSERT INTO L_klienci_wysylka]
>>
>> I'm sure that table L_klienci_wysylka exist because I can enter the data
>> manually.
>> I am using OpenOffice Base 3.2.0 with HSQL engine.
>>
>> I know that this is PostgreSQL mailing list, but I can't get the answer from
>> OOBase mailing list.
>>
>> Krzysztof
>>
> Actually, I've just noticed you've also used single quotes on the column names.
>
> Use:
>
> INSERT INTO L_klienci_wysylka (id_klienta,data_wys)(SELECT id,data_wys
> FROM I_klienci);
>
> If the id column in l_klienci really is in upper-case, you'll have to
> put double-quotes around it:
>
> INSERT INTO L_klienci_wysylka (id_klienta,data_wys)(SELECT
> "ID",data_wys FROM I_klienci);
>
> So double-quotes (not 2 single quotes in a row) are for specifying
> names of columns, tables, sequences etc when they contain spaces,
> full-stops (periods to our American cousins) or mixed-case names.
> Single-quotes are for specifying values, such as 'Austria', 'Tom Lane
> made me cry', '2010-09-13 12:12:11' (although obviously not for
> numeric values.
>
> Regards
>
> Thom
>
Thank You Thom for your reply.
I think i tried all the possibilities and the correct command was:

INSERT INTO "L_klienci_wysylka" ("id_klienta","data_wys") (SELECT
"ID","data_wys" FROM "I_klienci");

I knew that I won't be disappointed when I ask in that mailing list.

Have a nice day!

Krzysztof