A cursor with the name 'DEL_CUR' already exists.

DECLARE DEL_CUR CURSOR FOR 改為
DECLARE DEL_CUR CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR

ID重新計算

DBCC CHECKIDENT ('table name', RESEED, 0)

after trigger and before trigger + cursor

ALTER TRIGGER [dbo].[insertTrigger]
   ON  [dbo].[PBB]
   INSTEAD of insert
AS
BEGIN

declare @id as int
declare @yeartext as int
declare @monthtext as int
declare @timedate as int
declare @timedate_text as varchar(50)
declare @itemtext as varchar(50)
declare @formulatext as varchar(50)
declare @region as varchar(50)
declare @country as varchar(50)
declare @asp as varchar(50)
declare @category as varchar(50)
declare @model as varchar(50)
declare @sam as varchar(50)
declare @ntd_amount as float
declare @inumber as int
declare @timedate_start as varchar(50)
declare @timedate_end as varchar(50)
DECLARE @MyCursor CURSOR

SET @MyCursor = CURSOR FAST_FORWARD
FOR
select id,yeartext,monthtext,timedate,timedate_text,itemtext,formulatext,region,country,asp,category,model,sam,ntd_amount,inumber,timedate_start,timedate_end from inserted

OPEN @MyCursor
FETCH NEXT FROM @MyCursor into @id,@yeartext,@monthtext,@timedate,@timedate_text,@itemtext,@formulatext,@region,@country,@asp,@category,@model,@sam,@ntd_amount,@inumber,@timedate_start,@timedate_end
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO [dbo].[PBB] ([YearText],[MonthText],[TimeDate],[TimeDate_Text],[ItemText],[FormulaText],[Region],[Country],[ASP],[Category],[Model],[SAM],[iNumber],[TimeDate_Start],[TimeDate_End])    
SELECT week_year,week_month,cast((cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)) as int)
,(cast(week_year as varchar(10))+'M'+right(('0'+cast(week_month as varchar(10))),2))
,'A','Purchase amt',@region,@country,@asp,@category,@model,@sam,@inumber,@timedate_start,@timedate_end
FROM  htccsddc.[CSD_DB].[dbo].[Calender]
where (cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)>=@TimeDate_Start)
and (cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)<=@TimeDate_End)
group by week_year,week_month
order by week_year,week_month

INSERT INTO [dbo].[PBB] ([YearText],[MonthText],[TimeDate],[TimeDate_Text],[ItemText],[FormulaText],[Region],[Country],[ASP],[Category],[Model],[SAM],[iNumber],[TimeDate_Start],[TimeDate_End])    
SELECT week_year,week_month,cast((cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)) as int)
,(cast(week_year as varchar(10))+'M'+right(('0'+cast(week_month as varchar(10))),2))
,'B','buy back amt',@region,@country,@asp,@category,@model,@sam,@inumber,@timedate_start,@timedate_end
FROM  htccsddc.[CSD_DB].[dbo].[Calender]
where (cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)>=@TimeDate_Start)
and (cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)<=@TimeDate_End)
group by week_year,week_month
order by week_year,week_month

INSERT INTO [dbo].[PBB] ([YearText],[MonthText],[TimeDate],[TimeDate_Text],[ItemText],[FormulaText],[Region],[Country],[ASP],[Category],[Model],[SAM],[iNumber],[TimeDate_Start],[TimeDate_End])    
SELECT week_year,week_month,cast((cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)) as int)
,(cast(week_year as varchar(10))+'M'+right(('0'+cast(week_month as varchar(10))),2))
,'C','buy back %',@region,@country,@asp,@category,@model,@sam,@inumber,@timedate_start,@timedate_end
FROM  htccsddc.[CSD_DB].[dbo].[Calender]
where (cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)>=@TimeDate_Start)
and (cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)<=@TimeDate_End)
group by week_year,week_month
order by week_year,week_month

INSERT INTO [dbo].[PBB] ([YearText],[MonthText],[TimeDate],[TimeDate_Text],[ItemText],[FormulaText],[Region],[Country],[ASP],[Category],[Model],[SAM],[iNumber],[TimeDate_Start],[TimeDate_End])    
SELECT week_year,week_month,cast((cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)) as int)
,(cast(week_year as varchar(10))+'M'+right(('0'+cast(week_month as varchar(10))),2))
,'D','Avg. Purchase amt of Past Selected Period',@region,@country,@asp,@category,@model,@sam,@inumber,@timedate_start,@timedate_end
FROM  htccsddc.[CSD_DB].[dbo].[Calender]
where (cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)>=@TimeDate_Start)
and (cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)<=@TimeDate_End)
group by week_year,week_month
order by week_year,week_month
--end
FETCH NEXT FROM @MyCursor
into @id,@yeartext,@monthtext,@timedate,@timedate_text,@itemtext,@formulatext,@region,@country,@asp,@category,@model,@sam,@ntd_amount,@inumber,@timedate_start,@timedate_end
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
END

declare @sql as nvarchar(max) execute sp_executesql @sql

declare @sql as nvarchar(max)
set @sql =
N'INSERT INTO [dbo].[PBB] ('+@Groupby+',[iNumber],[TimeDate_Start],[TimeDate_End])
SELECT distinct '+@Groupby+','+quotename(@MonthCalType,'''')+' as iNumber,'+quotename(@TimeDate_Start,'''')+' as TimeDate_Start,'+quotename(@TimeDate_End,'''')+' as TimeDate_End
 FROM #temp1 where timedate>='+quotename(@TimeDate_Start,'''')+' and timedate<='+quotename(@timedate_end,'''')
execute sp_executesql @sql

使用 EXCEPT 與 INTERSECT 來比對兩個資料表中的資料

--使用 EXCEPT 來比對兩個資料表的資料
SELECT * FROM DBO.名稱清單
EXCEPT
SELECT * FROM DBO.名稱清單_BAK

--使用 INTERSECT 來比對兩個資料表的資料
SELECT * FROM DBO.名稱清單
INTERSECT
SELECT * FROM DBO.名稱清單_BAK


說明：[出處：MSDN]

比較兩個查詢的結果來傳回個別值。

EXCEPT 會從左側查詢中傳回在右側查詢中找不到的任何個別值。

INTERSECT 會傳回 INTERSECT 運算元左右兩側查詢都傳回的任何個別值。

使用 EXCEPT 或 INTERSECT 的兩個查詢，其結果集的基本組合規則如下：

在所有查詢中，資料行的數目和順序都必須相同。

資料類型必須相容。

DB筆記

一、看temp table是否存在
IF OBJECT_ID('tempdb.dbo.#tmp_New') IS NOT NULL
DROP TABLE #tmp_New; 
二、直接執行組合字串
EXECUTE('TRUNCATE TABLE '+@Table_To)
EXECUTE('INSERT '+@Table_To+' SELECT * FROM ['+@Server+'].['+@Database+'].['+@Schema+'].['+@Table+']')
三、ISNULL 函數：以指定的取代值來取代 NULL-- 使用 ISNULL 函數，用 N/A 字串來取代所有 NULL 項目
SELECT CustomerID, City, ISNULL(Region, 'N/A') Region, Country
FROM Customers
GO
四、SET QUOTED_IDENTIFIER ON/OFF语句的作用
当在数据库中新建一个名字是USER的表的时候，常常会带来一些麻烦，因为USER是SQL中的关键字，但是上面的几个语句不会报错。再说一个概念：标示符是SQL中的中括号[]。
当SET QUOTED_IDENTIFIER值为ON时，双引号内的字符被当作是数据库对象。就是说双引号" "和标识符[]效果是一样样的，他们都表示引用的字符是数据库对象。单引号'表示字符串的边界。
当SET QUOTDE_IDENTIFIER OFF时，双引号被解释为字符串的边界，和单引号的作用是类似的。就是说双引号"不能当做标识符使用，但是可以当做字符边界，和单引号'的效果是一样样的。
可以做一个总结：当SET QUOTED_IDENTIFIER ON " "等同于[ ] 表示数据库对象；当SET QUOTED_IDENTIFIER OFF " "等同于' '表示字符串边界；还有这里的双引号" 并不是两个单引号'合起来的，是shift+”打出来的，初学者可能会犯这样的错误。
五、SET ANSI_NULLS ON/OFF
当 SET ANSI_NULLS 为 ON 时，即使 column_name 中包含空值，使用 WHERE column_name = NULL 的 SELECT 语句仍返回零行。即使 column_name 中包含非空值，使用 WHERE column_name <> NULL 的 SELECT 语句仍会返回零行。 
当 SET ANSI_NULLS 为 OFF 时，等于 (=) 和不等于 (<>) 比较运算符不遵从 SQL-92 标准。使用 WHERE column_name = NULL 的 SELECT 语句返回 column_name 中包含空值的行。使用 WHERE column_name <> NULL 的 SELECT 语句返回列中包含非空值的行。此外，使用 WHERE column_name <> XYZ_value 的 SELECT 语句返回所有不为 XYZ_value 也不为 NULL 的行。

常用SP查詢指令

--取得sp名稱https://msdn.microsoft.com/en-us/library/ms177596.aspx
select * from sysobjects where xtype='p'
--取得sp內容
select text, object_name(id) from sys.syscomments
where 1=1
and text like '%XXX%'
order by object_name(id)
--查詢db lock的table
select object_name(resource_associated_entity_id) as tablename,*
from sys.dm_tran_locks
where request_type='lock'

查詢正在執行的SQL指令

SELECT      r.scheduler_id as 排程器識別碼,
            status         as 要求的狀態,
            r.session_id   as SPID,
            r.blocking_session_id as BlkBy,
            substring(
    ltrim(q.text),
    r.statement_start_offset/2+1,
    (CASE
                 WHEN r.statement_end_offset = -1
                 THEN LEN(CONVERT(nvarchar(MAX), q.text)) * 2
                 ELSE r.statement_end_offset
                 END - r.statement_start_offset)/2)
                 AS [正在執行的 T-SQL 命令],
            r.cpu_time      as [CPU Time(ms)],
            r.start_time    as [開始時間],
            r.total_elapsed_time as [執行總時間],
            r.reads              as [讀取數],
            r.writes             as [寫入數],
            r.logical_reads      as [邏輯讀取數],
            -- q.text, /* 完整的 T-SQL 指令碼 */
            d.name               as [資料庫名稱]
FROM        sys.dm_exec_requests r 
   CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS q
   LEFT JOIN sys.databases d ON (r.database_id=d.database_id)
WHERE       r.session_id > 50 AND r.session_id <> @@SPID
ORDER BY    r.total_elapsed_time desc

Windows實用軟體庫

http://sourceforge.net/

Linux 基本指令

http://linux.vbird.org/linux_basic/redhat6.1/linux_06command.php