I'm trying to increment an existing ID field that was generated using sequence in snowflake.

For reference here's the code & sample output:

Code

CREATE OR REPLACE SEQUENCE id_sequence
START WITH 1
INCREMENT BY 1
;

Output

Id   Keyword    URL    Type              Position_Group 
1     A          A      Ad                1
2     A          B      Ad                2             
3     A          C      Organic           1           
4     A          D      Organic           2          
5     A          E      Organic           3
6     A          F      Featured_Snippet  1           

Trying to add new data that increments on this table using sequence has not provided the desired output that I need which to increment based off of the max(Id). Tried to use ALTER SEQUENCE and use max(Id) as a variable to pass into sequence but no luck so far. Appreciate any recommendations!

Desired Output

Id   Keyword    URL    Type              Position_Group 
1     A          A      Ad                1
2     A          B      Ad                2             
3     A          C      Organic           1           
4     A          D      Organic           2          
5     A          E      Organic           3
6     A          F      Featured_Snippet  1           
--
7     A          G      Organic           4
8     A          H      Organic           5

CodePudding user response：

I think you are trying to implement SCD and you want the surrogate keys to be incremented by 1 - each time a new record is inserted - if this is what you are looking for then I wouldn't recommend you to use sequence.

You should use "autoincrement" concept.

create or replace TABLE test_surrogate_key ( test_ID NUMBER(38,0) NOT NULL autoincrement, test_name VARCHAR(2), );

Let me know if this helps.

CodePudding user response：

If you run that first line repeatedly, this is why you always get id starting at 1. Which I think you have said is a problem..

CREATE OR REPLACE SEQUENCE id_sequence START WITH 1 INCREMENT BY 1 ;

You CREATE table, if commented out to just be a SELECT

--CREATE OR REPLACE TABLE serp_table AS  
WITH serp_tmp(Keyword, URL, Type, Position_Group) as (
    SELECT * from VALUES 
    ('bob', 'https://bobthebuild.com/', 'Ad', 1),
    ('A', 'https://asite.com/', 'Organic', 1),
    ('A', 'https://aasite.com/', 'Organic', 2)
)
SELECT  
    id_sequence.nextval AS Id, 
    Keyword, URL, Type, Position_Group
FROM serp_tmp;

gives:

second run gives:

So if we don't recreate the sequence and how it's used it works how I would expect.

I guess I am still struggling to understand what your struggle is.