LwFmKVjz

1/*
2Doing a Merge on a table with a Sequence in Vertica.
3
4This strategy requires two staging tables and the sequence to be decoupled from the target table.
5
6The first staging table will hold the rows to be inserted without an ID. This table would be the result of transformations
7from tables already in the database. The second staging table will hold the same rows except with an assigned ID from the sequence.
8
9In this example natural_pk represents the natural primary key for the table. This can be one or many columns.
10While the address column represents a moving dimension of the data.
11 */
12
13DROP SEQUENCE IF EXISTS temp.foobar_id;
14CREATE SEQUENCE temp.foobar_id START 1;
15
16-- target table is the production table we want to load new rows into
17DROP TABLE IF EXISTS  temp.target;
18CREATE TABLE temp.target (
19  id NUMBER PRIMARY KEY,
20  natural_pk VARCHAR(32),
21  address VARCHAR(32)
22);
23
24INSERT INTO temp.target VALUES (1, 'adam smith', '20 Windsor Ln');
25INSERT INTO temp.target VALUES (2, 'bailey adams', '53 Washington St');
26
27DROP TABLE IF EXISTS temp.staging_1;
28CREATE TABLE temp.staging_1 (
29  natural_pk VARCHAR(32),
30  address VARCHAR(32)
31);
32
33-- data with a natural primary key but no ID
34INSERT INTO temp.staging_1 VALUES ('bailey adams', '113 Oak Grove Rd');
35INSERT INTO temp.staging_1 VALUES ('cindy Rockefeller', '203 Elm St');
36
37
38DROP TABLE IF EXISTS temp.staging_2;
39CREATE TABLE temp.staging_2 (
40  id NUMBER,
41  natural_pk VARCHAR(32),
42  address VARCHAR(32)
43);
44
45INSERT INTO temp.staging_2 (
46  id,
47  natural_pk,
48  address
49)
50SELECT
51  COALESCE(tgt.id, NEXTVAL('temp.foobar_id')) as id,
52  staging_1.natural_pk,
53  staging_1.address
54
55     FROM temp.staging_1 staging_1
56LEFT JOIN temp.target tgt
57          ON staging_1.natural_pk = tgt.natural_pk
58;
59
60-- before the merge
61-- (1, 'adam smith', '20 Windsor Ln')
62-- (2, 'bailey adams', '53 Washington St')
63SELECT id, address FROM temp.target;
64
65MERGE INTO temp.target tgt
66     USING temp.staging_2 src
67        ON tgt.id = src.id
68
69  WHEN MATCHED THEN UPDATE SET address = src.address
70
71  WHEN NOT MATCHED THEN INSERT (id, natural_pk, address) VALUES (src.id, src.natural_pk, src.address)
72;
73
74-- after the merge
75-- (1, 'adam smith', '20 Windsor Ln')
76-- (2, 'bailey adams', '113 Oak Grove Rd')
77-- (3, 'cindy Rockefeller', '203 Elm St')
78SELECT * FROM temp.target;