Overview
The Guide will take you through the SQL Script process in order repair a Store Table where the StoreID's are not Sequential , ie 1, 2, 3.
There are instances of Databases where a Venue might have been converted from a multivenue to a single site and the Store numbers are now not sequential or starting from StoreID 1, instead they have StoreID's like 127, 130, 138 etc
This results in other errors for functions like Handling Fee where the Handling fee Array cannot populate due to the corrupted StoreID's
Prerequisites
SSMS
Full Backup
Make a full backup of the database before proceeding, encase you have to roll back.
Login to SSMS
The following script was used to identify all the Tables where StoreID is being used.
This is purely for reference but useful to know where the changes are going to take place.
--All Tables that contain StoreID
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%StoreID%'
ORDER BY schema_name, table_name;
Identify the current Store Records
Do a select query and make notes of the current StoreID's
--Verify Stores
Select * from Store
Change the SEED Numbering on Store Table
This will set the Record numbering to start from StoreID 1
--Reset SEED
DBCC CHECKIDENT (Store, RESEED, 0)
Set Identity insert for the Store Table to ON
--Change IDENTITY Insert
SET IDENTITY_INSERT Store ON
Correct the Store Records to Start from 1
Edit the Store Table and Copy the Existing Stores as New Stores Records
This has to be done as you cannot change the StoreID's as they are Primary Keys
Please see Video on the Right to show an example of the process.
Set Identity insert for the Store Table to OFF
--Change IDENTITY Insert
SET IDENTITY_INSERT Store OFF
Verify the Current Store List
This should include the Old Store Records and the New Store Records
Where the NEW Store Records ID's should number 1, 2 and 3
Delete the OLD Store Records
Now that the NEW Store Records are in place we need to remove the OLD Store Records
--Delete old Stores
Delete Store where StoreID not in (1,2,3)
Verify the Current Store List
This should include the New Store Records only.
--Verify Stores
Select * from Store
Correcting the StoreID Records for All Tables per Store
The following script is used to create a TempDB that houses all the SQL Script required to update the StoreID's per Store.
So for example, if the site has 3 Stores you will have to run the Script 3 times with amendments to the OrgStoreID and NewStoreID Values within the Script.
In our example, the Orginal StoreID for the Bar Store was '120', and the New StoreID is now '1'
So below pointed out in green we have to: SET @OrgStoreID = 120 and SET @NewStoreID = 1
Run the Script, it will loop through each table and set the StoreID's to the new value.
Repeat the process for any additional stores i.e Bistro Original StoreID was 137 is it now StoreID 2
--Builds a tempDB with SQLSCRIPTS.
IF OBJECT_ID('tempdb..#BaseData') IS NOT NULL DROP TABLE #BaseData
GO
CREATE TABLE #BaseData (Schema_Name sysname, Table_Name sysname, Field_Name sysname, SQLScript varchar(max));
DECLARE @FieldName varchar(20)
SET @FieldName = '%StoreID%'
DECLARE @OrgStoreID int
--Set Orignal StoreID below....
SET @OrgStoreID = 120 --<<<CHANGE VALUE HERE
DECLARE @NewStoreID int
--Set New StoreID below....
SET @NewStoreID = 1 --<<<CHANGE VALUE HERE
INSERT INTO #BaseData (Schema_Name, Table_Name, Field_Name, SQLScript)
SELECT
,'update ' + s.name + '.' + o.name + ' SET ' + c.name + ' = ' + CONVERT(VARCHAR(20),@NewStoreID) + ' where ' + c.name + ' = ' + CONVERT(VARCHAR(20),@OrgStoreID)
FROM sys.columns c
JOIN sys.types t
ON c.user_type_id = t.user_type_id
JOIN sys.objects o
ON c.object_id = o.object_id
JOIN sys.schemas s ON o.schema_id=s.schema_id
WHERE c.name LIKE @FieldName
AND o.type_desc = 'USER_TABLE'
AND o.name not like 'Store';
SELECT * FROM #BaseData
-- THIS LOOPS THROUGH THE TEMPDB AND RUNS ALL THE SQLSCRIPTS FOR THE STOREIDS
DECLARE @sql VARCHAR(MAX)
DECLARE @SqlScript VARCHAR(max)
DECLARE c CURSOR LOCAL FAST_FORWARD FOR
SELECT SQLScript
FROM #BaseData;
OPEN c;
FETCH NEXT FROM c INTO @SQLScript;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @SqlScript
EXEC(@sql);
FETCH NEXT FROM c INTO @SQLScript;
END
CLOSE c;
DEALLOCATE c;
Renumber Stores Sequentially v1.00 (10/03/23) by Jamie Bradshaw
/*
Renumber Stores Sequentially v1.00 (10/03/23) by Jamie Bradshaw (jamie@bepoz.com.au)
Based on previous script concept by Calvin Schentke
Version agnostic
Renumbers store table sequentially and dynamically updates all storeid fields in Bepoz database
Updates store table to respect redirects
No user input necessary.
*/
--Take a copy to reinsert into store, keep a second copy incase things go wrong.
select * into storebackup from store
select * into storebackupkeep from store --This will error and stop script being run more than once, it also keeps an original copy of the store table.
-- Delete and Reseed Store table
delete from store
dbcc checkident('store', reseed, 0)
--Variables to dynamically build store insert query
declare @columnname varchar(max) = ''
declare @storecolumns varchar(max) = ''
declare @insertquery varchar(max) = ''
--Cursor to populate insert query column names
declare cursor_createquery cursor
for
select c.name as col
from sys.columns c join sys.tables t on c.object_id = t.object_id
where t.name = 'Store' and c.name != 'StoreId' --Exclude Identity Column
-- Build list of columns in store table into insert format
open cursor_createquery
fetch next from cursor_createquery into @columnname
while @@FETCH_STATUS = 0
begin
set @storecolumns += @columnname + ','
fetch next from cursor_createquery into @columnname
end
close cursor_createquery
deallocate cursor_createquery
--Trim last comma
set @storecolumns = left(@storecolumns, len(@storecolumns)-1)
-- Build dynamic insert query
set @insertquery = 'insert into store '
set @insertquery += '(' + @storecolumns + ')' -- Field List
set @insertquery += '(select ' + @storecolumns + ' from storebackup)'
-- Insert storebackup back into store allowing identity column to provide sequential ids
exec sp_sqlexec @insertquery
--Link old store numbers to new store numbers
select s.name, sb.storeid as oldstoreid, s.storeid as newstoreid
into #tempstore
from storebackup sb join store s on s.name = sb.name
--Create queries to update storeid in all tables containing storeid (exclude store/storebackup, fixed later)
select t.name as [table], c.name as [column], ts.oldstoreid, ts.newstoreid, 'Update ' + t.name + ' set ' + c.name + ' = ' + cast(ts.newstoreid as varchar) + ' where ' + c.name + ' = ' + cast(ts.oldstoreid as varchar) as query
into #tempquery
from sys.columns c join sys.tables t on c.object_id = t.object_id
join #tempstore ts on 1=1
where c.name like '%StoreId%'
and t.name not in ('store', 'storebackup', 'storebackupkeep')
declare @storeupdatequery varchar(max)
--Exec dynamic update queries created in previous step
declare cursor_fixstores cursor
for
select query from #tempquery
open cursor_fixstores
fetch next from cursor_fixstores into @storeupdatequery
while @@FETCH_STATUS = 0
begin
exec sp_sqlexec @storeupdatequery
fetch next from cursor_fixstores into @storeupdatequery
end
close cursor_fixstores
deallocate cursor_fixstores
--Update Store Table to respect redirects
--Restock
update s set s.restockedfrom = ts.newstoreid
from store s join #tempstore ts on s.restockedfrom = ts.oldstoreid
where s.restockedfrom = ts.oldstoreid
--AltStores
update s set s.altstoretype0 = ts.newstoreid
from store s join #tempstore ts on s.altstoretype0 = ts.oldstoreid
where s.altstoretype0 = ts.oldstoreid
update s set s.altstoretype1 = ts.newstoreid
from store s join #tempstore ts on s.altstoretype1 = ts.oldstoreid
where s.altstoretype1 = ts.oldstoreid
update s set s.altstoretype2 = ts.newstoreid
from store s join #tempstore ts on s.altstoretype2 = ts.oldstoreid
where s.altstoretype2 = ts.oldstoreid
update s set s.altstoretype3 = ts.newstoreid
from store s join #tempstore ts on s.altstoretype3 = ts.oldstoreid
where s.altstoretype3 = ts.oldstoreid
--ReqStores
update s set s.reqstoretype0 = ts.newstoreid
from store s join #tempstore ts on s.reqstoretype0 = ts.oldstoreid
where s.reqstoretype0 = ts.oldstoreid
update s set s.reqstoretype1 = ts.newstoreid
from store s join #tempstore ts on s.reqstoretype1 = ts.oldstoreid
where s.reqstoretype1 = ts.oldstoreid
update s set s.reqstoretype2 = ts.newstoreid
from store s join #tempstore ts on s.reqstoretype2 = ts.oldstoreid
where s.reqstoretype2 = ts.oldstoreid
update s set s.reqstoretype3 = ts.newstoreid
from store s join #tempstore ts on s.reqstoretype3 = ts.oldstoreid
where s.reqstoretype3 = ts.oldstoreid
update s set s.reqstoretype4 = ts.newstoreid
from store s join #tempstore ts on s.reqstoretype4 = ts.oldstoreid
where s.reqstoretype4 = ts.oldstoreid
update s set s.reqstoretype5 = ts.newstoreid
from store s join #tempstore ts on s.reqstoretype5 = ts.oldstoreid
where s.reqstoretype5 = ts.oldstoreid
update s set s.reqstoretype6 = ts.newstoreid
from store s join #tempstore ts on s.reqstoretype6 = ts.oldstoreid
where s.reqstoretype6 = ts.oldstoreid
update s set s.reqstoretype7 = ts.newstoreid
from store s join #tempstore ts on s.reqstoretype7 = ts.oldstoreid
where s.reqstoretype7 = ts.oldstoreid
--BaseStorePrice
update s set s.basestoreprice_1 = ts.newstoreid
from store s join #tempstore ts on s.basestoreprice_1 = ts.oldstoreid
where s.basestoreprice_1 = ts.oldstoreid
update s set s.basestoreprice_2 = ts.newstoreid
from store s join #tempstore ts on s.basestoreprice_2 = ts.oldstoreid
where s.basestoreprice_2 = ts.oldstoreid
update s set s.basestoreprice_3 = ts.newstoreid
from store s join #tempstore ts on s.basestoreprice_3 = ts.oldstoreid
where s.basestoreprice_3 = ts.oldstoreid
update s set s.basestoreprice_4 = ts.newstoreid
from store s join #tempstore ts on s.basestoreprice_4 = ts.oldstoreid
where s.basestoreprice_4 = ts.oldstoreid
update s set s.basestoreprice_5 = ts.newstoreid
from store s join #tempstore ts on s.basestoreprice_5 = ts.oldstoreid
where s.basestoreprice_5 = ts.oldstoreid
update s set s.basestoreprice_6 = ts.newstoreid
from store s join #tempstore ts on s.basestoreprice_6 = ts.oldstoreid
where s.basestoreprice_6 = ts.oldstoreid
update s set s.basestoreprice_7 = ts.newstoreid
from store s join #tempstore ts on s.basestoreprice_7 = ts.oldstoreid
where s.basestoreprice_7 = ts.oldstoreid
update s set s.basestoreprice_8 = ts.newstoreid
from store s join #tempstore ts on s.basestoreprice_8 = ts.oldstoreid
where s.basestoreprice_8 = ts.oldstoreid
--Cleanup
drop table #tempstore
drop table #tempquery
drop table storebackup
Database Update Tables and Rebuild Indexes
Once the Script has completed and changed all the StoreID's for each Store, I would recommend running Update Tables and Rebuild Indexes from Database.exe
Backoffice
Login to Backoffice and navigate to Venue Stores and Till and verify that the correct Store Structure is now in place.
