INSERT INTO .. SELECT FROM not selecting new rows created by statement

Database

INSERT INTO .. SELECT FROM not selecting new rows created by statement

I am trying to exponentially double the size of a table using the INSERT INTO and SELECT ... FROM statements. I have added increments where needed to the correct fields in the SELECT part of the command.

 

I begin with 9 rows of data, run the statement once and end up with 18.

 

However, when I run it again, the command only selects the original 9 rows, not the original 9 PLUS the new 9 I have created.

 

Below is the command I currently have, is there any way that I can get it to select all the data inside the table each time, rather than just the original 9 rows?

 

INSERT INTO DATA_TEST
(T1_CUSTOMERNO_SK, T1_EFF_DT, T1_EXPIR_DT, T1_DATA_SRC_CD, T1_DATA_SUB_SRC_CD,
                                T4_ACCOUNTNO_SK, T4_CUSTOMERNO_SK, T4_EFF_DT, T4_EXPIR_DT, T4_ACCOUNTLINKSTATUS,
                                T4_DATA_SRC_CD, T4_DATA_SUB_SRC_CD, T5_ACCOUNTNO_SK, T5_EFF_DT,
                                T5_EXPIR_DT, T5_PRODUCTID_SK, T5_CLOSEDATE, T5_ACCOUNTSTATUS,
                                T5_HOLDER2R85, T5_PRODUCTGROUPNUMBER, T5_DATA_SRC_CD, T5_DATA_SUB_SRC_CD,
                                T5_CURRENCY, T6_EFF_DT, T6_EXPIR_DT, T6_PRODUCTGROUPNUMBER, T6_PRODUCTID_SK,
                                T6_DATA_SRC_CD, T6_DATA_SUB_SRC_CD, T9_EFF_DT, T9_EXPIR_DT, T9_ACCOUNTNO_SK,
                                T9_DATA_SRC_CD, T9_DATA_SUB_SRC_CD, T9_CURRENCY, T16_ACCOUNTNO,
                                T16_ACCOUNTNO_SK, T16_DATA_SRC_CD, T16_DATA_SUB_SRC_CD, T11_TRANDATE,
                                T11_UPDATEDATE, T11_DEBITCREDITFLAG, T11_TRANCODE, T11_TRANDETAIL,
                                T11_AMOUNT, T11_TRANMETHOD, T11_TRANSACTIONID, T11_DATA_SRC_CD,
                                T11_DATA_SUB_SRC_CD, T11_LOAD_DT, T11_ACCOUNTNO_SK)
								
SELECT                T1_CUSTOMERNO_SK + 1, T1_EFF_DT + 1, T1_EXPIR_DT + 1, T1_DATA_SRC_CD,
                                T1_DATA_SUB_SRC_CD, T4_ACCOUNTNO_SK + 100, T4_CUSTOMERNO_SK + 1, T4_EFF_DT + 1,
                                T4_EXPIR_DT + 1, T4_ACCOUNTLINKSTATUS, T4_DATA_SRC_CD, T4_DATA_SUB_SRC_CD,
                                T5_ACCOUNTNO_SK + 100, T5_EFF_DT + 1 , T5_EXPIR_DT + 1, T5_PRODUCTID_SK + 10, T5_CLOSEDATE,
                                T5_ACCOUNTSTATUS, T5_HOLDER2R85, T5_PRODUCTGROUPNUMBER, T5_DATA_SRC_CD,
                                T5_DATA_SUB_SRC_CD, T5_CURRENCY, T6_EFF_DT + 1, T6_EXPIR_DT + 1, T6_PRODUCTGROUPNUMBER,
                                T6_PRODUCTID_SK + 10, T6_DATA_SRC_CD, T6_DATA_SUB_SRC_CD, T9_EFF_DT +1,
                                T9_EXPIR_DT + 1, T9_ACCOUNTNO_SK + 100, T9_DATA_SRC_CD, T9_DATA_SUB_SRC_CD,
                                T9_CURRENCY, T16_ACCOUNTNO + 100, T16_ACCOUNTNO_SK + 100, T16_DATA_SRC_CD,
                                T16_DATA_SUB_SRC_CD, T11_TRANDATE + 1, T11_UPDATEDATE + 1, T11_DEBITCREDITFLAG,
                                T11_TRANCODE, T11_TRANDETAIL, T11_AMOUNT + 1, T11_TRANMETHOD, T11_TRANSACTIONID + 100,
                                T11_DATA_SRC_CD, T11_DATA_SUB_SRC_CD, T11_LOAD_DT + 1, T11_ACCOUNTNO_SK + 100
								
FROM    DATA_TEST;
1 REPLY
Senior Apprentice

Re: INSERT INTO .. SELECT FROM not selecting new rows created by statement

Your target table is probably defined as a SET table, which removes the duplicate rows you created with your Select.

 

Either create the table as MULTISET or change the calculations for every Insert.