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