Buat File Txt Data Tabel SQLServer

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

Database