You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Walter Rogura <mi...@sudus.eu> on 2009/03/03 00:40:38 UTC
COPY command
Hi,
is there a COPY command applicable in derby? I know this from
PostgreSQL, e.g.
COPY table (column1, column2) FROM stdin;
c11 c12
c21 c22
c31 c32
\.
I tried it with SQuirreL but had no success. Btw. where do I find a
reference of supported SQL commands in derby?
Thank you very much,
Walter
Re: COPY command
Posted by Walter Rogura <mi...@sudus.eu>.
Hi Francois,
That is good stuff and what I need.
Thank you very much,
Walter
On Tue, 3 Mar 2009 10:29:39 -0800
Francois Orsini <fr...@gmail.com> wrote:
> Hi Walter,
>
> You can also import data from a text file with comma-separated fields
> using Derby's SYSCS_UTIL.SYSCS_IMPORT_DATA() built-in procedure...
>
> CREATE TABLE TAB1 (c1 VARCHAR(30), c2 INT)
> CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (NULL, 'TAB1', null, null,
> 'myfile.del',null, null, null, 0)
>
> With myfile.del containing data sych as:
> Robert,1
> Mike,2
> Leo,3
>
> More info at:
> http://db.apache.org/derby/docs/10.4/tools/tools-single.html#ctoolsimport16245
>
> You can also create a table out of another existing table and specify
> which (if not all) columns you want to have:
> CREATE TABLE TAB1 AS SELECT c1, c2 FROM TAB3 WITH NO DATA;
>
> You can find a list of Derby's SQL commands in the Reference guide:
> http://db.apache.org/derby/docs/10.4/ref/ref-single.html
>
> Cheers
>
> --francois
>
> On Mon, Mar 2, 2009 at 3:40 PM, Walter Rogura <mi...@sudus.eu> wrote:
>
> > Hi,
> >
> > is there a COPY command applicable in derby? I know this from
> > PostgreSQL, e.g.
> > COPY table (column1, column2) FROM stdin;
> > c11 c12
> > c21 c22
> > c31 c32
> > \.
> >
> > I tried it with SQuirreL but had no success. Btw. where do I find a
> > reference of supported SQL commands in derby?
> >
> > Thank you very much,
> > Walter
> >
Re: COPY command
Posted by Francois Orsini <fr...@gmail.com>.
Hi Walter,
You can also import data from a text file with comma-separated fields using
Derby's SYSCS_UTIL.SYSCS_IMPORT_DATA() built-in procedure...
CREATE TABLE TAB1 (c1 VARCHAR(30), c2 INT)
CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (NULL, 'TAB1', null, null,
'myfile.del',null, null, null, 0)
With myfile.del containing data sych as:
Robert,1
Mike,2
Leo,3
More info at:
http://db.apache.org/derby/docs/10.4/tools/tools-single.html#ctoolsimport16245
You can also create a table out of another existing table and specify which
(if not all) columns you want to have:
CREATE TABLE TAB1 AS SELECT c1, c2 FROM TAB3 WITH NO DATA;
You can find a list of Derby's SQL commands in the Reference guide:
http://db.apache.org/derby/docs/10.4/ref/ref-single.html
Cheers
--francois
On Mon, Mar 2, 2009 at 3:40 PM, Walter Rogura <mi...@sudus.eu> wrote:
> Hi,
>
> is there a COPY command applicable in derby? I know this from
> PostgreSQL, e.g.
> COPY table (column1, column2) FROM stdin;
> c11 c12
> c21 c22
> c31 c32
> \.
>
> I tried it with SQuirreL but had no success. Btw. where do I find a
> reference of supported SQL commands in derby?
>
> Thank you very much,
> Walter
>
Re: COPY command
Posted by Walter Rogura <mi...@sudus.eu>.
Thank you very much Rick! I'll look into that.
On Tue, 03 Mar 2009 06:14:53 -0800
Rick Hillegas <Ri...@Sun.COM> wrote:
> Hi Walter,
>
> Derby's SQL dialect is documented in the Reference Guide:
> http://db.apache.org/derby/docs/10.4/ref/
>
> If what you need to do is populate a table from stdin, you can do
> this by writing a table function which reads from stdin and presents
> the input as rows. Then you populate your table like this:
>
> insert into mytable
> select * from table( stdinReadingTableFunction() ) s
>
> Table functions are described in the Developer's Guide in a section
> titled "Programming Derby-style table functions":
> http://db.apache.org/derby/docs/10.4/devguide/ Table functions are
> discussed at length in the white paper posted here:
> http://developers.sun.com/javadb/reference/whitepapers/index.jsp
>
> Hope this helps,
> -Rick
>
>
> Walter Rogura wrote:
> > Hi,
> >
> > is there a COPY command applicable in derby? I know this from
> > PostgreSQL, e.g.
> > COPY table (column1, column2) FROM stdin;
> > c11 c12
> > c21 c22
> > c31 c32
> > \.
> >
> > I tried it with SQuirreL but had no success. Btw. where do I find a
> > reference of supported SQL commands in derby?
> >
> > Thank you very much,
> > Walter
> >
>
Re: COPY command
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Walter,
Derby's SQL dialect is documented in the Reference Guide:
http://db.apache.org/derby/docs/10.4/ref/
If what you need to do is populate a table from stdin, you can do this
by writing a table function which reads from stdin and presents the
input as rows. Then you populate your table like this:
insert into mytable
select * from table( stdinReadingTableFunction() ) s
Table functions are described in the Developer's Guide in a section
titled "Programming Derby-style table functions":
http://db.apache.org/derby/docs/10.4/devguide/ Table functions are
discussed at length in the white paper posted here:
http://developers.sun.com/javadb/reference/whitepapers/index.jsp
Hope this helps,
-Rick
Walter Rogura wrote:
> Hi,
>
> is there a COPY command applicable in derby? I know this from
> PostgreSQL, e.g.
> COPY table (column1, column2) FROM stdin;
> c11 c12
> c21 c22
> c31 c32
> \.
>
> I tried it with SQuirreL but had no success. Btw. where do I find a
> reference of supported SQL commands in derby?
>
> Thank you very much,
> Walter
>