Menyisipkan data

  • 3 menit

Transact-SQL memberikan banyak cara untuk menyisipkan baris ke dalam tabel.

Pernyataan INSERT

Pernyataan INSERT digunakan untuk menambahkan satu atau beberapa baris ke tabel. Ada beberapa bentuk pernyataan.

Sintaksis dasar dari pernyataan INSERT sederhana diperlihatkan di bawah ini:

INSERT [INTO] <Table> [(column_list)]
VALUES ([ColumnName or an expression or DEFAULT or NULL],…n)

Dengan bentuk pernyataan INSERT ini, yang disebut INSERT VALUES, Anda dapat menentukan kolom yang akan memiliki nilai yang ditempatkan di dalamnya dan urutan penyajian data untuk setiap baris yang dimasukkan ke dalam tabel. column_list adalah opsional tetapi disarankan. Tanpa column_list, pernyataan INSERT akan mengharapkan nilai untuk setiap kolom dalam tabel dalam urutan di mana kolom ditentukan. Anda juga dapat memberikan nilai untuk kolom tersebut sebagai daftar yang dipisahkan koma.

Saat mencantumkan nilai, kata kunci DEFAULT berarti nilai yang telah ditentukan sebelumnya, yang ditentukan saat tabel dibuat, akan digunakan. Ada tiga cara default dapat ditentukan:

  • Jika kolom telah ditetapkan memiliki nilai yang dihasilkan secara otomatis, nilai tersebut akan digunakan. Nilai yang dihasilkan secara otomatis akan dibahas nanti dalam modul ini.
  • Saat tabel dibuat, nilai default dapat diberikan untuk kolom, dan nilai tersebut akan digunakan jika DEFAULT ditentukan.
  • Jika kolom telah ditentukan untuk memungkinkan nilai NULL, dan kolom tersebut bukan kolom yang dibuat secara otomatis dan tidak memiliki default yang ditentukan, NULL akan dimasukkan sebagai DEFAULT.

Detail pembuatan tabel berada di luar cakupan modul ini. Namun, sering kali berguna untuk melihat kolom apa yang ada dalam tabel. Cara termudah adalah dengan hanya menjalankan pernyataan SELECT pada tabel tanpa mengembalikan baris apa pun. Dengan menggunakan kondisi WHERE yang tidak pernah TRUE, tidak ada baris yang dapat dikembalikan.

SELECT * FROM Sales.Promotion
WHERE 1 = 0;

Pernyataan ini akan memperlihatkan kepada Anda semua kolom dan namanya, tetapi tidak akan menampilkan tipe data atau properti apa pun, seperti apakah NULL diizinkan, atau jika ada nilai default yang ditentukan. Contoh output dari kueri mungkin terlihat seperti ini:

PromotionName

StartDate

ProductModelID

Diskon

Catatan

Untuk memasukkan data ke dalam tabel ini, Anda dapat menggunakan pernyataan INSERT seperti yang diperlihatkan di sini.

INSERT INTO Sales.Promotion (PromotionName,StartDate,ProductModelID,Discount,Notes)
VALUES
('Clearance Sale', '01/01/2021', 23, 0.1, '10% discount');

Untuk contoh di atas ini, daftar kolom dapat dihilangkan, karena kita memberikan nilai untuk setiap kolom dalam urutan yang benar:

INSERT INTO Sales.Promotion
VALUES
('Clearance Sale', '01/01/2021', 23, 0.1, '10% discount');

Misalkan tabel ditentukan sedemikian rupa sehingga nilai default tanggal saat ini diterapkan ke kolom StartDate, dan kolom Catatan memungkinkan nilai NULL. Anda dapat menunjukkan bahwa Anda ingin menggunakan nilai ini secara eksplisit, seperti ini:

INSERT INTO Sales.Promotion
VALUES
('Pull your socks up', DEFAULT, 24, 0.25, NULL);

Atau, Anda dapat menghilangkan nilai dalam pernyataan INSERT, dalam hal ini nilai default akan digunakan jika ditentukan, dan jika tidak ada nilai default tetapi kolom mengizinkan NULL, NULL akan dimasukkan. Jika Anda tidak memberikan nilai untuk semua kolom, Anda harus memiliki daftar kolom yang menunjukkan nilai kolom mana yang Anda berikan.

INSERT INTO Sales.Promotion (PromotionName, ProductModelID, Discount)
VALUES
('Caps Locked', 2, 0.2);

Selain menyisipkan satu baris pada satu waktu, pernyataan INSERT VALUES dapat digunakan untuk menyisipkan beberapa baris dengan memberikan beberapa kumpulan nilai yang dipisahkan koma. Kumpulan nilai juga dipisahkan dengan koma, seperti ini:

(col1_val,col2_val,col3_val),
(col1_val,col2_val,col3_val)

Daftar nilai ini dikenal sebagai konstruktor nilai tabel. Berikut adalah contoh menyisipkan dua baris lagi ke dalam tabel dengan konstruktor nilai tabel:

INSERT INTO Sales.Promotion
VALUES
('The gloves are off!', DEFAULT, 3, 0.25, NULL),
('The gloves are off!', DEFAULT, 4, 0.25, NULL);

INSERT ... SELECT

Selain menentukan sekumpulan nilai literal dalam pernyataan INSERT, T-SQL juga mendukung penggunaan hasil operasi lain untuk memberikan nilai INSERT. Anda dapat menggunakan hasil dari pernyataan SELECT atau output dari prosedur tersimpan untuk menyediakan nilai untuk pernyataan INSERT.

Untuk menggunakan INSERT dengan SELECT berlapis, buat pernyataan SELECT untuk menggantikan klausul VALUES. Dengan bentuk ini, yang disebut INSERT SELECT, Anda dapat menyisipkan kumpulan baris yang dikembalikan oleh kueri SELECT ke dalam tabel tujuan. Penggunaan INSERT SELECT menyajikan pertimbangan yang sama seperti INSERT VALUES:

  • Anda dapat secara opsional menentukan daftar kolom setelah nama tabel.
  • Anda harus memberikan nilai kolom atau DEFAULT, atau NULL, untuk setiap kolom.

Sintaksis berikut menggambarkan penggunaan INSERT SELECT:

INSERT [INTO] <table or view> [(column_list)]
SELECT <column_list> FROM <table_list>...;

Catatan

Kumpulan hasil dari prosedur tersimpan (atau bahkan kumpulan dinamis) juga dapat digunakan sebagai input untuk pernyataan INSERT. Bentuk INSERT ini, disebut INSERT EXEC, secara konseptual mirip dengan INSERT SELECT dan akan menyajikan pertimbangan yang sama. Namun, prosedur tersimpan dapat mengembalikan beberapa kumpulan hasil, sehingga diperlukan perawatan ekstra.

Contoh berikut menyisipkan beberapa baris untuk promosi baru bernama Get Framed dengan mengambil ID model dan nama model dari tabel Production.ProductModel, untuk setiap model yang berisi "frame" di namanya.

INSERT INTO Sales.Promotion (PromotionName, ProductModelID, Discount, Notes)
SELECT DISTINCT 'Get Framed', m.ProductModelID, 0.1, '10% off ' + m.Name
FROM Production.ProductModel AS m
WHERE m.Name LIKE '%frame%';

Tidak seperti kueri bertumpuk, SELECT berlapis yang digunakan dengan INSERT tidak diapit tanda kurung.

SELECT ... INTO

Pilihan lain untuk menyisipkan baris, yang mirip dengan INSERT SELECT, adalah pernyataan SELECT INTO. Perbedaan terbesar antara INSERT SELECT dan SELECT INTO adalah SELECT INTO tidak dapat digunakan untuk menyisipkan baris ke dalam tabel yang sudah ada, karena selalu membuat tabel baru berdasarkan hasil SELECT. Setiap kolom di tabel baru akan memiliki nama, tipe data, dan nullability yang sama dengan kolom (atau ekspresi) yang sesuai dalam daftar SELECT.

Untuk menggunakan SELECT INTO, tambahkan INTO <new_table_name> dalam klausul SELECT kueri, tepat sebelum klausa FROM. Berikut adalah contoh yang mengekstrak data dari tabel Sales.SalesOrderHeader ke dalam tabel baru bernama Sales.Invoice..

SELECT SalesOrderID, CustomerID, OrderDate, PurchaseOrderNumber, TotalDue
INTO Sales.Invoice
FROM Sales.SalesOrderHeader;

SELECT INTO akan gagal jika sudah ada tabel dengan nama yang ditentukan setelah INTO. Setelah tabel dibuat, tabel dapat diperlakukan seperti tabel lainnya. Anda dapat memilihnya, menggabungkannya ke tabel lain, atau menyisipkan lebih banyak baris ke dalamnya.