[comp.databases] Need help with INGRES SQL copy to/from file

beverly@ai.cs.wisc.edu (Beverly Seavey (-Kung)) (08/28/90)

  Iam trying to transfer the contents of one table to another
  using "copy from" and "copy into".

  The SQL statement:

	copy table atomlook3 (atomnumb=c0,comma=d1,
						  aaname=c0,comma=d1,
						  atomname=c0, comma=d1,
						  nucleus = c0)
   into 'DSK$USER12:[NMRDB]pd.out'


results in a nicely formatted file with one line for
each record in the table atomlook3. If I then try to 
transfer the file's contents  
into a new table:

	 copy table pd_atom_look(atomnumb=c0comma,
							 aaname=c0comma,
							 atomname=c0comma,
							 nucleus=c0)
     from 'DSK$USER12:[NMRDB].pd.out'

I get :
INGRES ERROR: 18826 COPY:Error encountered while processing row 2
INGRES ERROR: 17111 string cannot be converted to numeric due to
			  incorrect syntax.
			  
     copy table pd_atom_look(atomnumb=c0comma,
							   aaname=c0comma,
                             atomname=c0comma,
							 nucleus=c0nl)
     from 'DSK$USER12:[NMRDB]pd.out'

results  in:
INGRES ERROR: 18815 COPY: an unterminated "varchar" field occurred while fillin
INGRES ERROR: 18826 COPY: Error encountered while processing row 0


Just what syntax exactly does Ingres/SQL want here?

drack@titan.tsd.arlut.utexas.edu (Dave Rackley) (08/29/90)

In article <11104@spool.cs.wisc.edu> beverly@ai.cs.wisc.edu (Beverly Seavey (-Kung)) writes:

>     Iam trying to transfer the contents of one table to another
>     using "copy from" and "copy into".

>     The SQL statement:

>	   copy table atomlook3 (atomnumb=c0,comma=d1,
>						     aaname=c0,comma=d1,
>						     atomname=c0, comma=d1,
>						     nucleus = c0)
>      into 'DSK$USER12:[NMRDB]pd.out'


>   results in a nicely formatted file with one line for
>   each record in the table atomlook3. If I then try to 
>   transfer the file's contents  
>   into a new table:

[Stuff deleted]
	    
A different approach would be:

    create table newtable as
       select * from oldtable

This will create a 'copy' of your old table, and it will allow additional
SQL clauses.  You can still use your copy syntax for outputting reports!


Hope this helps ;-)

--
+=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=+
| David Rackley		        |                                             |
| Applied Research Laboratories |         "Say what?  Use what? ...           |
| P.O. Box 8029                 | ...I'd rather kiss a fat man on the lips!"  |
| Austin, TX.  78713-8029       |                                             |
+=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=+
|        DISCLAIMER?  I don't know anything 'bout any ol' disclaimer!         |
+=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=+

chesky@portia.Stanford.EDU (Snehylata Gupta) (08/29/90)

In article <11104@spool.cs.wisc.edu> beverly@ai.cs.wisc.edu (Beverly Seavey (-Kung)) writes:
>
>  Iam trying to transfer the contents of one table to another
>  using "copy from" and "copy into".
>
>  The SQL statement:
>
>	copy table atomlook3 (atomnumb=c0,comma=d1,
>						  aaname=c0,comma=d1,
>						  atomname=c0, comma=d1,
>						  nucleus = c0)
>   into 'DSK$USER12:[NMRDB]pd.out'
>
>
>results in a nicely formatted file with one line for
>each record in the table atomlook3. If I then try to 
>transfer the file's contents  
>into a new table:
>
>	 copy table pd_atom_look(atomnumb=c0comma,
>							 aaname=c0comma,
>							 atomname=c0comma,
>							 nucleus=c0)
                                                                   ^
  Should be c0nl.
  From the syntax of copying into the file. You have to tell Ingres to
read a newline character as the delimiter after reading the column nucleus.

>Just what syntax exactly does Ingres/SQL want here?

I hope that is what Ingres wants.

Sanjay

moiram@tekcae.CAX.TEK.COM (Moira Mallison) (08/30/90)

In article <11104@spool.cs.wisc.edu> beverly@ai.cs.wisc.edu (Beverly Seavey (-Kung)) writes:
>>
>>  Iam trying to transfer the contents of one table to another
>>  using "copy from" and "copy into".
>>

For copying tables in the same database, I agree that the SQL select
statement is the better answer.  If you need to copy the data in and
out of a file (to port to another database, for example), I usually
start with the copydb command.  You can constrain it to a single table
with the -t flag, and to ascii output with the -c flag.  The output
of the copydb command will be a copy.in and copy.out scripts in the
query language.  You can edit these scripts if necessary, but they
make a convenient starting place for the copy statement.

Moira Mallison
CAx Data Management
Tektronix, Inc