Insert with COPY TABLE, add columns

[ais_test]

Hi everybody.

My company is making few tests in order to use vectorwise for some projects, but we have the need to make some special inserts that im not able to reproduce in vector wise, this is the examle...
Table:

CREATE TABLE MYTABLE (
      CLIENTNUM           VARCHAR(20) NULL,
      DATADATE            DATE NULL,
      TYPECLIENT         VARCHAR(2) NULL,
      TITTLENUMBER             INTEGER NULL,
      TOTAL         DECIMAL(14,2) NULL,
      PHISICALID  INTEGER NOT NULL,
      CONSTID     INTEGER NOT NULL,
      DATAID      INTEGER NOT NULL,
      RUNDATE              date,
      CONSTRAINT XPKMYTABLE
            PRIMARY KEY (DATAID, CONSTID, PHISICALID  )
);\p\g

And the next would be the insert from a positional file:

COPY TABLE MYTABLE (
      CLIENTNUM = CHAR(20)
      DATADATE   = CHAR(10)
      TYPECLIENT    =CHAR(2)
      TITTLENUMBER   =CHAR(2)
      TOTAL       = CHAR(16)

      PHISICALID  = Sequence(1,1)
      CONSTID     = 1
      DATAID      = 1
      RUNDATE         = getDate()
  
);\p\g

And the last 4 columns are not in the file, should be added on runtime.
PHISICALID as some kind of sequence
CONSTID and DATA ID as a fixed constant num by now... who cares just a "1"
RNDATE should be the day of running that insert.

This would be data to insert as a example:

000000007           2009-01-315300+000000021425,66

How can i make that insert work?

Thnk you in advance

Leo

PS: As further work, our file have the date as yyyyMMdd (Fixed the "yyy" typo), without any separators, if we can make that work would make me and my boss rly happy and we would end by buying the product.

Tyvm

[kschendel]

You can do it with a value default on an Ingres-style table. Load the data into a heap table, leave off the columns that you want to default, and then do a mass insert/select into the Vectorwise table.

As for the date, setting II_DATE_FORMAT to YMD (or use the set date_format statement) will read YYYYMMDD dates. I don't think there's a date format that accepts three-digit years.

[ais_test]

Oh about the date my fault, years are 4 digits.

Furthermore, regarding the Ingres-style table, you are suggesting to use another table to be able to insert the data without that primary key and then insert select adding the columns right?

Btw i didnt found rly much documentation about functions and syntax of VW, is there somewhere witch can be found in a clear way?

[kschendel]

Originally Posted by ais_test

Furthermore, regarding the Ingres-style table, you are suggesting to use another table to be able to insert the data without that primary key and then insert select adding the columns right?

Well, that would work, but actually I was thinking of using value defaults on an Ingres (heap, btree, etc) table. You can declare an identity column or a column with a sequence default (the two are more or less the same thing), and a column with a default of current_date, and copy the data into that table, omitting the defaulted columns. Then, insert everything into your Vectorwise structured table.

The SQL Reference and Vectorwise documentation should be available online somewhere under Actian.com (I don't have the exact link handy).

[teresa]

The Vectorwise documentation is available at http://docs.actian.com/

[schma01]

Actually you should be able to do what Karl suggests w/ Vectorwise tables only. the default function DOES work with VW tables.

Here's a simple example.

INGRES TERMINAL MONITOR Copyright 2010 Ingres Corporation
VectorWise Microsoft Windows Version VW 2.0.1 (a64.win/119) login
Wed May 23 09:12:49 2012
Enter \g to execute commands, "help help\g" for help, \q to quit

continue
* /* SQL Startup File */
create table t1 (c1 int, c2 int default 50)
Executing . . .

continue
* create table t2 (c1 int)
Executing . . .

continue
* insert into t1 (c1) values (1)
Executing . . .

(1 row)
continue
* insert into t2 values (2)
Executing . . .

(1 row)
continue
* insert into t1 (c1) select * from t2
Executing . . .

(1 row)
continue
* select * from t1
Executing . . .


+-------------+-------------+
|c1 |c2 |
+-------------+-------------+
| 1| 50|
| 2| 50|
+-------------+-------------+
(2 rows)
continue
*
Your SQL statement(s) have been committed.

VectorWise Version VW 2.0.1 (a64.win/119) logout
Wed May 23 09:12:49 2012

[mohsa01]

Hi Leo,

Not sure if this thread has already been resolved in the meantime. Just to be sure, the full syntax for your example based on Karl's and Marry's advice would be as follows:

CREATE TABLE MYTABLE
(
CLIENTNUM VARCHAR(20) NULL,
DATADATE DATE NULL,
TYPECLIENT VARCHAR(2) NULL,
TITTLENUMBER INTEGER NULL,
TOTAL DECIMAL(14,2) NULL,
PHISICALID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
CONSTID INTEGER NOT NULL WITH DEFAULT 1,
DATAID INTEGER NOT NULL WITH DEFAULT 1,
RUNDATE DATE WITH DEFAULT CURRENT_DATE
);
SET DECIMAL ',';
SET DATE_FORMAT 'YMD';
COPY mytable(
clientnum= CHAR(20),
datadate= CHAR(8),
typeclient= CHAR(2),
tittlenumber= CHAR(2),
total= CHAR(16)nl
)
from 'myfile';

The content of myfile I used for the test is as follows

000000001 200901015300+000000021425,61
000000002 200901025300+000000021425,62
000000003 200901035300+000000021425,63
000000004 200901045300+000000021425,64
000000005 200901055300+000000021425,65
000000006 200901065300+000000021425,66
000000007 200901075300+000000021425,67
000000008 200901085300+000000021425,68
000000009 200901095300+000000021425,69

Regards, Sarkaut