CODEWITHSUNDEEP
sqlbulkinsert
Tony
Tony

Reputation: 12715

Databases - ID column - identity or not?

I'm did some research about SQL batch inserts - let's say I have 100k items to be inserted, and I set the batch size to 100.

If the ID column is not marked as Identity then that bulk insert will work.

But I found quite interesting (theoretical so far) problem, and I need some opinions:

The problem can be, if e.g. 5 users are making the bulk inserts in the same time, how then safely provide the the ID column value ? I can't just get the table rows count + 1, because in that way all of that 5 users will have the ID duplicates and the bulk insert operation will fail.

Upvotes: 1

Views: 46

Answers (2)

zandi
zandi

Reputation: 704

Should I Use IDENTITY or Not?

Sometimes Another Approach Works Better

Upvotes: 1

user4420255
user4420255

Reputation:

You can use SEQUENCE as an UNIQUE ID generator or try TRIGGER ON INSERT to get a unique ID.

EDIT

With mysql you can build trigger for every row

DELIMITER $$

CREATE TRIGGER adresse_trigger_insert_check
 BEFORE INSERT ON adresse
 FOR EACH ROW BEGIN
 IF NEW.land IS NULL THEN
  SET NEW.land := 'XY';
 END IF;
END$$

DELIMITER ;

Upvotes: 1

Related Questions