Bulk Loading

Database

Bulk Loading

Hi,

Im having a problem to load many records into a table that has primary index but not autoincrement.

Is there's a way to get the last primary index and append it to the data that i will load to the table?

and the data is not only 1 row, likely more rows primary index must be unique.

Thanks.

4 REPLIES

Re: Bulk Loading

You can use the following code to genrate ID column. Although the use of CSUM function is discouraged now.

INSERT   INTO Target_Table_name
(
INDEX_COLUMN
--Other_Column_list
)
SELECT
,System_Generated_Id
FROM
(
SELECT
(CSUM(1, 1) + MAX_INDEX_COLUMN_VALUE) AS System_Generated_Id
--OTHER_COLUMN_LIST
FROM
TABLENAME,
(
SELECT
MAX(INDEX_COLUMN)
FROM
Target_Table_name
)
MAX_INDEX(MAX_INDEX_COLUMN_VALUE)
)
;

Hope it will help you!

Khurram
N/A

Re: Bulk Loading

I don't think it's a good way to insert data without Index column, or PK column. Unless you won't extend this table in the future.

It would be a difficulty thing when you want to add a new column in this table and back fill its history data ....

Re: Bulk Loading

Hi,

The Following Code Snippet will work, 

Assuming that your target table has 3 fields, COLUMNPI,COLUMN1,COLUMN2 and Source hastow fields COL1,COL2.

INSERT INTO TARGET_TABLE
(
COLUMNPI,
COLUMN1,
COLUN2
)
SEL
CASE WHEN TT.MAXI IS NULL
THEN ROW_NUMBER() OVER( ORDER BY COLUMN1,COLUMN2)
ELSE
TT.MAXI + CAST(ROW_NUMBER() OVER( ORDER BY COLUMN1,COLUMN2)
END AS COLUMNPI,
COL1,
COL2
FROM SOURCE_TABLE
LEFT OUTER JOIN
( SEL MAX(COLUMNPI) AS MAXI
FROM
TARGET_TABLE
) TT
ON 1=1
;

The Row_number function will always gives you distinct of values. Or, We can go for the Identity Column.

CREATE MULTISET TABLE TARGET_TABLE
(COLUMNPI INTEGER GENERATED ALWAYS AS IDENTITY
(START WITH 1 INCREMENT BY 1
MINVALUE -2147483647
MAXVALUE 2147483647 NO CYCLE),
COLUMN1,
COLUMN2
);

INSERT INTO TARGET_TABLE
(
COLUMN1,
COLUMN2
)
SELECT
COL1,
COL2
FROM SOURCE_TABLE;

Thanks & Regards,

Adharssh. 

Re: Bulk Loading

You can try using Identity Column in DDL