Granite WMS/SQL

SQL tips and tricks

Database

• Granite
• Accpac

Insert ID

SET IDENTITY_INSERT tablename ON 
SET IDENTITY_INSERT tablename OFF

View sql result statistics

SET STATISTICS IO ON
SET STATISTICS TIME ON

Find triggers in database

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'

Disable all triggers in database

EXEC sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER ALL"

Activity monitor - Release locks

Right click on the SQL instance Open Activity monitor Click processes

Helpful query

Example to iterate / loop over records

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