Menu
Alright, I've got a question, not really an issue.
I've got a table in my database, fairly small, only 3 columns but potential to grow. I've got two solutions to my problem, but not sure why to use one or the other.
I've got a piece of data, which might or might not already be in the database. Two ways to solve this. I've got the unique ID, so it is easy to check.
INSERT INTO databaseREPLACE INTO, because I've got the ID already.My question now is. Which one is better to use. What are the pros and cons in using either of the 2 results. Or is there a better result?
A note, the data is exactly the same, so there is no chance the record gets updated with a newer value. Thus the REPLACE INTO will insert data which is already there.
403
REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.
INTO' creates the destination table, it exclusively owns that table and is quicker compared to the 'INSERT … SELECT'. Because the 'INSERT … SELECT' inserts data into an existing table, it is slower and requires more resources due to the higher number of logical reads and greater transaction log usage.
INSERT INTO SELECT vs SELECT INTO: Both the statements could be used to copy data from one table to another. But INSERT INTO SELECT could be used only if the target table exists whereas SELECT INTO statement could be used even if the target table doesn't exist as it creates the target table if it doesn't exist.
If you are using Insert or Insert into both will insert the data in Table. However Insert into is basically used to fatch the data from another table using select command and insert into table where you want to insert the data.
REPLACE INTO is not recommended here - you don't really need to replace anything. It does DELETE followed by INSERT, with all the consequences. For example all indexes have to be updated, which leads to unnecessary work and index fragmenting if you use it frequently.
On the other hand there is ON DUPLICATE KEY UPDATE, which is used mainly for counters, but you are not updating your row with increments or any other value changes, so you would have to use weird syntax like SET id=id or something similar.
Checking if the record exists in the database would be the best solution for you, but instead of using another query let mysql do that check for you and use:
`INSERT IGNORE INTO ...`
This way if you try to insert any row with duplicated unique or primary key it simply won't be inserted without generating any error. Note the side effect of possibly missing other error messages, but if you know exactly what you insert you should be fine.
173
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
