DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'C:\Backup\' SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name FROM MASTER.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0
BEGIN SET @fileName = @path + @name + '_' + @fileDate + '.BAK' BACKUP DATABASE @name TO DISK = @fileName FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor
-----------
DECLARE @TotalOscars INT
DECLARE @FilmOscars INT
SET @TotalOscars = 0
DECLARE FilmCursor CURSOR
FOR SELECT FilmOscarWins FROM tblFilm
FOR UPDATE OFFilmCumulativeOscars
OPEN FilmCursor
FETCH NEXT FROM FilmCursor INTO @FilmOscars
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TotalOscars += @FilmOscars
UPDATE tblFilm
SETFilmCumulativeOscars = @TotalOscars
WHERE CURRENT OFFilmCursor
FETCH NEXT FROMFilmCursor INTO@FilmOscars
END
CLOSE FilmCursor
DEALLOCATE FilmCursor
Langganan:
Posting Komentar (Atom)
Dynamic Query - SQL Server
Berikut adalah contoh dari bentuk dari Dynamic Query di SQL Server yang dibungkus ke dalam Store Procedure: USE [Data00] GO SET ANSI_NULLS ...
Popular Posts
-
CONTOH PENGGUNAAN LOOPING CASE UNTUK UPDATE TANGGAL BAYAR YANG NULL PADAHAL ANGSURAN DI ATASNYA ADA TGL PEMBAYARANNYA declare @norek varc...
-
UBAH FORMAT TANGGAL CONTOH DI DATABASE FORMATNYA YYYY/MM/DD, KITA UBAH JADI DD/MM/YYYY select a.nomor_register_aplikasi no_rekening, a.na...
-
SELECT THN, BLN_THN, KODE, COUNTER_MAX, COUNTER, PANJANG, CASE WHEN PANJANG=4 THEN TO_CHAR(COUNTER) WHEN PANJANG=3 T...
Tidak ada komentar:
Posting Komentar