SQL Script to Assign All Bins to All Items in Microsoft Dynamics GP

I try to do as much through the front-end of Microsoft Dynamics GP as possible in order that the correct business logic be applied to the changes as possible. However, sometimes doing it that way takes far too much time. Recently when working with a particular client, we needed to assign all bins to all items.

Bins were being being introduced so there were no bins currently assigned, which meant a simple SQL script could be created using CROSS JOIN which produces a resultset where each item umber in the Item Master (IV00101) table was paired with the location code/bin number combination in the Site Bin Master (IV40701) table and inserted into Item Site Bin Priorities (IV00117).

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
INSERT INTO IV00117 --Item Site Bin Priorities (IV00117)
	(
		ITEMNMBR

		,LOCNCODE

		,Priority

		,BIN

		,MINSTOCKQTY

		,MAXSTOCKQTY

	)
--VALUES
	(
		SELECT
			['Item Number'].ITEMNMBR

			,['Site Bin Master'].LOCNCODE

			,ROW_NUMBER() OVER(PARTITION BY ['Item Number'].ITEMNMBR, ['Site Bin Master'].LOCNCODE ORDER BY ['Item Number'].ITEMNMBR, ['Site Bin Master'].LOCNCODE, ['Site Bin Master'].BIN) AS Priority

			,['Site Bin Master'].BIN

			,0 AS MINSTOCKQTY

			,0 AS MAXSTOCKQTY

		FROM
			IV00101 AS ['Item Number'] --Item Master (IV00101)
		CROSS JOIN

			IV40701 AS ['Site Bin Master']--Site Bin Master (IV40701)
	)
GO

If any assignments exist in the IV00117 table the script will fail with a duplicate key error; the script also assigns priority based on the alphabetical order of the site/bin number combinations which may not be suitable in all contexts.

Read original post SQL Script to Assign All Bins to All Items in Microsoft Dynamics GP at azurecurve|Ramblings of a Dynamics GP Consultant