Ratakan

Informations :

SP Backup Multiple Table

/*
 query backup multiple table
*/


DECLARE @DT VARCHAR(10)
SET @DT = 'back' --Ganti dengan kode table sementara

DECLARE @NAME VARCHAR(30), @QUERY VARCHAR(MAX)
CREATE TABLE #TEMP(
 NAME VARCHAR(30),
 RECORD BIGINT,
 RECORD_TEMP BIGINT,
 ERR BIT,
 ERR_MSG VARCHAR(MAX)
)
--Masukkan nama table yang akan di-maintain di dalam tanda petik serta tambahkan INSERT INTO #TEMP(NAME) VALUES('') jika ada lebih banyak lagi 
INSERT INTO #TEMP(NAME) VALUES('AISM0001')
INSERT INTO #TEMP(NAME) VALUES('AISM0002')
INSERT INTO #TEMP(NAME) VALUES('AISM0003')
INSERT INTO #TEMP(NAME) VALUES('AISM0004')




--End masukkan nama table yang akan di-maintain

DECLARE RS CURSOR FOR
 SELECT NAME FROM #TEMP
OPEN RS
FETCH NEXT FROM RS INTO @NAME

WHILE @@FETCH_STATUS = 0
BEGIN
 SET @QUERY = ''
 
 IF (SELECT COUNT(*) FROM sysobjects WHERE NAME=@NAME AND TYPE='U') > 0
 BEGIN
  SET @QUERY = 'UPDATE #TEMP SET RECORD=(SELECT COUNT(*) FROM ' + @NAME + ') WHERE NAME=' + CHAR(39) + @NAME + CHAR(39)
  EXEC (@QUERY)
  
  SET @QUERY = ''
  
  IF (SELECT COUNT(*) FROM sysobjects WHERE NAME=(@NAME + '_' + @DT) AND TYPE='U') = 0
  BEGIN
   SET @QUERY = 'SELECT * INTO ' + @NAME + '_' + @DT + ' FROM ' + @NAME
   EXEC (@QUERY)
   
   IF (SELECT COUNT(*) FROM sysobjects WHERE NAME=(@NAME + '_' + @DT) AND TYPE='U') > 0
   BEGIN
    UPDATE #TEMP SET ERR=0, ERR_MSG='Backup done.' WHERE NAME=@NAME
   END
   
   SET @QUERY = 'UPDATE #TEMP SET RECORD_TEMP=(SELECT COUNT(*) FROM ' + @NAME + '_' + @DT + ') WHERE NAME=' + CHAR(39) + @NAME + CHAR(39)
   EXEC (@QUERY)
  END
  ELSE
  BEGIN
   UPDATE #TEMP SET ERR=1, ERR_MSG='The temporary table already exist.' WHERE NAME=@NAME
  END
 END
 ELSE
 BEGIN
  UPDATE #TEMP SET ERR=1, ERR_MSG='This table does not exist.' WHERE NAME=@NAME
 END
 
 FETCH NEXT FROM RS INTO @NAME
END
GO

CLOSE RS
GO

DEALLOCATE RS
GO

SELECT * FROM #TEMP
DROP TABLE #TEMP





Hasilnya


No comments