[Feature Export Suggestion] Add "DELETE + INSERT" Option

[expired user #9701]'s profile image [expired user #9701] posted 8 years ago in Feature discussion Permalink

German (English below):

Hallo, erstmal top Software! Allerdings habe ich jetzt in den 4 Jahren wo ich diese Software benutze immer wieder einen "Mängel" feststellen können.

Wäre es möglich beim exportieren von ausgewählten Reihe nicht nur "REPLACE" und "INSERT" sondern auch "DELETE + INSERT" auswählen zu können? Das würde das Leben um einiges Leichter machen und die Umsetzung dieses Vorschlages sollte auch keine großartige Zeit beanspruchen.

Vielen Dank

ENGLISH SHORTVERSION:

When exporting SQL Data, it would be a good idea, to also feature a "Delete + INSERT" option. This would further enhance the current features "REPLACE" and "INSERT".

ansgar's profile image ansgar posted 8 years ago Permalink

This is already doable, isn't it? See attached screenshot.

1 attachment(s):
  • sqlexport-delete-insert
[expired user #9701]'s profile image [expired user #9701] posted 8 years ago Permalink

Thanks for the reply! Yes, but I meant the "mark and export" option.

ansgar's profile image ansgar posted 8 years ago Permalink

Yes, that could be a new grid export option.

Electrosa's profile image Electrosa posted 8 years ago Permalink

Is there any difference between REPLACE versus DELETE+INSERT? Doesn't the REPLACE delete a row if already exists before inserting it into the table?

kalvaro's profile image kalvaro posted 8 years ago Permalink

Is there any difference between REPLACE versus DELETE+INSERT? Doesn't the REPLACE delete a row if already exists before inserting it into the table?

REPLACE does not delete rows from target that do not exist in source.

ansgar's profile image ansgar posted 8 years ago Permalink

I'm scratching my head, now that I overthink this DELETE/INSERT over REPLACE preference. When the next query is the INSERT INTO, what's effectively different from the REPLACE format?

DELETE FROM `mytable` WHERE `id`=2;
INSERT INTO `mytable` VALUES (2, 'admin');
DELETE FROM `mytable` WHERE `id`=3;
INSERT INTO `mytable` VALUES (3, 'open');
[expired user #9701]'s profile image [expired user #9701] posted 8 years ago Permalink
DELETE FROM `mytable` WHERE `id`=2 AND id2=20 AND id3=30;
INSERT INTO `mytable` VALUES (2, 20, 30, 'admin');

REPLACE INTO mytable VALUES (2, 20, 30, 'admin');

In fact, this is the same. But(!) there is some cases where you prefer to take more control to the duplicate key check, by doing it manually with "Delete+Insert". (More than 1 Primary Key for instance)

I guess it's really dependant on what someone really needs - In some cases REPLACE INTO is an easy and quick way, but sometimes you want to modify the DELETE-clause by urself.

I have made bad experiences with REPLACE INTO when I inserted alot of data with more than 1 Primary Key. It copied it all to new rows instead of actually replacing them. Since then, I always used DELETE + INSERT to really control what I delete and what I insert.

Therefore it would be a good customizable option.

ansgar's profile image ansgar posted 8 years ago Permalink

A table cannot have more than one primary key, so I guess you mean primary keys with multiple columns in them?

Apart from that, I think these DELETE/INSERT can help MSSQL and PostgreSQL users, which both do not support the REPLACE INTO syntax.

Code modification/commit from ansgarbecker, 8 years ago, revision 9.3.0.5063
Add "SQL DELETEs/INSERTs" to grid export formats. See http://www.heidisql.com/forum.php?t=20929
ansgar's profile image ansgar posted 8 years ago Permalink

SQL DELETEs/INSERTs supported now in build r5063. Please update.

[expired user #9701]'s profile image [expired user #9701] posted 8 years ago Permalink

AWESOME!

Really appreciate the support of Heidisql. Definitly going to suggest it to anyone in the need of a sql program.

[expired user #9701]'s profile image [expired user #9701] posted 8 years ago Permalink

A table cannot have more than one primary key, so I guess you mean primary keys with multiple columns in them?

Yes :P

Please login to leave a reply, or register at first.