I have searched for an answer to my query and surprisingly have found no answer. I do find it a bit difficult to believe that almost 100% of people INSERT VALUES as opposed to INSERT SELECTing.
So, my query is:
CREATE SET TABLE cdm.acl_test ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( ID_COL INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 MINVALUE -2147483647 MAXVALUE 100000000 NO CYCLE), CIDPERSID INTEGER, PRDID INTEGER) UNIQUE PRIMARY INDEX ( ID_COL );
Now... How do I INSERT using a select statement?
What I have done is:
INSERT INTO tablename SELECT 1 ,CUSTOMER_ID ,PRODUCT_ID FROM CUSTOMER_PRODUCTS;
This seems to have worked, but I just want to ensure this is correct.
When records are stored with an identity column, the system gets a batch of numbers for each AMP (assuming they go in by Insert Into/ Select). There are a number of reasons why they may not be consecutive, and there is no guarantee that the first input record is number 1, second number 2, etc. In fact it is extremely unlikely. This is described in SQL Ref/ Data Definition Statement/ Create Table/ Column Clause.
If you want them numbered, you can either put them in a work table and number them with Row_Number, use an Inmod to number them on input, or insert each individually using Bteq. For high volumes and option 3, kick off the process, go get married/ have children and check its process just before your retirement party! (So not recommended.)
Hi All, I have my identity column in negative (as stated above). When I try to insert a record, it goes to number lesser than minimum limit..
ID_NUM BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH -99999999999999999 INCREMENT BY 1 MINVALUE -99999999999999999 MAXVALUE -1 NO CYCLE) Whenever I insert a record, id_NUM value show as -10000000000000000 (-99999999999999999 - 1 instead of -99999999999999999+1).. (note number of 9s is 17 here)
Can you explain why? But this is not the case with identity defined as -9999999999999999 as lower limit.. (note - number of 9s is 16 here)