張小芸的生活記事簿 δ

情境: 需要撈資料, 填入另一張表格時,..

例如, 依會員條件讀取E-mail, 並填入待發電子報的列表中,

Create PROCEDURE 預存程序名稱
@serno int

AS
BEGIN

--宣告要使用的資料集
DECLARE
@customer_id as nvarchar(50),
@firstname as nVarchar(30),
@lastname as nVarchar(30),
@email as nVarchar(50)

--宣告CURSOR
DECLARE icur CURSOR FOR

--Select SQL
SELECT customer_id, firstname, lastname, email FROM customer

--開啟CURSOR
OPEN icur

--將SELECT的值丟入
FETCH NEXT FROM icur INTO @customer_id, @firstname, @lastname, @email

--讀取資料,並執行所需SQL
WHILE @@FETCH_STATUS = 0
BEGIN
　INSERT INTO mail_list(serno, mail) VALUES (@serno, @email)
FETCH NEXT FROM icur INTO @customer_id, @firstname, @lastname, @email
END

--關閉CURSOR
CLOSE icur
DEALLOCATE icur


END

以上,並感謝阿姆叔大力指導..

========== QQ分隔線 ==========

刪除SQL的LOG指令:

dump transaction DbName with no_log
backup log DbName with no_log
DBCC SHRINKFILE(DbName_Log,1)

以上短短三行~ Log 順間滅少~ 嘿!!