All Forums Database
jcgionson09 1 post Joined 04/13
12 Sep 2013
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.
 

M.Saeed Khurram 544 posts Joined 09/12
13 Sep 2013

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

logao 1 post Joined 09/13
13 Sep 2013

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 ....

Adharssh 36 posts Joined 08/13
13 Sep 2013

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. 

Share the Knowledge. Feel the Happiness, When you share/Teach it.

15 Sep 2013

You can try using Identity Column in DDL

You must sign in to leave a comment.