suherman

Inserting Data into 2 Tables with 1 Statement without Trigger

Biasanya kita menggunakan trigger untuk dapat melakukan memasukkan data ke dalam 2 tabel yang berbeda sekaligus. 1 tabel target yang akan dimasukkan data yang kemudian dibuatkan trigger yang kemudian trigger tersebut akan melakukan proses insert ke tabel ke 2.

Namun dalam perkembangannya, Insert Statement saat ini di memiliki sub-klausa OUTPUT. dengan memanfaatkan sub-klausa OUTPUT tersebut kita dapat membuat 1 buah statement insert yang dapat melakukan insert data ke 2 buah tabel sekaligus.

Mari kita coba praktekkan bersama-sama :

contoh kasusnya adalah sebagai berikut, di departemen TI memiliki policy bahwa setiap penambahan user baru pada sebuah aplikasi harus melakukan penggantian password dalam kurun waktu 7 hari. dalam 7 hari tersebut, user akan selalu diingatkan oleh sistem setiap login untuk pengganti passwordnya sampai user tersebut mengganti password.

Pertama kita akan membuat 2 buah tabel yaitu tabel DftrUser dan tabel TblPengingat

USE AdventureWorks;

GO

CREATE SCHEMA [Pengguna] AUTHORIZATION dbo;

GO

CREATE TABLE [Pengguna].[DftrUser]

    (

       [PenggunaID] [int] IDENTITY(1,1) NOT NULL,

       [Nama] VARCHAR(30) NOT NULL,

       [Password] VARCHAR(30) NOT NULL,

        CONSTRAINT [PK_PenggunaID] PRIMARY KEY CLUSTERED 

            (

               [PenggunaID] ASC

            )ON [PRIMARY]

    ) ON [PRIMARY];

GO

CREATE TABLE [Pengguna].[TblPengingat]

    (

        [PengingatID] [int] IDENTITY(1,1) NOT NULL,

       [PenggunaID] [int] NOT NULL,

       [Tgl] DATETIME NOT NULL,

       [SttPengguna] CHAR(1) NOT NULL,

        CONSTRAINT [PK_PengingatID] PRIMARY KEY CLUSTERED 

            (

               [PengingatID] ASC

            )ON [PRIMARY]

    ) ON [PRIMARY]; 

GO

Sekarang kita coba untuk memasukkan data-data yang diperlukan untuk proses diatas dengan menggunakan 1 buah statement tanpa menggunakan trigger

INSERT INTO [Pengguna].[DftrUser] ( Nama, password )

OUTPUT INSERTED.PenggunaID, DATEADD(d,7,GETDATE()),'0'

INTO[Pengguna].[TblPengingat]

    (

        PenggunaID,

        Tgl,

        SttPengguna

    )

VALUES  ( 'Suherman','K4taSand1');

dan berikut hasilnya :

select * from [Pengguna].[DftrUser]

select * from [Pengguna].[TblPengingat]

tampak bahwa data telah masuk ke kedua tabel tersebut dengan hanya menggunakan 1 statement DML saja.

Demikian semoga bermanfaat.

Backup and Restore Resource Database

Sebelumnya saya pernah menulis apa itu resource database disini, dan berikut adalah lanjutannya yaitu bagaimana untuk melakukan Backup dan Restore Resource Database.

Untuk melakukan aktivitas Backup dan Resore Resource Database tidak dapat menggunakan dengan metode yang sama dengan system databases yang lain, karena Resource Database tidak dapat terlihat di SQL Server Management Studio.

Kegiatan Restore dan Backup Resource Database dapat dilakukan dengan menggunakan Copy dan paste file.

Backup

untuk dapat melakukan backup, kita akan menggunakan fitur xp_cmdshell, namun sebelum dapat menggunakan fitur ini, harus diaktifkan terlebih dahulu dengan cara sebagai berikut :

USE master

GO

-- To allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1

GO

-- To update the currently configured value for advanced options.

RECONFIGURE

GO

-- To enable the feature.

EXEC sp_configure 'xp_cmdshell', 1

GO

-- To update the currently configured value for this feature.

RECONFIGURE

GO

use master

GO

/* Copy Resource Database Files Using XP_CMDSHELL */ 

EXEC xp_cmdshell 'COPY "C:\Program Files\Microsoft SQL Server\

    MSSQL10.MSSQLSERVER\MSSQL\Binn\mssqlsystemresource.mdf" 

    "E:\SystemDatabaseBackups\SS2008"', NO_OUTPUT

GO

EXEC xp_cmdshell 'COPY "C:\Program Files\Microsoft SQL Server\

    MSSQL10.MSSQLSERVER\MSSQL\Binn\mssqlsystemresource.ldf" 

    "E:\SystemDatabaseBackups\SS2008"',NO_OUTPUT

GO

Note : karena String setelah xp_cmdshell terlalu panjang, pada script diatas saya tulis menjadi 3 line untuk setiap string. namun pada prakteknya harus ditulis dalam 1 line.

Untuk SQL Server 2008 R2 :

use master

GO

/* Copy Resource Database Files Using XP_CMDSHELL */ 

EXEC xp_cmdshell 'COPY "C:\Program Files\Microsoft SQL Server\

    MSSQL10_50.SS2008R2\MSSQL\Binn\mssqlsystemresource.mdf" 

    "E:\SystemDatabaseBackups\SS2008R2"', NO_OUTPUT

GO

EXEC xp_cmdshell 'COPY "C:\Program Files\Microsoft SQL Server\

    MSSQL10_50.SS2008R2\MSSQL\Binn\mssqlsystemresource.ldf" 

    "E:\SystemDatabaseBackups\SS2008R2"',NO_OUTPUT

GO

Note : karena String setelah xp_cmdshell terlalu panjang, pada script diatas saya tulis menjadi 3 line untuk setiap string. namun pada prakteknya harus ditulis dalam 1 line.

Restore

untuk proses restorenya sangat mudah, setelah database master telah di rebuild ulang dari backup database master sebelumnya kemudian pastikan sql server servicesnya dimatikan, kemudian copy kan file mssqlsystemresource.mdf and mssqlsystemresource.ldf ke folder yang semestinya. kemudian aktifkan kembali SQL Server Services nya.

Resource Database

Jika anda menggunakan SQL Server 2005 atau SQL Server 2008 sebaiknya lebih mengenal lagi dengan 1 buah System Database yaitu Resource Database

Seperti yang kita ketahui bahwa System database terdiri dari :

1. Master database, database yang merekam semua informasi pada level sistem di SQL Server seperti logon account, endpoint, linked-server, system configuration setting,dst.
2. Msdb database, sebagai penyimpanan informasi jobs, operator, alerts, backup, restore, informasi database mail, dst.
3. Model database, digunakan sebagai model pada saat pembuatan database baru.
4. temdb database, database ini akan terjadi setiap kali layanan SQL Server berjalan.
5. Reportserver database
6. ReportServerTempDB database
7. Distribution database

Namun pada edisi SQL Server 2005 keatas adalagi 1 buah database system yaitu Resource Database.

Resource Database adalah database yang bersifat read-only yang berisi semua objek sistem yang disertakan dengan SQL Server 2005 keatas. objek sistem SQL Server, seperti sys.objects, secara fisik bertahan dalam Resource database, tetapi system object secara logis muncul dalam skema sys disetiap database. Database Resource tidak berisi user data atau user metadata.

fungsi dari resource database adalah untuk memudahkan proses upgrade ke versi baru dari SQL Server tanpa melalui proses yang rumit. Pada versi sebelumnya setiap kali versi dari SQL Server ditingkatkan semua versi system objects sebelumnya harus di drop dan objek versi sistem yang baru akan dibuat.

physical file names dari Resource database adalah mssqlsystemresource.mdf and mssqlsystemresource.ldf. Di SQL Server 2005 dapat ditemukan di direktori <drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ sedangkan di SQL Server 2008 dapat ditemukan di direktori <drive>: \Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\MSSQL\Binn.

Dengan adanya resource database, ketika SQL Server akan ditingkatkan ke versi yang lebih baru maka resource database juga akan ikut terbaharui. Jika versi baru dari SQL Server tersebut menimbulkan masalah proses rolling back dapat dilakukan dengan sangat mudah, hanya dengan cara mengganti resourcedatabase dengan versi sebelumnya.

So, bagi mereka yang menggunakan SQL Server 2005 keatas, sebaiknya mulai memperhatikan keberadaan dari Resource database ini dan menjadikannya sebagai salah satu database yang harus diperhatikan secara serius dalam DRP (Disaster Recovery Plan).