perintah-insert-mysql

7 Variasi Perintah INSERT di MySQL yang Sering Terlupakan

Data Manipulation Language (DML) merupakan bagian dari perintah SQL (Structured Query Language) yang terdiri dari berbagai perintah untuk memanipulasi data di dalam suatu database. Empat perintah utama yang termasuk dalam perintah DML adalah perintah SELECT, INSERT, UPDATE dan DELETE. MySQL sebagai salah satu software database terkemuka tentunya mendukung keempat perintah DML tersebut dengan sangat baik. Di dalam tutorial kali ini, akan dibahas mengenai perintah INSERT dimana ternyata cukup banyak variasi perintah INSERT di MySQL yang sering terlupakan (terabaikan), namun sebenarnya sangat berguna.

Selain bentuk dasar perintah INSERT, saya mencatat setidaknya ada 7 (tujuh) variasi bentuk perintah INSERT yang saya yakin suatu saat akan berguna untuk Anda. Berikut ini ketujuh perintah INSERT tersebut yang selanjutnya akan saya jelaskan satu per satu.

  1. INSERT INTO (field1, field2, …) VALUES (…)
  2. INSERT … SELECT …
  3. INSERT IGNORE …
  4. INSERT DELAYED …
  5. INSERT LOW PRIORITY | HIGH PRIORITY …
  6. INSERT … ON DUPLICATE KEY UPDATE
  7. REPLACE INTO …

Untuk mempermudah pemahaman terhadap tutorial ini, maka seluruh contoh yang disajikan akan mengacu pada struktur tabel sebagai berikut:

Nama tabel: tblmhs

mysql> desc tblmhs;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| nim      | varchar(10) | NO   | PRI | NULL    |       |
| nama     | varchar(40) | NO   |     | NULL    |       |
| alamat   | text        | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Di sini tidak saya bahas mengenai bagaimana membuat struktur tabel di atas, karena sudah saya bahas pada tutorial mengenai perintah dasar MySQL. Lebih lengkapnya, Anda juga bisa pelajari buku gratis MySQL yang dapat didownload di website ini juga.

Bentuk Dasar Perintah INSERT

Sebelum membahas variasi dari perintah INSERT, alangkah baiknya kita mengingat kembali mengenai bagaimana bentuk dasar dari perintah INSERT tersebut. Perintah INSERT pada dasarnya merupakan perintah yang dapat digunakan untuk memasukkan data (record) ke sebuah tabel di database. Bentuk perintah ini cukup sederhana. Berikut ini contoh sederhana perintah INSERT untuk memasukkan data seorang mahasiswa dengan nim ‘1011500100‘, nama ‘ACHMAD SOLICHIN‘ dan alamat di JAKARTA SELATAN.

INSERT INTO tblmhs VALUES('1011500100', 'ACHMAD SOLICHIN', 'JAKARTA SELATAN');

Hal yang penting dari contoh perintah INSERT di atas adalah urutan dari nilai data yang akan ditambahkan harus sama dengan urutan field (kolom) di tabel. Urutan field di dalam tabel tblmhs adalah nim, nama lalu alamat, sehingga waktu melakukan INSERT urutan seharusnya juga nim, nama dan alamat. Jika urutan tidak sesuai, maka data yang dimasukkan bisa jadi menempati kolom yang salah juga

#1. INSERT INTO tabel (field1, field2) VALUES …

Pada bentuk dasar perintah INSERT di atas, kita harus menyebutkan nilai dari seluruh kolom. Jadi jumlah nilai yang akan ditambahkan harus sesuai dengan jumlah kolom dalam tabel. Namun ada kalanya kita hanya ingin menambahkan sebagian dari kolom tabel, jadi tidak semua kolom kita masukkan. Jika demikian, variasi perintah INSERT yang pertama ini tentu tepat untuk digunakan. Kita menyebutkan kolom-kolom yang akan di-insert-kan di belakang nama tabel. Urutannya juga tidak harus sesuai dengan urutan di dalam struktur tabel. Sebagai contoh, kita akan memasukkan data nim dan nama seorang mahasiswa, tanpa memasukkan alamatnya. Urutan fieldnya sengaja dibalik, nama terlebih dahulu baru nim. Berikut perintahnya:

INSERT INTO tblmhs (nama, nim) VALUES('CHOTIMATUL MUSYAROFAH', '1012501983');

Terlihat pada contoh di atas bahwa urutan yang harus disesuaikan adalah urutan field yang berada di belakang nama tabel ‘tblmhs’ dan urutan data (values) yang akan di-insert-kan.

#2. INSERT … SELECT …

Ada kalanya kita ingin memindahkan atau meng-copy sejumlah data yang berada pada suatu tabel ke tabel yang lainnya. Singkatnya copy data antar tabel. Untuk menyelesaikan permasalahan tersebut, tentunya sangat kurang efektif jika kita harus melakukan insert satu per satu. Untuk melakukan copy record antar tabel, akan lebih efektif jika kita menggunakan bentuk perintah “INSERT … SELECT …” yang menggabungkan proses insert (menambahkan data) dan select (mengambil data). Sebagai contoh, kita ingin memindahkan data nim dan nama mahasiswa yang berada di tabel tblpendaftaran ke tabel tblmhs, berikut ini perintahnya:

INSERT INTO tblmhs(nim, nama) SELECT nim, nama FROM tblpendaftaran;

#3. INSERT IGNORE

Saya pernah membuat suatu absensi pada suatu perusahaan dimana data absensi diimport dari suatu file teks hasil keluaran dari mesin finger. Setelah diimport, data absensi karyawan akan diperiksa dan dilengkapi oleh staf HRD. Proses import dari teks file dapat terjadi berulang-ulang sehingga diperlukan mekanisme agar pada saat proses import data yang mungkin sudah diedit oleh staf HRD tidak tertimpa. Jadi intinya, saat proses import, harus diperiksa apakah data absensi yang akan dimasukkan ke tabel sudah ada atau belum. Jika sudah ada, maka tidak perlu dilakukan insert, namun jika tidak ada maka lakukan proses import. Awalnya terpikir untuk melakukan pengecekan data ke database terlebih dahulu sebelum insert, namun dari isi efektivitas tentu hal ini tidak baik, apalagi jika datanya cukup banyak. Beruntung kemudian ketemu bentuk perintah INSERT IGNORE … di MySQL. Dengan perintah ini, permasalahan tersebut dapat terselesaikan hanya dengan satu perintah.

Dalam contoh berikut ini, akan melakukan insert data ke tabel tblmhs hanya jika data nim belum ada di tabel. Pemeriksaan data didasarkan pada field yang menjadi primary key. Jadi jika terjadi duplikasi primary key maka data akan diabaikan (ignored).

INSERT IGNORE INTO tblmhs VALUES('1012501983','BUNGA', 'TANGERANG');

#4. INSERT DELAYED

Bentuk perintah INSERT DELAYED … merupakan bentuk perintah yang mungkin akan kita perlukan saat membuat suatu aplikasi transaksional dengan beberapa pengguna dalam satu waktu. Dengan tambahan perintah “DELAYED” maka proses menambahkan data akan ditangguhkan hingga tidak ada user yang mengakses tabel. Contoh berikut ini menggambarkan bagaimana perintah tersebut:

INSERT DELAYED INTO tblmhs (nim, nama) VALUES ('1011500121', 'LINTANG', 'TANGERANG');

Penjelasan lebih lanjut mengenai perintah ini dapat dilihat di manual MySQL.

#5. INSERT LOW PRIORITY | HIGH PRIORITY

Tambahan perintah LOW PRIORITY atau HIGH PRIORITY dibelakang perintah INSERT dapat digunakan untuk mengatur prioritas dari perintah INSERT yang akan dijalankan. Perintah dengan prioritas lebih tinggi tentu akan didahulukan untuk dijalankan. Sama seperti bentuk perintah INSERT DELAYED, perintah ini juga berguna saat kita “bermain” dengan proses transaksional. Berikut ini contoh sederhananya:

INSERT INTO tblmhs VALUES('1111500111', 'INDAH', 'JAKARTA');

#6. INSERT … ON DUPLICATE KEY UPDATE

Variasi perintah INSERT ini mungkin suatu saat akan kita perlukan. Misalnya pada kasus dimana pada proses insert sejumlah data, jika datanya sudah ada, dalam arti terjadi duplikasi primary key, maka lakukan perintah update untuk kolom tertentu. Sebagai contoh, kita akan melakukan penambahan data ke tblmhs, jika datanya sudah ada, maka update field alamat dengan alamat yang baru. Berikut ini perintahnya:

INSERT INTO tblmhs (nim, nama, alamat) VALUES('1111500111', 'WULAN', 'MALANG') ON DUPLICATE KEY UPDATE alamat='JAKARTA';

Untuk penjelasan lebih rinci terkait perintah ini dapat dilihat di dokumentasi MySQL (http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html).

#7. REPLACE INTO …

Saya sangat senang dengan adanya bentuk perintah REPLACE di MySQL ini. Perintah ini sangatlah membantu. Contohnya saat saya ingin melakukan penambahan data secara massal seperti pada kasus import absensi karyawan di atas. Jika pada proses import, kita menginginkan agar data selalu up to date, maka jika data sudah ada harus dilakukan proses update. Perintah REPLACE INTO berperan untuk melakukan dua tugas sekaligus, yaitu INSERT jika datanya belum ada dan UPDATE jika datanya sudah ada. Berikut ini contoh perintah ini:

REPLACE INTO tblmhs VALUES('1011500100', 'ACHMAD SOLICHIN', 'TANGERANG SELATAN');

Demikian ketujuh bentuk variasi perintah INSERT di dalam MySQL yang sering terlupakan namun seringkali kita butuhkan. Semoga postingan ini bermanfaat untuk kita semua. Mari terus semangat berbagi demi Indonesia!

Berbagi itu indah...Share on Facebook0Share on Google+6Tweet about this on TwitterShare on LinkedIn0Pin on Pinterest0Digg this

76 comments

  1. wah cakep nih artikel.. terimakasih. Oh ya, mhn diulas ttg cara mempuslish artikel on schdule tp tidak dengan teknik cron. Jadi misalnya input data berita hari ini tapi terpublish scr otomatis besok atau pekan depan mislanya. Terimkasih

  2. @tob bin tob
    saya kira caranya cukup sederhana, tinggal input berita pada tanggal mendatang, lalu pas menampilkan seleksi berdasarkan tanggal, yang tampil hanya tanggal hari ini dan tanggal2 sebelumnya.

  3. pak solihin . . . saya mau nanya nih gmn insert seluruh value yang ada di <table></table> ke dalam database. jadi seperti program pembelian pak, nyimpen detil pembelian.
    1. kan user memilih barang yang mau di beli(bisa lebih dari 1)
    2. lalu saya tampilkan ke page tapi belum di insert ke dalam database(baru di tampilih via <table></table>
    3. nah ketika saya mau simpan pembelian nya saya bingung nyimpen value di dalam <table></table> tersebut pak please pak solihin . . . help me.

    1. @andi. mungkin solusi paling enaknya dgn menggunakan ajax (jquery akan lebih simple). ada 2 proses yg mungkin: (1) saat menambahkan barang, langsung masukkan ke tabel di web lalu saat ditekan tombol simpan baru disimpan semua ke DB atau (2) saat menambahkan barang, langsung simpan ke DB baru ditampilin ke tabel di web.

  4. Sangat menarik… meski masih bingung,,, maklum gaptek… tak peajari lagi insya ALLAH…
    Terimakasih pak sholihin…. semoga tambah manfaat ilmunya,,,,

  5. artikelna bagus pa,,,,
     
     
    pa,,,
    klo autonumber yg berdasakan tgl, tahun dan bulan ntu sql kyk gmn y???
    saia pake vb.net n databasenya access
    misal
    123/CA/Mo-Int/20/VI/11
    123 = no urut
    CA = inisial unit
    Mo-Int = inisial order internal
    20 = tanggal order dibuat
    IV = (Angka romawi) bulan order tersebut dibuat
    11 = tahun order tersebut dibuat

  6. pak solihin mohon bantuannya pa, gmana cara memecah field urai ke field lain dalam satu tabel, misal :  

    No
    nama
     tgl
     tm
    urai
    id
    paket
    date
     time
    ctrl

    1
    Surya
     12-10-2011
     21:35
    #TT#Paket I#2011-10-12#21:30#
    TT
    Paket I
    2011-10-12
     21:30
    ok

    kondisi
    1. if ctrl != ok maka split urai_sms ke field detail dan simpan
    2. apabila field ctrl = ok tidak di split lagi

    sebelumnya atas bantuannya terimakasih

    surya 

  7. makasih pak atas share ilmunya.
    Pak saya mau tanya. Gimana caranya menginsert banyak record pada 1 tabel.
    Contohnya saat penggunaan shopping cart. Ketika data pada shopping cart lebih dari 1
    baris / record, kemudian mau dimasukkan ke 1 tabel hanya dengan 1 tombol gimana caranya.
    Mohon bantuannya pak. Makasih sebelumnya

    1. secara konsep sama aja kayak kita menginsertkan 1 data/record, kalo untuk banyak data dan pengin insert sekaligus, ya tinggal dimasukin aja perulangan…jadi insertnya tetep satu-satu.

  8. Dengan menggunakan perintah insert … ignored,…
    apakah implementasinya juga bisa dilakukan jika data yang dimasukkan namanya sama,… jd meskipun primary key nya beda tetapi nama yang dimasukkan sama,…
    Ex: insert into mhs (‘nim’, ‘namamhs’) values (’30’, ‘dede’);

    nama dede sudah terdaftar sebelumnya,… dengan perintah ignored tsb,.. apakah dede tetap bisa masuk,… atau tidak tersimpan,..

  9. udah saya coba ternyata dede masih bisa tersimpan dengan menggunakan perintah insert ignore,…

    gmn caranya meskipun yang diprimary key beda namun jika data yang dimasukkan namanya sama maka datanya tidak masuk dalam database MySQL,…

  10. Pak saya mau nanya… misal saya punya tabel A dan tabel B… misal saya ingin insert data di tabel A dari tabel B… bila datanya belum ada di tabel A maka INSERT… bila datanya sudah ada maka UPDATE… dan bila data di tabel A tidak ada di tabel B, maka hapus data (yang tidak ada tsb) di tabel A…
    Bagaimana syntax SQLnya???

    Mohon Pencerahannya Pak… Terima Kasih sebelumnya….

    1. Perlu 2 langkah query:

      REPLACE INTO tabel_A SELECT * FROM tabel_B

      DELETE FROM tabel_A a WHERE a.id NOT IN (SELECT b.id FROM tabel_B)

      Belum dicoba tapi semoga bisa mencerahkan.

  11. Saya mau tanya Mas Ahmad… bagaimana caranya memasukkan data massal ke tabel penyimpanan. Bagaimana syntax sql-nya kalau dikaitkan dengan program java. Trims Wasalam. Ambo

    1. perintahnya ya sama, pake INSERT INTO juga. kalo datanya dalam bentuk teks, bisa dibaca pake java, lalu eksekusi secara berulang perintah INSERT INTO.

  12. ada yang ingin saya tanyakan mas, untuk yang insert….select

    INSERT INTO tblmhs(nim, nama) SELECT nim, nama FROM tblpendaftaran
    kalau kasunya begini ada 2 tabel a dan b. tabel a fieldnya nim, nama. dan tabel b nim, nama, tanggal. untuk input data ditabel b bagaimana mas, utk tabel b nim, nama diambil dari tabel a. dan utk field tanggal itu memakai date(yyy-mm-dd). itu query nya bagaimana mas?
    mohon pencerahannya

  13. pak, dari berbagai cara di atas, kan semuanya me-insert atau me-replace, berdasarkan primary key nya sudah ada atau belum. kalo misalnya saya ingin me-replace berdasarkan suatu field isi nya uda ada apa belum, tapi bukan merupakan primary key, itu bagaimana ya pak?
    soalnya saya sudah coba pakai if not exist tapi belum juga berhasil..
    mohon dijawab pak, terima kasih.

  14. sy mau tanya bbrpa hal :
    1. Saya coba buat 5 event, yang dijalankan setiap hari (tengah malam)sekitar jam 01.00-01.30. Dan sy kasih selisih dengan tenggat waktu 3-4 menit per event tsb. Tapi kenapa yang mysql meng-eksekusi nya setiap pukul 04.30 ?? itu apa ya sebabnya?

    2. sy punya View dengan nama A, dan table B. Table B mempunyai field ‘tanggal’ dan ‘jumlah’. Pertanyaannya-> sy ingin membuat event yg bertujuan INSERT ke table B tsb. Field tanggal=NOW(), dan field jumlah=COUNT data dari View A. Sy dah coba tapi gagal terus. bagaimana caranya ya om.
    mohon pencerahan. Terima Kasih sebelumnya.

  15. pak, sy mau insert 2 tabel sekaligus dalam 1 file yaitu tabel header_po (no_po,tgl_po, kd_suplier) dan tabel detail_po(no_po, kd_barang)
    gimana ya pak coding insertnya ?
    terimakasih

  16. true));
    } else {
    echo json_encode(array(‘msg’=>’Some errors occured.’));
    }
    ?>

    pak mau tanya kalau ingin mengeksekusi sekaligus antara insert dan update bagaiaman yah??? mohon bantuannya terima kasih

  17. Dari dulu nyari solusi baru ktemu sekarang, terimakasih banyak pak solihin. Ntar kalo ada ksulitan lagi mau nanya ya…

  18. Dear Mr Achmad,

    Mau nanya nih, saya punya dua tabel dengan fieldnya:
    tabelA -> Nama, nomorID, Alamat
    tabelB -> nomorID, Tanggal

    bagaimana melakukan INSERT INTO tabelA hanya jika nomorID pada tabelB exist.

    Terima kasih atas pencerahannya..

  19. mau tanya nih,
    bisa ga kalo misalnya ada 28 field dalam 1 tabel… trus insert to tabel nya cm 15 field.. bisa ga sih?

  20. Pak dosen, saya mau memiliki pertanyaan dengan sebuah contoh kasus, kira-kira kasusnya seperti ini:
    -ada sebuah tabel ‘pelanggan’ dengan field ‘nama’ dan field ‘nomor_handphone’ yang sudah terisi.
    -semua nama dari field ‘nama’ ditampilkan melalui list menu (form html + coding PHP)
    bagaimana caranya memasukkan ke dalam tabel lain misal tabel ‘kontak’, data field ‘nomor_handphone’ dari nama yang dipilih.
    terima kasih.

  21. pa mau tanya, saya memiliki 2 tabel yaitu pasien_masuk (no_rm, nama, tgl_masuk,status) dan pasien_keluar (no_rm, tgl_keluar, cara_keluar), saya mau menambahkan data pd table pasien_keluar sekaligus bisa mengubah isi field status pd table pasien_masuk, gmna sintax sql.nya ? Trims..

  22. Trim atas penjelsannya, kebetulan saya sedang mencari cara mengupdate data, tetapi jika belum ada maka insert data.
    Bagaimana cara mencari data
    Record nya: Nama, Mata_Pelajaran, Nilai
    contoh record
    Andi, Matematika,7
    Andi,IPS,8
    Andi,Fisika,9
    Budi, Matematika,10
    Budi,IPS,6
    Budi,Fisika,8
    Anto,Mamatika,7
    Anto,IPS,9
    Anto,Fisika,8
    Bagaimana perintah SQLnya agar didapat nilai tertinggi saja per mata_pelajaran, agar hasilnya keluarnya:
    Budi,Matematika,10
    Anto,IPS,9
    Andi,Fisika,9
    trimakasih.

  23. Bagaimana cara mengabungkan perintah UPDATE, SELECT dan INSERT ?
    Jadi saya ingin memindahkan tabel calon siswa ke tabel siswa dan mengubah kolom status di tabel siswa ? Terima Kasih, Mohon Bantuannya

  24. cari muter muter di google tentang mengcopy data antar tabel, cuma disini yang bener bener jitu.
    makasi om ilmunya, bermanfaat x bah.

  25. untuk insert select apakah bisa jika nama field nya tidak sama ? misalnya INSERT INTO tblcollector(collector) SELECT txt_collector FROM tblutama;

  26. bagaimana coding mengambil data dari tabel A dan disimpan ke tabel B, setelah data di tabel B tersimpan lalu yang di tabel A itu terhapus ?

  27. Makasih pak atas share ilmunya…
    Pak mohon bantuannya.. saya coba bwt import data dari text ke MySQL dan data di teksnya ada tanda kutip satu yg berada di nama mahasiswa… nah pas import berjalan aplikasinya langsung error ada pesan kurang lebih seperti ini your MySQL server version for the right syntax to use near bla bla….
    gimana yah pak cara mengatasinya di aplikasinya? secara saya hilangkan yg namanya ada tanda kutip itu program baru bisa import…..

    Terimakasih pak
    Syaukani

  28. thanks atas jawabannya mengenai proses insert yang mengambil data dari tabel lain
    ternyata beda dengan dengan perintah create dengan data dari tabel lain yah 🙂

  29. maaf pak, saya mau tanya…
    gimana caranya untuk masukin beberapa field dalam 1 primary key ya pak?
    contohnya DB penjualan, dalam 1 no faktur berisi beberapa barang…
    bisa tolong dibantu pak?
    terimakasih sebelumnya….

  30. maaf pak saya mau tanya, bagaimana cara mempercepat proses jalanya exsekusi suatu printah mysql jika datanya lebih dari 100rb record?misal…mau menampilkan data pdhl udah di limit ttep lelet, atau mau update data ke seluruh data…

  31. artikel sangat strategis untuk solusi yang saya butuhkan, terimakasih.
    Lebih lanjut mohon saran adakah solusi strategis bilamana saya updatekan data dari tabel dalm lokal host ke 2 macam table di mysql webserver, masing masing tabel strukturmya beda velue datanya sesuai, proses dilakukan transaksional multi user, terimakasih

  32. Gmn perintah insert untuk menyisipkan record pada tabel yg sdh ada data recordnya..Misal ada data entry nomor 1 s/d 4, sewaktu proses entry ternyata ada data yg terlupa tidak dientry

  33. Akhirnya nemu solusi setelah beberapa minggu jatuh bangun! Hahaha awalnya pakai perintah

    INSERT INTO tabel_baru SELECT * FROM tabel_lama

    pake perintah itu kok tiba-tiba nda fungsi lagi. Terimakasih atas post yang sangat bermanfaat ini. Good Jod gaes!

  34. Keren nie..pak gmn cara buat costing sheet untuk resep..saya coba pake insert tp tetap susah masukin rumus ke inser tsb..mohon petunjuk…

  35. Assalamualaikum wr.wbt.

    Mas, Saya juga lagi mau buat absensi, tapi sistem CRUD sederhana.
    Bagaimana perintah mencegah update jika data ID Karyawan tidak sesuai di database..??

    alurnya (ID input manual, tanggal dan jama input otomatis) :
    1. Add data (ID, tanggal, jam) sebagai info MASUK
    2. Edit data (tanggal, jam) sebagai info PULANG
    3. Menghitung selisih waktu keduanya.
    * Pada saat edit data, user tidak bisa menyimpan/update jika ID yang dimasukkan di field tidak sesuai di database pada alur 1.

    Thanks..

Leave a Reply

Your email address will not be published. Required fields are marked *