sql

adding a country to existing uCommerce catalog

After creating two different catalogs for two different countries (US and Italy) in our uCommerce solution, I needed to add another country. This time however the Italy store and catalog are changed (manually) into an EMEA store. Therefore the new country is added to this EMEA store, in my case, using the same payment method and currency.

Basically you can use the stored procedure for creating a new store and remove the things you do not need. You will need to change the parameters somewhat to link the new country to the payment method (and perhaps other relations).

Here’s the stored procedure that let’s you do that.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Ruben Verschueren
-- Create date: 2016/07/28
-- Description: creates a new uCommerce country store
-- and adds it to an existing store
-- =============================================
CREATE PROCEDURE AddCountryToStore
@createdBy nvarchar(20),
@currencyIsoCode nvarchar(4),
@currenctExchangeRate int,
@countryName nvarchar(25),
@vatRate decimal(5,2),
@priceGroupDescription nvarchar(250),
@cultureCode nvarchar(6),
@paymentMethodId int,
@feePercent decimal(18,4),
@shippingMethodName nvarchar(128),
@productCatalogId int
AS
BEGIN
SET NOCOUNT ON;

DECLARE @currencyId int
DECLARE @pricegroupId int
DECLARE @countryId int
DECLARE @shippingMethodId int
DECLARE @definitionTypeId int
DECLARE @emailProfileId int
DECLARE @orderNumberId int
DECLARE @productCatalogGroupId int
DECLARE @ProductCatalogPriceGroupRelationId int

SELECT @definitionTypeId = definitionTypeId
FROM uCommerce_DefinitionType
WHERE name = 'PaymentMethod'

SELECT @emailProfileId = emailprofileid
FROM uCommerce_EmailProfile
WHERE name ='Default'

SELECT @orderNumberId = ordernumberid
FROM uCommerce_OrderNumberSerie
WHERE OrderNumberName = 'Default'

-- create currency
SELECT @currencyId = currencyid
FROM uCommerce_Currency
WHERE isocode = @currencyIsoCode AND deleted = 0

IF (@currencyId is not null)
BEGIN
PRINT 'currency already exists'
END
ELSE
BEGIN
INSERT uCommerce_Currency(isocode,exchangerate,deleted,guid)
VALUES (@currencyIsoCode, @currenctExchangeRate, 0, NEWID())
SELECT @currencyId = SCOPE_IDENTITY()
END

-- create pricegroup
SELECT @pricegroupId = pricegroupid
FROM uCommerce_PriceGroup
WHERE name = @countryName AND currencyId = @currencyId

IF (@pricegroupId is not null)
BEGIN
PRINT 'pricegroup already exists'
UPDATE uCommerce_PriceGroup
SET deleted = 0 WHERE name = @countryName AND currencyId = @currencyId
END
ELSE
BEGIN
INSERT uCommerce_PriceGroup(name,currencyid,vatrate,description,
createdon,createdby,modifiedon,modifiedby,deleted,guid)
VALUES (@countryName ,@currencyId,@vatRate, @priceGroupDescription,getdate(),
@createdBy,getdate(),@createdBy,0,NEWID())
SELECT @pricegroupId = SCOPE_IDENTITY()
END

-- create country
SELECT @countryId = countryid
FROM uCommerce_Country
WHERE name = @countryName AND Culture = @cultureCode
IF (@countryId is not null)
BEGIN
PRINT 'country already exists'
UPDATE uCommerce_Country
SET deleted = 0
WHERE name = @countryName
AND Culture = @cultureCode
END
ELSE
BEGIN
INSERT uCommerce_Country(name,culture,deleted)
VALUES (@countryName, @cultureCode, 0)
SELECT @countryId = SCOPE_IDENTITY()
END


-- needed?
SELECT @shippingMethodId = ShippingMethodId
FROM uCommerce_ShippingMethod
WHERE name = @shippingMethodName AND paymentmethodId = @paymentMethodId
IF (@shippingMethodId is not null)
BEGIN
PRINT 'shipping method already exists'
END
ELSE
BEGIN
INSERT uCommerce_ShippingMethod(name,PaymentMethodId,ServiceName,deleted)
VALUES (@shippingMethodName, @paymentMethodId, 'SinglePriceService', 0)
SELECT @shippingMethodId = SCOPE_IDENTITY()
END

--map shipping method to country
IF((SELECT count(*)
FROM uCommerce_ShippingMethodCountry
WHERE CountryId = @countryId and shippingMethodId = @shippingMethodId) = 0)
BEGIN
INSERT uCommerce_ShippingMethodCountry(CountryId,shippingMethodId)
VALUES(@CountryId,@shippingMethodId)
END

-- create ProductCatalog pricegroup relation
SELECT @ProductCatalogPriceGroupRelationId = ProductCatalogPriceGroupRelationId
FROM uCommerce_ProductCatalogPriceGroupRelation
WHERE ProductCatalogId = @productCatalogId and PriceGroupId = @pricegroupId
IF (@ProductCatalogPriceGroupRelationId is not null)
BEGIN
PRINT 'ProductCatalog is already linked to pricegroup'
END
ELSE
BEGIN
INSERT uCommerce_ProductCatalogPriceGroupRelation
(productcatalogid,PriceGroupId,SortOrder)
VALUES (@productCatalogId,@priceGroupId,0)
SELECT @ProductCatalogPriceGroupRelationId = SCOPE_IDENTITY()
END
END

Now that we created the stored procedure, we can execute it as many times as we want adding more countries to our store. Obviously you need to change the values of the parameters especially the PaymentMethodId and productCatalogId. In the uCommerce backend you will now see the new country, pricegroup etc. If you open up the catalog, you will now see the new country as an option in the allowed priceGroups. Just enable the checkbox and you’re good to go… after you add all the prices on the products that is.

1
2
3
4
5
6
7
8
9
10
11
12
EXEC AddCountryToStore 
'Ruben' --createdBy
,'EUR' --CurrencyIsoCode
,100 --currentExchangeRate
,'Netherlands' --countryName
,0.21 --vatRate
,'Pricing for NL eCommerce' --priceGroupDescription
,'nl-NL' --cultureCode
,8 --PaymentMethodId
,0.0000 --feePerentage
,'MyShippingMethod' --shippingmethodName
,5 --productCatalogId

duplicating a UCommerce Store

In a recent project we used uCommerce (6.XX) with Sitecore 8.1 to implement an international webshop.

Each language in sitecore will eventually be linked to it’s own shop,  starting with the italian one (linked to it-IT).  We opted to create a separate store per country/language in uCommerce because each of them would have some unique features. Some would have different prices, SKU’s, tax calculation and so on.

After we finished the Italian shop, we wanted to duplicate it so that the content editors wouldn’t have to enter all the common data again. As it turns out this isn’t supported (yet) by uCommerce. After confirming with our uCommerce buddy, I wrote an SQL script that would do just that.

If you are reading this it means you might be thinking of doing the same thing, so I must warn you I took a few shortcuts here and there.  For instance the product definitions are not duplicated and the script has some hard coded things like disabling product reviews. All this can be easily changed in the stored procedure code below. As the goal was only to do the bulk of the work you might want to create/duplicate more data.

Here’s a list of what the script creates or duplicates:

  • Currency
  • PriceGroup
  • Country (and existing country is activated)
  • Definition
  • Payment Method
  • Shipping Method
  • ProductCatalogGroup
  • Mapping between Shipping Method and ProductCatalogGroup
  • Mapping between Shipping Methood and Payment Method
  • Product Catalog
  • Mapping between Product Catalog and PriceGroup
  • Categories of products
  • Products (duplicating existing only)
  • Product properties
  • Product description
  • Product Category relation
  • Price
  • Variants
  • Variant Properties
  • Variant Description
  • Variant Price

You can call the stored procedure with the following script. All you have to do is feed in the correct parameters for you situation. The one shortcut I really don’t like is the SKU suffix. product SKU’s have to be unique so I add the suffix to garantee that. Since the content editor has to enter new ones anyway it’s not that big of a deal.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
EXEC CreateStore 
'Ruben' --createdBy
,'USD' --CurrencyIsoCode
,143 --currentExchangeRate
,'United States' --countryName
,0.22 --vatRate
,'Pricing for US eCommerce' --priceGroupDescription
,'en-US' --cultureCode
,'Adyen' --definitionName
,'Configuration for Adyen' --definitionDescription
,'Adyen Test' --PaymentMethodName
,'Adyen' --paymentMethodSeviceName
,0.0000 --feePerentage
,'ShipmentName' --shippingmethodName
,'United States' --productCatalogGroupName
,'CatalogName' --productCatalogName
,24 --parentCatalog
,'CategoryName' --productCategoryDefinitionName
,'_US' --SKU_suffix

And finally the stored procedure script. With the exception of the products (and related data) I always check if the data already exists, so you could execute the stored procedure several times if you wanted to. This was mostly handy during the development and saved me a lot of cleaning up. This way I could add the next bit and just execute the entire thing again without having to restore my database.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
CREATE PROCEDURE CreateStore	
@createdBy nvarchar(20),
@currencyIsoCode nvarchar(4),
@currenctExchangeRate int,
@countryName nvarchar(25),
@vatRate decimal(5,2),
@priceGroupDescription nvarchar(250),
@cultureCode nvarchar(6),
@definitionName nvarchar(512),
@definitionDescription nvarchar(max),
@paymentMethodName nvarchar(50),
@paymentMethodServiceName nvarchar(50),
@feePercent decimal(18,4),
@shippingMethodName nvarchar(128),
@productCatalogGroupName nvarchar(128),
@productCatalogName nvarchar(128),
@parentCatalog int,
@productCategoryDefinitionName nvarchar(128),
@skuSuffix nvarchar(5)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @currencyId int
DECLARE @pricegroupId int
DECLARE @countryId int
DECLARE @paymentMethodId int
DECLARE @shippingMethodId int
DECLARE @definitionId int
DECLARE @definitionTypeId int
DECLARE @emailProfileId int
DECLARE @orderNumberId int
DECLARE @productCatalogGroupId int
DECLARE @productCatalogId int
DECLARE @ProductCatalogPriceGroupRelationId int
DECLARE @ProjectCategoryId int

SELECT @definitionTypeId = definitionTypeId
FROM uCommerce_DefinitionType
WHERE name = 'PaymentMethod'

SELECT @emailProfileId = emailprofileid
FROM uCommerce_EmailProfile
WHERE name ='Default'

SELECT @orderNumberId = ordernumberid
FROM uCommerce_OrderNumberSerie
WHERE OrderNumberName = 'Default'

SELECT @ProjectCategoryId = definitionid
FROM ucommerce_Definition
WHERE name = @productCategoryDefinitionName

-- create currency
SELECT @currencyId = currencyid
FROM uCommerce_Currency
WHERE isocode = @currencyIsoCode AND deleted = 0
IF (@currencyId is not null)
BEGIN
PRINT 'currency already exists'
END
ELSE
BEGIN
INSERT uCommerce_Currency(isocode,exchangerate,deleted,guid)
VALUES (@currencyIsoCode, @currenctExchangeRate, 0, NEWID())
SELECT @currencyId = SCOPE_IDENTITY()
END

-- create pricegroup
SELECT @pricegroupId = pricegroupid
FROM uCommerce_PriceGroup
WHERE name = @countryName AND currencyId = @currencyId
IF (@pricegroupId is not null)
BEGIN
PRINT 'pricegroup already exists'
END
ELSE
BEGIN
INSERT uCommerce_PriceGroup(name,currencyid,vatrate,description,
createdon,createdby,modifiedon,modifiedby,deleted,guid)
VALUES (@countryName ,@currencyId,@vatRate, @priceGroupDescription,
getdate(),@createdBy,getdate(),@createdBy,0,NEWID())
SELECT @pricegroupId = SCOPE_IDENTITY()
END

-- create country
SELECT @countryId = countryid
FROM uCommerce_Country
WHERE name = @countryName AND Culture = @cultureCode
IF (@countryId is not null)
BEGIN
PRINT 'country already exists, activating it'
UPDATE uCommerce_Country
SET deleted = 0
WHERE name = @countryName
AND Culture = @cultureCode
END
ELSE
BEGIN
INSERT uCommerce_Country(name,culture,deleted)
VALUES (@countryName, @cultureCode, 0)
SELECT @countryId = SCOPE_IDENTITY()
END

-- create definition
SELECT @definitionId = definitionId
FROM uCommerce_Definition
WHERE name = @definitionName AND DefinitionTypeId = @definitionTypeId
IF (@definitionId is not null)
BEGIN
PRINT 'definition already exists'
END
ELSE
BEGIN
INSERT uCommerce_Definition(name,definitiontypeid,description,deleted,sortorder,guid,builtin)
VALUES (@definitionName, @definitionTypeId, @definitionDescription,0,0,NEWID(),0)
SELECT @definitionId = SCOPE_IDENTITY()
END

-- create payment method
SELECT @paymentMethodId = paymentMethodId
FROM uCommerce_PaymentMethod
WHERE name = @paymentMethodName
AND paymentMethodServiceName = @paymentMethodServiceName
AND definitionId = @definitionId
IF (@paymentmethodid is not null)
BEGIN
PRINT 'payment method already exists'
END
ELSE
BEGIN
INSERT ucommerce_paymentmethod(name,feepercent,paymentmethodservicename,enabled,deleted,
modifiedon,modifiedby,pipeline,definitionid)
VALUES (@paymentmethodname, @feepercent, @paymentmethodservicename,1,0,
getdate(),@createdby,'checkout',@definitionid)
SELECT @paymentmethodid = scope_identity()
END

-- create shippingMethod
SELECT @shippingMethodId = ShippingMethodId
FROM uCommerce_ShippingMethod
WHERE name = @shippingMethodName
AND paymentmethodId = @paymentMethodId

IF (@shippingMethodId is not null)
BEGIN
PRINT 'shipping method already exists'
END
ELSE
BEGIN
INSERT uCommerce_ShippingMethod(name,PaymentMethodId,ServiceName,deleted)
VALUES (@shippingMethodName, @paymentMethodId, 'SinglePriceService', 0)
SELECT @shippingMethodId = SCOPE_IDENTITY()
END

-- create ProductCatalogGroup
SELECT @productCatalogGroupId = productCatalogGroupId
FROM uCommerce_ProductCatalogGroup
WHERE name = @productCatalogGroupName
AND emailprofileId = @emailProfileId
AND currencyid= @currencyId AND ordernumberId = @orderNumberId
IF (@productCatalogGroupId is not null)
BEGIN
PRINT 'ProductCatalogGroup already exists'
END
ELSE
BEGIN
INSERT uCommerce_ProductCatalogGroup(name,EmailProfileId,currencyid,domainid,
OrderNumberId,deleted,CreateCustomersAsMembers,ProductReviewsRequireApproval,guid )
VALUES (@productCatalogGroupName, @emailProfileId,@currencyid,'website',
@orderNumberId,0,0,0,NEWID())
SELECT @productCatalogGroupId = SCOPE_IDENTITY()
END

--map shipping method to product catalog group
IF((SELECT count(*) FROM uCommerce_ProductCatalogGroupShippingMethodMap
WHERE productcataloggroupid = @productCatalogGroupId AND shippingMethodId = @shippingMethodId) = 0)
BEGIN
INSERT uCommerce_ProductCatalogGroupShippingMethodMap(productcataloggroupid,shippingMethodId)
VALUES(@productCatalogGroupId,@shippingMethodId)
END

--map shipping method to country
IF((SELECT count(*) FROM uCommerce_ShippingMethodCountry
WHERE CountryId = @countryId AND shippingMethodId = @shippingMethodId) = 0)
BEGIN
INSERT uCommerce_ShippingMethodCountry(CountryId,shippingMethodId)
VALUES(@CountryId,@shippingMethodId)
END

--map shipping method to paymentmethod
IF((SELECT count(*) FROM uCommerce_ShippingMethodPaymentMethods
WHERE PaymentMethodId = @paymentMethodId AND shippingMethodId = @shippingMethodId) = 0)
BEGIN
INSERT uCommerce_ShippingMethodPaymentMethods(paymentMethodId,shippingMethodId)
VALUES(@paymentMethodId,@shippingMethodId)
END

-- create ProductCatalog
SELECT @productCatalogId = productCatalogId
FROM uCommerce_ProductCatalog
WHERE name = @productCatalogName
AND ProductCatalogGroupId = @productCatalogGroupId
AND PriceGroupId = @pricegroupId
IF (@productCatalogId is not null)
BEGIN
PRINT 'ProductCatalog already exists'
END
ELSE
BEGIN
INSERT uCommerce_ProductCatalog(productcataloggroupid,name,pricegroupid,ShowPricesIncludingVAT,
IsVirtual,DisplayOnWebSite,LimitedAccess,Deleted,CreatedBy,CreatedOn,ModifiedBy,ModifiedOn,
SortOrder,guid)
VALUES (@productCatalogGroupId,@productCatalogName,@pricegroupId,0,0,1,0,0,@createdBy,getdate(),
@createdBy,getdate(),0,NEWID())
SELECT @productCatalogId = SCOPE_IDENTITY()
END

-- create ProductCatalog pricegroup relation
SELECT @ProductCatalogPriceGroupRelationId = ProductCatalogPriceGroupRelationId
FROM uCommerce_ProductCatalogPriceGroupRelation
WHERE ProductCatalogId = @productCatalogId and PriceGroupId = @pricegroupId
IF (@ProductCatalogPriceGroupRelationId is not null)
BEGIN
PRINT 'ProductCatalog is already linked to pricegroup'
END
ELSE
BEGIN
INSERT uCommerce_ProductCatalogPriceGroupRelation(productcatalogid,PriceGroupId,SortOrder)
VALUES (@productCatalogId,@priceGroupId,0)
SELECT @ProductCatalogPriceGroupRelationId = SCOPE_IDENTITY()
END

-- create Categories for catalog
IF((SELECT count(categoryId) FROM uCommerce_Category
WHERE productCatalogId = @productCatalogId AND definitionId = @ProjectCategoryId) = 0)
INSERT INTO uCommerce_Category(name,DisplayOnSite,createdon,ParentCategoryId,
ProductCatalogId,ModifiedOn,ModifiedBy,Deleted,SortOrder,CreatedBy,DefinitionId,guid)
SELECT name,DisplayOnSite,getdate(),ParentCategoryId,
@productCatalogId,getdate(),@createdBy,0,0,@createdBy,@ProjectCategoryId,NEWID()
FROM ucommerce_category
WHERE productcatalogid = @parentCatalog

-- create products
IF OBJECT_ID('tempdb..#Products') IS NOT NULL DROP TABLE #Products

SELECT productid
INTO #Products
FROM uCommerce_Product
WHERE parentproductid IS NULL AND variantsku IS NULL

DECLARE @Id int
DECLARE @newProductId int
DECLARE @categoryId int

WHILE EXISTS(SELECT * FROM #Products)
BEGIN
SELECT Top 1 @Id = productid FROM #Products
/*
* create product
* add product/category relation => [uCommerce_CategoryProductRelation]
=> duplicate FROM existing, change id's to correct product/category id's
* add price => [uCommerce_PriceGroupPrice]
*/


INSERT INTO uCommerce_Product(sku,name,displayonsite,weight,productDefinitionId,
AllowOrdering,modifiedBy, ModifiedOn, CreatedBy, CreatedOn, Guid)
SELECT sku + @skuSuffix,name,displayonsite,weight,productdefinitionId,
allowordering,@createdBy, getdate(),@createdBy, getdate(),NEWID()
FROM uCommerce_Product
WHERE productid = @Id

SELECT @newProductId = SCOPE_IDENTITY()

PRINT 'creating properties for' + cast(@newProductId as varchar(5))
--create product properties
INSERT INTO uCommerce_ProductProperty(value,ProductDefinitionFieldId,ProductId)
SELECT value, productdefinitionfieldid, @newProductId
FROM uCommerce_ProductProperty
WHERE productid = @Id

PRINT 'creating descriptions for' + cast(@newProductId as varchar(5))
--create product descriptions
INSERT INTO uCommerce_ProductDescription(productid,DisplayName,shortdescription,
LongDescription,CultureCode)
SELECT @newProductId,displayname,shortdescription,LongDescription,CultureCode
FROM uCommerce_ProductDescription
WHERE productid = @id

SELECT @categoryId = u2.categoryid
FROM uCommerce_category u1
INNER JOIN uCommerce_category u2 on u1.name = u2.name
INNER JOIN uCommerce_CategoryProductRelation cpr
on u1.categoryId = cpr.CategoryId
WHERE cpr.ProductId = @Id
AND u2.definitionId = @ProjectCategoryId
AND u2.productcatalogid = @productCatalogId

PRINT 'creating product/category relations for' + cast(@newProductId as varchar(5))
--create category/product relation
INSERT uCommerce_CategoryProductRelation(ProductId,CategoryId,SortOrder)
VALUES (@newProductId,@categoryId,0)


PRINT 'adding prices for' + cast(@newProductId as varchar(5))
--add price
INSERT uCommerce_PriceGroupPrice(productId,Price,DiscountPrice,PriceGroupId)
SELECT @newProductId, price, discountprice, @pricegroupId
FROM uCommerce_PriceGroupPrice
WHERE ProductId = @Id

--add product variants and prices
IF OBJECT_ID('tempdb..#Productvariants') IS NOT NULL DROP TABLE #Productvariants
SELECT productid
INTO #Productvariants
FROM uCommerce_Product
WHERE parentproductid = @Id
AND variantsku IS NOT NULL

DECLARE @variantId int
DECLARE @newVariantId int

WHILE EXISTS(SELECT * FROM #Productvariants)
BEGIN
SELECT Top 1 @variantId = productid
FROM #Productvariants

INSERT INTO uCommerce_Product(sku,variantsku,ParentProductId,name,displayonsite,weight,
productDefinitionId,AllowOrdering,modifiedBy, ModifiedOn, CreatedBy, CreatedOn, Guid)
SELECT sku+ @skuSuffix ,variantsku + @skuSuffix,@newProductId,name,displayonsite,weight,
productdefinitionId,allowordering,@createdBy, getdate(),@createdBy, getdate(),NEWID()
FROM uCommerce_Product
WHERE productid = @variantId

SELECT @newVariantId = SCOPE_IDENTITY()

--create product variant properties
INSERT INTO uCommerce_ProductProperty(value,ProductDefinitionFieldId,ProductId)
SELECT value, productdefinitionfieldid, @newVariantId
FROM uCommerce_ProductProperty
WHERE productid = @variantId

--create product descriptions
--INSERT INTO uCommerce_ProductDescription(productid,DisplayName,shortdescription,
LongDescription,CultureCode)
--SELECT @newProductId,displayname,shortdescription,LongDescription,CultureCode
--FROM uCommerce_ProductDescription
--WHERE productid = @variantId

--add variant price
INSERT uCommerce_PriceGroupPrice(productId,Price,DiscountPrice,PriceGroupId)
SELECT @newVariantId, price, discountprice, @pricegroupId
FROM uCommerce_PriceGroupPrice
WHERE ProductId = @variantId

Delete #Productvariants WHERE productid = @variantId
END
--end product variants and prices
Delete #Products WHERE productid = @Id
END
END

At first it seemed that we didn’t need duplicate product definitions since they would be identical. As it tends to happen in IT… things change. So here’s a script that will do just that. Since the relations between the definitions and the field values will probably change, I did not duplicate them. Here’s a list of what the script duplicates: * ProductDefinition * ProductDefinitionField

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- fields and inheritance should be set manually
-- add ProductDefinitions
IF OBJECT_ID('tempdb..#ProductDefinitions') IS NOT NULL DROP TABLE #ProductDefinitions
SELECT productdefinitionid,name,deleted
INTO #ProductDefinitions
FROM uCommerce_ProductDefinition

DECLARE @name nvarchar(512)
DECLARE @deleted bit
DECLARE @newDefinitionId int
DECLARE @productdefinitionId int
DECLARE @skuSuffix nvarchar(50)

WHILE EXISTS(SELECT * FROM #ProductDefinitions)
BEGIN
SELECT Top 1 @productdefinitionId = productdefinitionId,
@name = name, @deleted = deleted
FROM #ProductDefinitions

INSERT INTO uCommerce_ProductDefinition(name,deleted,guid)
SELECT name + @skuSuffix, @deleted, NEWID()
FROM uCommerce_ProductDefinition
WHERE productdefinitionId = @productdefinitionId

SELECT @newDefinitionId = SCOPE_IDENTITY()

--create product definition fields
INSERT INTO uCommerce_ProductDefinitionField
SELECT DataTypeId,@newDefinitionId,Name,DisplayOnSite,IsVariantProperty,Multilingual
,RenderInEditor,Searchable,Deleted,SortOrder,Facet,NEWID()
FROM uCommerce_ProductDefinitionField
WHERE productdefinitionid = @productdefinitionId


Delete #ProductDefinitions WHERE productdefinitionid = @productdefinitionid
END

And that’s the end of this chapter. The presented code here may not be a silver bullet but I hope it’s at least a machete clearing a path for you.

SQL cheatsheet: snippets and reference material

Here are a few of the SQL statements I keep googling for. I thought it would be helpful to keep the in one place, so I’ll have a cheatsheet next time.

Get a list of all tables in the database

1
2
3
SELECT * FROM sys.Tables
--OR
EXEC sp_tables '%', '%', 'master', "'TABLE'"

Get a list of all tables, views and system tables in the database

1
EXEC sp_tables '%'

Get a list of tables that the view depends on

1
2
3
4
SELECT view_name, Table_Name
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE View_Name = '<giveViewName>'
ORDER BY view_name, table_name

Get a list of all views that depend on a given table

1
2
3
4
SELECT view_name, Table_Name
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE Table_Name= 'Address'
ORDER BY view_name, table_name

Get al list of all functions

1
2
3
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE upper(ROUTINE_TYPE) = 'FUNCTION'

Get information about all fields in a certain table

1
2
3
SELECT column_name, data_type,  is_nullable, character_maximum_length
FROM information_schema.columns
WHERE table_name='contents';

Get a list of tables with their create and modified dates

1
2
SELECT name, object_id, create_date, modify_date
FROM sys.tables;

Get a list of constraints and the fields they apply to for a given table.

1
2
3
4
5
6
SELECT a.table_name,  a.constraint_name, b.column_name,  a.constraint_type
FROM information_schema.table_constraints a, information_schema.key_column_usage b
WHERE a.table_name = 'contents'
AND  a.table_name = b.table_name
AND  a.table_schema = b.table_schema
AND  a.constraint_name = b.constraint_name;

Example of how to generate a script for all tables

1
2
3
SELECT 'SELECT count(1) FROM <a class="incipient" title="[click to create page]"' + 
' href="https://somelink/%27%20+%20table_name%20+%20%27">' + table_name + '</a>;'
FROM information_schema.tables;

Get the definition (code) of a user defined stored procedure

1
2
SELECT routine_definition FROM INFORMATION_SCHEMA.ROUTINES
WHERE specific_name = ‘USP_NAME'

Retrieve the collation of a column

1
2
3
4
SELECT
col.name, col.collation_name
FROM sys.columns col
WHERE object_id = OBJECT_ID('TableName')

Solve collation issues in a join, union, …

1
2
 ALTER TABLE tablename ALTER COLUMN id
COLLATE Latin1_General_CI_AS NOT NULL

Retrieve FK en PK information

1
sp_help ‘TABLENAME’

​Check whick document types can be searched with a full text search.

uncomment the “WHERE” part to check for a specific document type (in this case pdf) ​

1
2
3
SELECT document_type, path 
FROM sys.fulltext_document_types 
---WHERE document_type = '.pdf'

Another way of finding references between tables

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT s1.name as FROM_schema
, o1.Name as FROM_table
, s2.name as to_schema
, o2.Name as to_table
FROM sys.foreign_keys fk
INNER JOIN sys.objects o1
ON fk.parent_object_id = o1.object_id
INNER JOIN sys.schemas s1
ON o1.schema_id = s1.schema_id
INNER JOIN sys.objects o2
ON fk.referenced_object_id = o2.object_id
INNER JOIN sys.schemas s2
ON o2.schema_id = s2.schema_id
--For the purposes of finding dependency hierarchy
-- we're not worried about self-referencing tables
WHERE NOT(s1.name = s2.name AND o1.name = o2.name)

A word on collation

What is collation?

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

Case sensitivity

If A and a, B and b, etc. are treated in the same way then it is case-insensitive. A computer treats A and a differently because it uses ASCII code to differentiate the input. The ASCII value of A is 65, while a is 97. The ASCII value of B is 66 and b is 98.

Accent sensitivity

If a and á, o and ó are treated in the same way, then it is accent-insensitive. A computer treats a and á differently because it uses ASCII code for differentiating the input. The ASCII value of a is 97 and áis 225. The ASCII value of o is 111 and ó is 243.

Kana Sensitivity

When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.

Width sensitivity

When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive. Database, Tables and columns with different collation SQL Server 2000 allows the users to create databases, tables and columns in different collations.

source

Removing tags FROM text

The following example shows a way you can remove tags FROM text. In this case only the content of a couple of tags needed to be retrieved FROM the text.

Full example and problem description can be found here

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
declare @var nvarchar(max)
declare @tag nvarchar(max)
declare @label nvarchar(max)
declare @start int
declare @stop int
declare @len int
declare @needed int

set @var = '<Name>Example1</Name>
<Type>String</Type>
<Nullable>False</Nullable>
<AllowBlank>False</AllowBlank>
<Prompt>Start Date (DD-MMM-YYYY)</Prompt>
<PromptUser>True</PromptUser>
<Parameter> </Parameter>
<Name>Example2</Name>
<Type>String</Type>
<Nullable>False</Nullable>
<AllowBlank>False</AllowBlank>
<Prompt>Case (Enter Case Number, % for all, OR %AL% for Alberta)</Prompt>
<PromptUser>True</PromptUser>
<DefaultValues>
<Value>%al%</Value>
</DefaultValues>
<Values>
<Value>%al%</Value>
</Values>
<Parameter></Parameter>'


set @needed = 0
set @start = charindex('<',@var)
set @stop = charindex('>',@var)
set @len = @stop - @start +1
set @tag = substring(@var,@start,@len)
set @label = substring(@var,@start+1,@len-2)
set @var = replace(@var,@tag,@label + ' : ')

while(@start <> 0)
begin
set @start = charindex('<',@var)
set @stop = charindex('>',@var)
set @len = @stop - @start +1
if(@start <> 0)
begin
set @tag = substring(@var,@start,@len)
if(charindex('/',@tag) = 0)
begin
set @label = substring(@var,@start+1,@len-2)+ ' : '
if(lower(@label) <> 'name : ' and lower(@label) <> 'value : ' and lower(@label) <> 'prompt : ')
begin
set @needed = 0
set @var = replace(@var,@tag,'')
set @start = @stop - len(@tag)
set @stop = charindex('<',@var)
set @len = @stop - @start
set @tag = substring(@var,@start,@len)
set @var = replace(@var,@tag,'')
end
end
else
begin
set @label = ''
end
set @var = replace(@var,@tag,@label)
end
end
print replace(@var,'
','')