barker/DB/migrate.cmd

@echo off
call:copyQuery a-Roles "SELECT GroupID, Name FROM Test.dbo.Auth_Groups;"
call:copyQuery b-Permissions "SELECT RoleID, Name FROM Test.dbo.Auth_Roles;"
call:copyQuery c-RolePermissions "SELECT RoleGroupID, RoleID, GroupID FROM Test.dbo.Auth_RoleGroups;"
call:copyQuery d-Users "SELECT UserID, Name, Password, CASE WHEN LockedOut = 1 THEN 't' ELSE 'f' END FROM Test.dbo.Auth_Users;"
call:copyQuery e-UserRoles "SELECT UserGroupID, UserID, GroupID FROM Test.dbo.Auth_UserGroups;"
call:copyQuery f-Customers "SELECT CustomerID, Name, CASE WHEN Address = '' THEN '\N' ELSE Address END, Phone + ' ' + Name FROM Test.dbo.Customers WHERE CustomerID != '2c716f4b-0736-429a-ad51-610d7c47cb5e';"
call:copyQuery g-FoodTables "SELECT FoodTableID, Name, 0, CASE WHEN IsActive = 1 THEN 't' ELSE 'f' END, '3f13f6e7-dc76-4fca-8fdb-b2bbf29b35df', SortOrder FROM Test.dbo.FoodTables;"
call:copyQuery h-Taxes "SELECT TaxID, Name, CASE WHEN Rate = .025 THEN .05 ELSE Rate END, 'f' FROM Test.dbo.Taxes;"
call:copyQuery i-MenuCategories "SELECT ProductGroupID, Name, CASE WHEN IsActive = 1 THEN 't' ELSE 'f' END, 'f', SortOrder FROM Test.dbo.ProductGroups;"
call:copyQuery j-SaleCategories "SELECT newid(), pgo.GroupType, 1, CAST((select top 1 t.TaxID from Test.dbo.Taxes t inner join Test.dbo.Products p on t.TaxID = p.VatID inner join Test.dbo.ProductGroups pg on p.ProductGroupID = pg.ProductGroupID where pg.GroupType = pgo.GroupType) AS Nvarchar(36)) FROM (select distinct pgx.GroupType from Test.dbo.ProductGroups pgx) as pgo;"
call:copyQuery k-Products "SELECT ProductID, Name, COALESCE(Units, '\N'), ProductGroupID, (SELECT GroupType FROM Test.dbo.ProductGroups pgs WHERE pgs.ProductGroupID = Products.ProductGroupID), Price, CASE WHEN HasHappyHour = 1 THEN 't' ELSE 'f' END , CASE WHEN IsActive = 1 THEN 't' ELSE 'f' END, CASE WHEN IsNotAvailable = 1 THEN 't' ELSE 'f' END, SortOrder, Quantity FROM Test.dbo.Products;"
call:copyQuery l-Modifiers "SELECT ModifierID, Name, CASE WHEN ShowInBill = 1 THEN 't' ELSE 'f' END, 't', 'e046ad33-dc65-4c78-8833-c3d3538d44c0', Price FROM Test.dbo.Modifiers;"
call:copyQuery m-Sections "SELECT NewID(), Location, 'f' FROM Test.dbo.PrintLocations GROUP BY Location;"
call:copyQuery n-Printers "SELECT NewID(), Printer, Printer, 0x1d564103 FROM Test.dbo.PrintLocations GROUP BY Printer, CutCode;"
call:copyQuery o-SectionPrinters "SELECT PrintLocationID, COALESCE(CAST(ProductGroupID AS Nvarchar(36)), '\N'), Location, Printer, Copies FROM Test.dbo.PrintLocations;"
call:copyQuery p-Vouchers "SELECT VoucherID, DATEADD(minute, -330, Date), Pax, UserID, DATEADD(minute, -330, CreationDate), DATEADD(minute, -330, LastEditDate), COALESCE(CAST(BillID AS Nvarchar(36)), '\N'), TableID, CustomerID, COALESCE(Narration, '\N'), VoidReason, CASE WHEN Printed = 0 THEN '0' WHEN Void = 1 THEN '5' ELSE VoucherType END, KotID FROM Test.dbo.Vouchers;"
call:copyQuery q-Kots  "SELECT KotID, VoucherID, Code, TableID, DATEADD(minute, -330, Date), UserID FROM Test.dbo.Kots;"
call:copyQuery r-Inventories "SELECT InventoryID, KotID, ProductID, SortOrder, Quantity, Price, CASE WHEN IsHappyHour = 1 THEN 't' ELSE 'f' END, CASE WHEN VatRate = .025 THEN .05 ELSE VatRate END, VatID, Discount FROM Test.dbo.Inventories;"
call:copyQuery s-InventoryModifiers "SELECT InventoryModifierID, InventoryID, ModifierID, 0 FROM Test.dbo.InventoryModifiers;"
call:copyQuery t-Overview "SELECT newid(), VoucherID, FoodTableID, Status FROM Test.dbo.FoodTables WHERE VoucherID IS NOT NULL;"
call:copyQuery u-Reprints "SELECT ReprintID, UserID, DATEADD(minute, -330, Date), VoucherID FROM Test.dbo.Reprints;"
call:copyQuery v-Settings "SELECT SettingID, Name, '{""Text"": ""' + Details + '""}' FROM Test.dbo.Settings;"
call:copyQuery w-VoucherSettlements "SELECT VoucherSettlementID, VoucherID, Settled, Amount FROM Test.dbo.VoucherSettlements;"
goto:eof

:copyQuery    - here starts my function identified by it's label
echo.
echo. Copying %~1
bcp "%~2" queryout csv\%~1.csv -c -t\t -S . -U sa -P 123456
goto:eof