Export File From SQL Query

Pernah gak dapet kerjaan dari IT Manager kamu untuk bikin export file dari MSSQL ke File Txt atau excel berdasarkan range waktu tertentu. Berikut dialognya..

“Ndro, aku minta data penjualan kasir per bulan mulai dari tahun 2002 sampai tahun 2008, dimasukkan ke file excel ya.. dipilah-pilah filenya..” kata Manager IT

“Waduh Bos.. itu pasti banyak banget ya.. aku khan kudu Query satu-satu per range tanggal, trus hasilnya di copy, lalu di masukkan ke file excel satu-satu ya.. ” sahut si Ndro

“Lha.. aku gak mau tau.. kamu khan di gaji buat itu.. Besok dah selesai khan..” kata si Manager..

“Siap Bos..” sahut si Ndro lagi dengan wajah pucat membayangkan ribetnya kerjaan itu..

Lha dari permintaan si Manager tadi filenya pasti jadi sekitar 7 x 12 bulan.. jadi… 84 File.. gile bener.. itu belum lagi kalo nanti ada revisi ketika tengah-tengah kerjain.. pasti ngulangi lagi.. bisa tambah tipis nih rambut..

Nah.. kalo ngerjain manual satu-satu pasti nggak mungkin khan.. nah ada cara pake FETCH untuk looping Querynya ama BCP untuk Export datanya ke file.. Caranya gini..

Pertama, tentukan dulu range tanggal yang diinginkan.. misal untuk januari 2004 jadi char “200401″ dst. Dari mana bisa diambil ? ya dari transaksi yang udah dibuat.. atau bikin saja satu table dengan nama stDate dengan satu field dengan nama tanggal varchar(6) isinya ada 84 record antara lain

200401
200402
200403
..
..
200812

Kedua, bikin perintah Query standart untuk ambil data transaksi dengan variable untuk filter data yang ada. Misal:

Declare @pSQL varchar(1000)
Set @pSQL = select faktur, tanggal, kode, namabarang, qty, 

             harga, discount, total from kasir 

             where convert(varchar(6), tanggal,112) = @ThnBln 

             Order by Tanggal

@ThnBln adalah variable varchar(6) yang nantinya digantikan oleh record yang ada di table stDate yang kita buat tadi.

Ketiga,
Tentukan direktory penyimpanan dan nama file yang ingin kita generate nantinya.. Buat perintah seperti ini

Declare @Perintah varchar(1000)
SET @Perintah = 'bcp "' + @pSQL + '" queryout c:\DataKasir\' + 

                @ThnBln + '_Kasir.xls  

                -c -S NAMASERVER -U USERNAME -P PASSWORD'

Bcp itu adalah utility bawaan dari SQL jadi nanti bisa lgs dipake..
Dari perintah di atas, nantinya hasil generate itu akan masuk di directory c:\DataKasir\

Yang Terakhir, gabungkan perintah-perintah tersebut menjadi begini

Declare @pSQL varchar(1000)
Declare @Perintah varchar(1000)

DECLARE Tgl_cursor CURSOR FOR
SELECT  Tanggal
FROM    stDate
Order by Tanggal 

OPEN Tgl_cursor

FETCH NEXT FROM tgl_cursor INTO @ThnBln
WHILE @@FETCH_STATUS = 0
BEGIN

Set @pSQL = select faktur, tanggal, kode, namabarang, qty, 

            harga, discount, total from kasir 

            where convert(varchar(6), tanggal,112) = @ThnBln 

            Order by Tanggal

SET @Perintah = 'bcp "' + @pSQL + '" queryout c:\DataKasir\' + 

                 @ThnBln + '_Kasir.xls  

                 -c -S NAMASERVER -U USERNAME -P PASSWORD'

EXEC master..xp_cmdshell @Perintah

FETCH NEXT FROM tgl_cursor INTO @ThnBln
END
CLOSE tgl_Cursor
DEALLOCATE tgl_cursor

Kronologi dari perintah ini adalah kita masukkan data tanggal yang sudah di-varchar-kan ke dalam cursor.. lalu data tersebut di looping dengan query tertentu lalu diproses lagi untuk di konversi menjadi file lain dengan nama yang unik sesuai dengan Tahun bulan yang sudah ditentukan…

Nah.. kalo dengan perintah itu.. kita bisa setting Query yang benar, trus dieksekusi deh.. kita tinggal duduk-duduk aja nungguin hasilnya.. Lama atau tidaknya ya tergantung besarnya data yang diambil..

Selamat Mencoba..