Problem
Kadangkala
dimana anda mungkin ingin mengekspor data dari tabel ke dalam file teks (.txt).
Namun penamaan file dibuat berdasarkan tanggal pembuatan.
Solution
Untuk
lebih memahami solusi yang Agung Panduan tawarkan, mari kita bahas skenario berikut
ini: Ada proses query yang meminta data dari satu tabel atau lebih dengan
menggunakan perintah select. Lalu setiap data akan digabungkan kedalam satu
parameter dalam bentuk string. Setiap satu kali looping maka setiap baris akan
di tambahkan ke tabel temporary. Kemudian setelah proses looping selesai, data
pada tabel temporary akan di ekspor ke dalam file txt dan disimpan pada directory
yang sudah ditentukan.
Pertama-tama
Kita
Misalkan ada sebuah tabel seperti gambar dibawah ini yang akan di eksport ke
dalam file txt.
Kedua
Deklarasi
parameter yang menampung data pada tabel yang akan di ekspor ke file txt
DECLARE
@l_i_no int,
@l_v_nota
varchar(4),
@l_v_supplier
varchar(4),
@l_v_namaBarang
varchar(max),
@l_v_put_string
varchar(max)
Ketiga
Deklarasi
parameter lokasi direktori, file ekstensi, dan waktu pembuatan file txt
DECLARE @filelocation VARCHAR(100) ='D:\MyPost\TestSQL\';
DECLARE @fileTimeStamp varchar(200) = convert(varchar,getDate(), 112 )+'_'+ Replace(convert(varchar,getDate(), 114 ),':','') -- select
convert(varchar, getdate(), 121)
DECLARE @fileExtension varchar(5) = 'txt'
DECLARE @cmd VARCHAR(8000)
Keempat
Buat
tabel sementara dalam basis data TestDB (kolom harus memiliki tipe data yang
sama seperti pada kolom tabel asli - dalam hal ini Teks)
--Create
temporary table if it does not exist
IF OBJECT_ID('TestDB_temp') IS NULL
CREATE TABLE TestDB_temp(
[fileText] [TEXT] NULL
);
Kelima
Membaca
data pada tabel dan menginputkan ke dalam parameter yang akan menampung data
lalu menambahkan data setiap terjadi satu kali looping ke tabel sementara.
DECLARE cursor_product CURSOR
FOR SELECT [No], Nota, Supplier_Code, Nama_Barang FROM Supplier
WHERE 1=1
OPEN cursor_product;
FETCH NEXT FROM cursor_product INTO
@l_i_no,
@l_v_nota,
@l_v_supplier,
@l_v_namaBarang
WHILE @@FETCH_STATUS = 0
BEGIN
if
@l_v_namaBarang is
null or @l_v_namaBarang = null
begin
set
@l_v_namaBarang=' '
end
set
@l_v_put_string =
LEFT(REPLICATE(' ', 2) + @l_i_no, 2) +
LEFT(REPLICATE(' ', 2) + @l_v_nota, 4) +
LEFT(REPLICATE(' ', 2) + @l_v_supplier, 4) +
LEFT(REPLICATE(' ', 2) + @l_v_namaBarang, 10)
insert
into TestDB_temp (fileText) Values (@l_v_put_string)
FETCH
NEXT FROM cursor_product INTO
@l_i_no,
@l_v_nota,
@l_v_supplier,
@l_v_namaBarang
END
CLOSE cursor_product;
DEALLOCATE cursor_product;
Keenam
Membaca
data pada tabel sementara menggunakan bcp dan buat file txt. Namun untuk membaca tabel tersebut harus melakukan koneksi ulang ke server dimana database berada. Jadi harus mencantumkan user id dan password serta instance server.
set @cmd = 'bcp "select [fileText] from
DB_MVC_K.dbo.[TestDB_temp]" queryout "'+ @filelocation + @fileTimeStamp+'.'+@fileExtension +'" -c -t, -U <user> -P <password> -S' + @@SERVERNAME
exec master..xp_cmdshell @cmd
DROP TABLE TestDB_temp
Demikianlah
scenario pembuatan file txt yang mana datanya berasal dari tabel tertentu.
Untuk script lengkapnya dapat di copy dan paste di bawah ini,
DECLARE
@l_i_no int,
@l_v_nota varchar(4),
@l_v_supplier varchar(4),
@l_v_namaBarang varchar(max),
@l_v_put_string varchar(max)
DECLARE @filelocation VARCHAR(100) ='D:\MyPost\TestSQL\';
DECLARE @fileTimeStamp varchar(200) = convert(varchar,getDate(), 112 )+'_'+ Replace(convert(varchar,getDate(), 114 ),':','') -- select convert(varchar, getdate(), 121)
DECLARE @fileExtension varchar(5) = 'txt'
DECLARE @cmd VARCHAR(8000)
--Create temporary table if it does not exist
IF OBJECT_ID('TestDB_temp') IS NULL
CREATE TABLE TestDB_temp(
[fileText] [TEXT] NULL
);
DECLARE cursor_product CURSOR
FOR SELECT [No], Nota, Supplier_Code, Nama_Barang FROM Supplier
WHERE 1=1
OPEN cursor_product;
FETCH NEXT FROM cursor_product INTO
@l_i_no,
@l_v_nota,
@l_v_supplier,
@l_v_namaBarang
WHILE @@FETCH_STATUS = 0
BEGIN
if @l_v_namaBarang is null or @l_v_namaBarang = null
begin
set @l_v_namaBarang=' '
end
set @l_v_put_string = LEFT(REPLICATE(' ', 2) + @l_i_no, 2) +
LEFT(REPLICATE(' ', 2) + @l_v_nota, 4) +
LEFT(REPLICATE(' ', 2) + @l_v_supplier, 4) +
LEFT(REPLICATE(' ', 2) + @l_v_namaBarang, 10)
insert into TestDB_temp (fileText) Values (@l_v_put_string)
FETCH NEXT FROM cursor_product INTO
@l_i_no,
@l_v_nota,
@l_v_supplier,
@l_v_namaBarang
END
CLOSE cursor_product;
DEALLOCATE cursor_product;
set @cmd = 'bcp "select [fileText] from DB_MVC_K.dbo.[TestDB_temp]" queryout "'+
@filelocation + @fileTimeStamp+'.'+@fileExtension +
'" -c -t, -U <user> -P <password> -S' + @@SERVERNAME
exec master..xp_cmdshell @cmd
DROP TABLE TestDB_temp
Share This :
0 Comments