Marhaban Yaa Ramadhan…

Bulan penuh berkah telah tiba. Pada hari pertama di bulan penuh berkah ini, saya akan menjelaskan table variable dan table temporary. Apa itu table variable dan table temporary?

Mungkin teman-teman sudah tidak asing dengan kata-kata table. Yuph, table merupakan tempat untuk penyimpanan data berupa baris dan kolom. Lalu apa yang dimaksud table variable dan table temporary? Apa perbedaan table biasa dalam database dengan table variable dan table temporary? Mungkin untuk membedakan keduanya akan saya urai satu persatu.

1. Table Variable

Table variable merupakan table yang dideklarasikan seperti halnya variable. Penggunaannya biasanya dipakai untuk menyimpan data sementara yang membutuhkan tampungan data seperti sebuah table. Table variable hanya dapat digunakan dalam satu koneksi data (biasanya dalam satu stored procedure) dan satu kali execute. Untuk mendeklarasikan table variable (karena seperti variable, table variable dideklarasikan, bukan di-create), digunakan kata kunci DECLARE dan diberi @ (a keong) sebagai nama variable serta TABLE sebagai tipe datanya. Contoh penggunaan table variable :

DECLARE @produk TABLE (
kode_produk VARCHAR(10) NOT NULL,
kode_gudang VARCHAR(5) NULL,
nama_produk VARCHAR(50) NULL
)
INSERT INTO @produk
SELECT *
FROM [produksi].[dbo].[produk]
SELECT *
FROM @produk

Jika kita execute query di atas, maka akan menghasilkan data seperti berikut :

2. Table Temporary

Table temporary sebenarnya hampir sama seperti table variable, namun table temporary dapat digunakan untuk beberapa kali execute. Proses create table temporary dapat dilakukan dengan query SELECT… INTO… selain dengan query CREATE seperti pembuatan table umumnya. Table temporary sendiri dibagi menjadi dua, global dan local table temporary.

• Local Table Temporary

Local table temporary ditandai dengan kres satu (#). Local table temporary hanya dapat digunakan hanya dalam satu session. Jika kita meng-create local table temporary dalam suatu session, kemudian kita lakukan proses insert, update, maupun delete pada session yang lain maka akan menyebabkan error. Contoh penggunaan Local Table Temporary :

CREATE TABLE #produk (
kode_produk VARCHAR(10) NOT NULL,
kode_gudang VARCHAR(5) NULL,
nama_produk VARCHAR(50) NULL
)
INSERT INTO #produk
SELECT *
FROM [produksi].[dbo].[produk]
SELECT *
FROM #produk

Jika kita execute query di atas, maka akan menghasilkan data seperti berikut :

• Global Table Temporary

Global table temporary ditandai dengan dua kres (##). Global table temporary dapat digunakan dalam semua session. Jika kita meng-create global table temporary dalam suatu session, maka kita dapat melakukan proses insert, update, maupun delete pada session yang lain. Contoh penggunaan Global Table Temporary :

CREATE TABLE ##produk (
kode_produk VARCHAR(10) NOT NULL,
kode_gudang VARCHAR(5) NULL,
nama_produk VARCHAR(50) NULL
)
INSERT INTO ##produk
SELECT *
FROM [produksi].[dbo].[produk]
SELECT *
FROM ##produk

Jika kita execute query di atas, maka akan menghasilkan data seperti berikut :

Untuk perlu diingat, baik table variable maupun table temporary, pada saat deklarasi maupun create, keduanya akan ter-create secara fisik pada database tempdb pada System Database. Namun untuk table variable, tabel fisik akan otomatis hilang/dihapus (biasanya saat proses eksekusi telah selesai dilakukan). Namun, untuk table temporary, table fisik hanya akan terhapus bila kita melakukan DROP TABLE terhadap table tersebut. Bila kita menjalankan query berikut :

SELECT COUNT(<em>)
FROM tempdb.dbo.sysobjects
GO
DECLARE @produk TABLE (
kode_produk VARCHAR(10) NOT NULL,
kode_gudang VARCHAR(5) NULL,
nama_produk VARCHAR(50) NULL
)
SELECT COUNT(</em>)
FROM tempdb.dbo.sysobjects
GO

atau :

SELECT COUNT(<em>)
FROM tempdb.dbo.sysobjects
GO
CREATE TABLE #produk (
kode_produk VARCHAR(10) NOT NULL,
kode_gudang VARCHAR(5) NULL,
nama_produk VARCHAR(50) NULL
)
SELECT COUNT(</em>)
FROM tempdb.dbo.sysobjects
GO
DROP TABLE #produk

atau :

SELECT COUNT(<em>)
FROM tempdb.dbo.sysobjects
GO
CREATE TABLE ##produk (
kode_produk VARCHAR(10) NOT NULL,
kode_gudang VARCHAR(5) NULL,
nama_produk VARCHAR(50) NULL
)
SELECT COUNT(</em>)
FROM tempdb.dbo.sysobjects
GO
DROP TABLE ##produk

Saat kita eksekusi query tersebut, kita akan melihat bahwa object pada database tempdb akan bertambah

Dari penjelasan di atas, ada beberapa hal penting mengenai table variable dan table temporary yang perlu diperhatikan :

1. Table variables tidak dapat digunakan dalam proses TRANSACTION (dengan query BEGIN TRAN/BEGIN TRANSACTION) sehingga tidak dapat dilakukan proses ROLLBACK.

1. Perlu diperhatikan untuk melakukan DROP TABLE terhadap table temporary dikarenakan table tersebut akan tetap tersimpan dalam database master dan tentunya akan memperbesar kapasitas database.

Demikian penjelasan singkat mengenai table variable dan table temporary dari saya. Bila ada yang ingin ditambahkan bisa diisi pada kolom komentar. Akhir kata, saya mengucapkan Selamat berpuasa. Semoga segala puasa dan amal ibadah kita diterima ALLAH SWT dan segala sesuatunya menjadi keberkahan untuk kita. Amin… 😀

Pagi hari yang cerah ini, saat yang tepat untuk berbagi,hehe….

Kali ini saya ingin menjelaskan tentang 3 pola query insert (untuk SQL Server) yang sering digunakan untuk menginputkan data. Mungkin teman-teman sudah tidak asing menggunakan pola INSERT INTO. Dalam penggunaannya ternyata memiliki pola yang lain untuk keperluan yang berbeda. Baik, saya langsung jelaskan satu persatu, namun sebelumnya saya asumsikan teman-teman telah memiliki tabel barang dan produk yang ada pada artikel Menyimpan hasil insert yang disimpan ke suatu tabel dari suatu tabel ke dalam tabel lain.

1. INSERT INTO … VALUES …

Untuk query ini, tentunya teman-teman sudah biasa menggunakannya. Query ini digunakan untuk menginput data ke database baik satu atau lebih dari suatu inputan. Misalnya, kita ingin menambahkan data ke tabel barang dengan kode_barang=C021,kode_gudang=G01B,nama_barang=Mi Instan. Maka kita dapat membuat query seperti berikut :

INSERT INTO [produksi].[dbo].[barang] (
[kode_barang],
[kode_gudang],
[nama_barang]
)
VALUES (
'C021',
'G01B',
'Mi Instan'
)

Atau kita ingin menambahkan dua buah data ke tabel barang dengan kode_barang=C022,kode_gudang=G01B,nama_barang=Kopi dan kode_barang=C023,kode_gudang=G01B,nama_barang=Kopi Luwak. Maka kita dapat membuat query seperti berikut :

INSERT INTO [produksi].[dbo].[barang] (
[kode_barang],
[kode_gudang],
[nama_barang]
)
VALUES (
'C022',
'G01B',
'Kopi'
),
(
'C023',
'G01B',
'Kopi Luwak'
)

2. INSERT INTO …. SELECT

Untuk query yang satu ini biasanya digunakan untuk menyimpan data ke tabel dari tabel yang lain (baik itu tabel lokal, table variable, maupun table temporary). Misalnya, kita ingin memasukkan semua data yang memiliki kode gudang G01B pada tabel barang ke dalam tabel produk. Maka kita dapat membuat query seperti berikut :

INSERT INTO [produksi].[dbo].[produk]
SELECT *
FROM [produksi].[dbo].[barang]
WHERE [kode_gudang] = 'G01B'

Walaupun query ini sebenarnya dapat digunakan untuk memasukkan data dari suatu inputan (namun hanya terbatas satu inputan saja). Misalnya kita ingin menambahkan data ke tabel barang dengan kode_barang=C024,kode_gudang=G01B,nama_barang=Teh Botol. Maka kita dapat membuat query seperti berikut :

INSERT INTO [produksi].[dbo].[barang]
SELECT 'C024',
'G01B',
'Teh Botol'

3. SELECT … INTO ….

Untuk query yang satu ini dapat kita gunakan untuk menginput data dari suatu tabel ke tabel yang lain, namun tabel tujuan belum ada/belum di-create. Biasanya query ini digunakan pada saat migrasi data maupun saat kita memerlukan sebuah tabel untuk tempat menampung data sementara dari suatu tabel. Tabel yang ter-create dari query ini akan memiliki properties yang sama persis dengan tabel sumber, baik dari jumlah kolom, tipe data kolom-kolomnya, dan lain-lainnya. Namun, tabel tujuan yang dapat di-create hanya tabel lokal dan table temporary saja (table variable tidak bisa menggunakan query ini). Misalnya, kita ingin memasukkan tabel barang ke tabel lain sebagai tempat menampung data sementara dengan spesifikasi tabel yang sama dengan tabel barang. Maka kita dapat membuat query seperti berikut :

• Tabel tujuan adalah tabel fisik :

SELECT *
INTO [produksi].[dbo].[temp_barang]
FROM [produksi].[dbo].[barang]

• Tabel tujuan adalah tabel temporary global :

SELECT *
INTO ##temp_barang
FROM [produksi].[dbo].[barang]

• Tabel tujuan adalah tabel temporary local :

SELECT *
INTO #temp_barang
FROM [produksi].[dbo].[barang]

Demikian penjelasan singkat mengenai 3 pola query untuk Insert data ini. Mudah-mudahan bermanfaat, selamat berkerja… 😀

Pada suatu hari, Bayu mendapat tugas untuk memasukkan data dari sebuah excel ke dalam database untuk keperluan aplikasi. Sementara dalam aplikasi tersebut tidak terdapat fasilitas untuk mengupload data Excel ke dalamnya. Lalu, bagaimana cara Bayu melakukannya? Apa Bayu harus menulis query insert untuk setiap datanya?

Pada kasus seperti di atas mungkin banyak teman-teman juga yang mengalaminya. Mungkin ada yang berpikiran menulis query untuk semua data. Untuk jumlah data yang sedikit mungkin bukan hal yang jadi masalah. Namun, bagaimana bila data tersebut terdiri dari puluhan atau ratusan data? Akan membutuhkan waktu yang sangat lama dan ini menjadi tidak efisien.

Pada kali ini, saya akan membagikan cara yang mungkin efektif dengan menggunakan Excel. Saya tidak menyebutkan detail Excel tersebut, teman-teman bisa menggunakan Microsoft Excel maupun software spreadsheet lainnya. Tapi untuk artikel ini, saya menggunakan Microsoft Excel 2013 (karena di laptop kantor terinstal Ms. Excel 2013,hehe..) dan database SQL Server 2008 R sebagai latihan. Untuk file Excel, teman-teman bisa download file Excelnya di : ProvinsiKabupaten.xls. Untuk tabel databasenya, teman-teman bisa create menggunakan query berikut :

USE [Indonesia]

CREATE TABLE [dbo].[tblProvinsi] (
[Id_Provinsi] [int] NOT NULL,
[Nama_Provinsi] <a href="250">varchar</a> NULL,
CONSTRAINT [PK_tblProvinsi] PRIMARY KEY CLUSTERED ([Id_Provinsi] ASC) WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY],
CONSTRAINT [IX_tblProvinsi] UNIQUE NONCLUSTERED ([Id_Provinsi] ASC) WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[tblKabupaten] (
[Id_Kabupaten] [int] NOT NULL,
[Id_Provinsi] [int] NOT NULL,
[Nama_Kabupaten] <a href="250">varchar</a> NULL,
CONSTRAINT [PK_tblKabupaten] PRIMARY KEY CLUSTERED (
[Id_Kabupaten] ASC,
[Id_Provinsi] ASC
) WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
) ON [PRIMARY]

Kita akan mencoba memasukkan data provinsi ke tabel provinsi. Berikut langkah-langkahnya :

• Kita buka file Excel tersebut dan kita blok kolom D2 – D32.

• Ketikkan pada kolom Formula dengan formula berikut

=”INSERT INTO [Indonesia].[dbo].[tblProvinsi] ([Id_Provinsi], [Nama_Provinsi]) VALUES (‘” & A2 & “‘, ‘” & B2 & “‘)”

seperti berikut :

Atau bisa memanfaatkan fungsi CONCATENATE pada excel dengan rumus

=CONCATENATE(“INSERT INTO [Indonesia].[dbo].[tblProvinsi] ([Id_Provinsi], [Nama_Provinsi]) VALUES (‘”,A2,”‘, ‘”,B2,”‘)”)

seperti berikut :

• Kemudian tekan Ctrl + Enter. Maka script pun siap kita eksekusi di SQL Server.

• Kita Copy kolom tersebut.

• Kita buka Sql Server. Paste-kan query yang tadi kita copy. Di sini saya tambahkan Begin Transaction agar proses Rollback saat tengah jalan ada yang salah dengan querynya.

• Execute script tersebut. Maka data telah masuk dalam tabel Provinsi.

Kita dapat melakukan hal serupa dengan kabupaten. Teman-teman bisa mencobanya. Untuk kasus yang lain hanya menyesuaikan query yang ingin dibuatnya. Generate ini tidak terbatas hanya untuk query Insert saja. Update, Delete, maupun query lainnya bisa diterapkan dengan cara ini tergantung dari kebutuhan yang diinginkan dan kasus yang terjadi. Bagaimana?? Mudah kan?? Selamat bekerja 😀

Pada siang hari yang cerah ini saya akan membagikan ilmu baru (karena saya pun baru mengetahuinya sambil menunggu pekerjaan baru). Apa itu?? Mungkin teman-teman pernah mengalami hal yang sama dengan kasus yang pernah saya alami. Suatu ketika teman-teman diharuskan memasukkan data ke suatu tabel (input data) dengan jumlah data yang sangat besar, namun saat proses insert data tersebut gagal di tengah proses (entah oleh data yang tidak valid ataupun koneksi yang terputus) dan tidak diperbolehkan proses Rollback data tetapi user ingin mengetahui data apa saya yang telah berhasil ter-insert dalam tabel tersebut. Mungkin solusi pertama kita bisa buat sebuah flag dalam tabel tersebut dimana flag tersebut diupdate saat semua proses insert berhasil dilakukan sampai selesai. Tetapi bila ternyata tabel tersebut tidak ada kolom untuk tempat flag dan kita tidak diperkenankan menambah kolom dari tabel tersebut, maka solusi berikut mungkin akan sangat berguna untuk menyelesaikan kasus tersebut. Lalu, apa solusinya?? Apa querynya??

Kita bisa menggunakan pola script berikut :

[sourcecode language=”sql”]
INSERT [tabel_tujuan]
OUTPUT INSERTED.*
INTO [tabel_monitor]
SELECT *
FROM [tabel_sumber]
[/sourcecode]

dimana :

tabel_tujuan = tabel yang akan di-insert

tabel_monitor = tabel tempat untuk menyimpan data yang berhasil diinsert selama proses (bisa berupa table temporary maupun table variable. Penjelasan keduanya insya ALLAH akan saya jelaskan di lain kesempatan)

tabel_sumber = tabel yang menjadi sumber data untuk diinsert

Sebagai latihan, kita bisa menggunakan tabel-tabel pada artikel berikut Join pada SQL Server. Di sini kita akan menggunakan tabel barang sebagai latihan kita. Lalu tambahkan tabel produk menggunakan script berikut :

[sourcecode language=”sql”]
USE [produksi]

CREATE TABLE [dbo].[produk] (
[kode_produk] varchar NOT NULL,
[kode_gudang] varchar NULL,
[nama_produk] varchar NULL,
CONSTRAINT [PK_produk] PRIMARY KEY CLUSTERED ([kode_produk] ASC) WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY],
CONSTRAINT [IX_produk] UNIQUE NONCLUSTERED ([kode_produk] ASC) WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
) ON [PRIMARY]
[/sourcecode]

Kemudian kita coba untuk melihat data hasil insert dengan query berikut :

[sourcecode language=”sql”]
DECLARE @produk_inserted TABLE (
[kode_produk] varchar NOT NULL,
[kode_gudang] varchar NULL,
[nama_produk] varchar NULL
)

INSERT produk
OUTPUT INSERTED.*
INTO @produk_inserted
SELECT *
FROM barang
[/sourcecode]

Di sini saya menggunakan table variable sebagai penampung data hasil insert. Saat terjadi masalah saat proses di atas berlangsung, kita dapat melihat data apa saja yang berhasil di-insert dengan select pada table variable.

[sourcecode language=”sql”]
SELECT *
FROM @produk_inserted
[/sourcecode]

Demikian artikel singkat ini, mudah2an bermanfaat… 😀