Granite WMS/SQL

SQL 技巧和竅門

資料庫

• Granite
• Accpac

插入 ID

SET IDENTITY_INSERT tablename ON 
SET IDENTITY_INSERT tablename OFF

檢視 SQL 結果統計

SET STATISTICS IO ON
SET STATISTICS TIME ON

在資料庫中查詢觸發器

select * from sys.triggers

select [definition] from sys.sql_modules m
inner join sys.objects obj on obj.object_id=m.object_id 
where obj.type ='TR'

停用資料庫中的所有觸發器

EXEC sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER ALL"

活動監視器 - 釋放鎖

右鍵單擊 SQL 例項 開啟活動監視器 點選程序

有用的查詢

迭代/迴圈記錄的示例

declare @id bigint --id to loop
select @id = min(id) from OptionalFields where AppliesTo = 'MASTERITEM' --example get all optional fields for masteritems

while @id is not null
begin
	--do your thing insert update
	insert into OptionalFieldValues_MasterItem(Belongsto_id, OptionalField_id) 
    select ID, @id from MasterItem;
    --get next id
    select @id = min(id) from OptionalFields where ID > @id
end