Creating View in vertica

[ajit_nayak]

Hi,

I have to insert data in 4 tables at a time from my C# code.
As there is no idea about storedprocedure & view in vertica , I am not able to do it.

My table Structure

Test1 (A,B,C,D,E)
Test2(B,C)
Test3(D,E)
Test4(A,C,E)

In case of view I can do it by multiple insert entries in SQL server. And by running the view once respective tables will be updated.

How can I do it in vertica with optimized way.

Suggestion Needed

Thanks,
Ajit Kumar Nayak

[nnani]

Hello ajit

As per my understanding
You need to load 4 tables using the insert into select clause
The select will be done from the view.


If my understanding is right
You will need a parent table to feed your views
Lets say

Code: Select all

nnani=> select * from public.kitab;
 k_id | k_name  |   d_date   |   e_date
------+---------+------------+------------
    1 | abc     | 2013-07-02 | 2013-07-02
    2 | jones   | 2001-12-24 | 2013-07-02
    3 | rambo   | 2001-12-24 | 2011-12-25
    4 | stalone | 2012-12-04 | 2011-12-25
(4 rows)


nnani=> create view public.kitab_vw as select * from tempdb.kitab;
CREATE VIEW
nnani=> select * from public.kitab_vw;
 k_id | k_name  |   d_date   |   e_date
------+---------+------------+------------
    1 | abc     | 2013-07-02 | 2013-07-02
    2 | jones   | 2001-12-24 | 2013-07-02
    3 | rambo   | 2001-12-24 | 2011-12-25
    4 | stalone | 2012-12-04 | 2011-12-25
(4 rows)

Now that you have your view created, Lets try inserting data into the 4 tables

Code: Select all

nnani=> insert into tempdb.test1 select A,B,C,D from public.kitab_vw;
 OUTPUT
--------
      4
(1 row)

nnani=> select * from public.test1;
 A |    B    |     C      |     D
---+---------+------------+------------
 1 | abc     | 2013-07-02 | 2013-07-02
 2 | jones   | 2001-12-24 | 2013-07-02
 3 | rambo   | 2001-12-24 | 2011-12-25
 4 | stalone | 2012-12-04 | 2011-12-25
(4 rows)

This is how you can insert data into every table
Vertica supports Views

Views are read only. You cannot perform insert, update, delete, or copy operations on a view.

Let me know, If the assumption was right

[id10t]

Hi!

And why you need 4 tables? Why you need all these duplicates?
I think you need a single table + additional projections, because Table2, Table3 and Table4 are subset of Table1.

[ajit_nayak]

My case is ....

Test1 is the parent table with(A,B,C,D)
A- Primary Key (An ID column)
B,C,D are some other non primary columns

Test2 contains (B,C)

B-Referenced to Test1(B)

and so on

To normalize the Table Structure I have created this Chain like table structure .

Thanks
Ajit