ADNSFScripts/9500adnsfLocalizationObjectNameUpdate.sql at master · friendscottn/ADNSFScripts · GitHub

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
-- Use the triggers to populate the localized names table
print 'Populating LocalizedObjectName table from existing objects'
declare @localeUpdateChunkSize int = 1000,
		@localeUpdateIndex int,
		@localeUpdateCount int

set nocount on

select @localeUpdateCount = count(*), @localeUpdateIndex = 1 from Product
while @localeUpdateIndex  < @localeUpdateCount
begin
	print '    Updating Products ' + convert(nvarchar(max), @localeUpdateIndex) + ' to ' + convert(nvarchar(max), @localeUpdateIndex + @localeUpdateChunkSize - 1) + ' of ' + convert(nvarchar(max), @localeUpdateCount)
	;with CountedRows as (
		select
			ProductId Id,
			row_number() over (order by ProductID) Ordinal
		from Product )
	update Product
	set Name = Name
	from CountedRows
		inner join Product on CountedRows.Id = Product.ProductID
	where CountedRows.Ordinal between @localeUpdateIndex and (@localeUpdateIndex + @localeUpdateChunkSize - 1)

	set @localeUpdateIndex = @localeUpdateIndex + @localeUpdateChunkSize
end

select @localeUpdateCount = count(*), @localeUpdateIndex = 1 from ProductVariant
while @localeUpdateIndex  < @localeUpdateCount
begin
	print '    Updating Variants ' + convert(nvarchar(max), @localeUpdateIndex) + ' to ' + convert(nvarchar(max), @localeUpdateIndex + @localeUpdateChunkSize - 1) + ' of ' + convert(nvarchar(max), @localeUpdateCount)
	;with CountedRows as (
		select
			VariantID Id,
			row_number() over (order by VariantID) Ordinal
		from ProductVariant )
	update ProductVariant
	set Name = Name
	from CountedRows
		inner join ProductVariant on CountedRows.Id = ProductVariant.VariantID
	where CountedRows.Ordinal between @localeUpdateIndex and (@localeUpdateIndex + @localeUpdateChunkSize - 1)

	set @localeUpdateIndex = @localeUpdateIndex + @localeUpdateChunkSize
end

select @localeUpdateCount = count(*), @localeUpdateIndex = 1 from Category
while @localeUpdateIndex  < @localeUpdateCount
begin
	print '    Updating Categories ' + convert(nvarchar(max), @localeUpdateIndex) + ' to ' + convert(nvarchar(max), @localeUpdateIndex + @localeUpdateChunkSize - 1) + ' of ' + convert(nvarchar(max), @localeUpdateCount)
	;with CountedRows as (
		select
			CategoryId Id,
			row_number() over (order by CategoryID) Ordinal
		from Category )
	update Category
	set Name = Name
	from CountedRows
		inner join Category on CountedRows.Id = Category.CategoryID
	where CountedRows.Ordinal between @localeUpdateIndex and (@localeUpdateIndex + @localeUpdateChunkSize - 1)

	set @localeUpdateIndex = @localeUpdateIndex + @localeUpdateChunkSize
end

select @localeUpdateCount = count(*), @localeUpdateIndex = 1 from Manufacturer
while @localeUpdateIndex  < @localeUpdateCount
begin
	print '    Updating Manufacturers ' + convert(nvarchar(max), @localeUpdateIndex) + ' to ' + convert(nvarchar(max), @localeUpdateIndex + @localeUpdateChunkSize - 1) + ' of ' + convert(nvarchar(max), @localeUpdateCount)
	;with CountedRows as (
		select
			ManufacturerId Id,
			row_number() over (order by ManufacturerID) Ordinal
		from Manufacturer )
	update Manufacturer
	set Name = Name
	from CountedRows
		inner join Manufacturer on CountedRows.Id = Manufacturer.ManufacturerID
	where CountedRows.Ordinal between @localeUpdateIndex and (@localeUpdateIndex + @localeUpdateChunkSize - 1)

	set @localeUpdateIndex = @localeUpdateIndex + @localeUpdateChunkSize
end

select @localeUpdateCount = count(*), @localeUpdateIndex = 1 from Distributor
while @localeUpdateIndex  < @localeUpdateCount
begin
	print '    Updating Distributors ' + convert(nvarchar(max), @localeUpdateIndex) + ' to ' + convert(nvarchar(max), @localeUpdateIndex + @localeUpdateChunkSize - 1) + ' of ' + convert(nvarchar(max), @localeUpdateCount)
	;with CountedRows as (
		select
			DistributorId Id,
			row_number() over (order by DistributorID) Ordinal
		from Distributor )
	update Distributor
	set Name = Name
	from CountedRows
		inner join Distributor on CountedRows.Id = Distributor.DistributorID
	where CountedRows.Ordinal between @localeUpdateIndex and (@localeUpdateIndex + @localeUpdateChunkSize - 1)

	set @localeUpdateIndex = @localeUpdateIndex + @localeUpdateChunkSize
end

select @localeUpdateCount = count(*), @localeUpdateIndex = 1 from Section
while @localeUpdateIndex  < @localeUpdateCount
begin
	print '    Updating Sections ' + convert(nvarchar(max), @localeUpdateIndex) + ' to ' + convert(nvarchar(max), @localeUpdateIndex + @localeUpdateChunkSize - 1) + ' of ' + convert(nvarchar(max), @localeUpdateCount)
	;with CountedRows as (
		select
			SectionId Id,
			row_number() over (order by SectionID) Ordinal
		from Section )
	update Section
	set Name = Name
	from CountedRows
		inner join Section on CountedRows.Id = Section.SectionID
	where CountedRows.Ordinal between @localeUpdateIndex and (@localeUpdateIndex + @localeUpdateChunkSize - 1)

	set @localeUpdateIndex = @localeUpdateIndex + @localeUpdateChunkSize
end

select @localeUpdateCount = count(*), @localeUpdateIndex = 1 from Genre
while @localeUpdateIndex  < @localeUpdateCount
begin
	print '    Updating Genres ' + convert(nvarchar(max), @localeUpdateIndex) + ' to ' + convert(nvarchar(max), @localeUpdateIndex + @localeUpdateChunkSize - 1) + ' of ' + convert(nvarchar(max), @localeUpdateCount)
	;with CountedRows as (
		select
			GenreId Id,
			row_number() over (order by GenreID) Ordinal
		from Genre )
	update Genre
	set Name = Name
	from CountedRows
		inner join Genre on CountedRows.Id = Genre.GenreID
	where CountedRows.Ordinal between @localeUpdateIndex and (@localeUpdateIndex + @localeUpdateChunkSize - 1)

	set @localeUpdateIndex = @localeUpdateIndex + @localeUpdateChunkSize
end

select @localeUpdateCount = count(*), @localeUpdateIndex = 1 from Vector
while @localeUpdateIndex  < @localeUpdateCount
begin
	print '    Updating Vectors ' + convert(nvarchar(max), @localeUpdateIndex) + ' to ' + convert(nvarchar(max), @localeUpdateIndex + @localeUpdateChunkSize - 1) + ' of ' + convert(nvarchar(max), @localeUpdateCount)
	;with CountedRows as (
		select
			VectorId Id,
			row_number() over (order by VectorID) Ordinal
		from Vector )
	update Vector
	set Name = Name
	from CountedRows
		inner join Vector on CountedRows.Id = Vector.VectorID
	where CountedRows.Ordinal between @localeUpdateIndex and (@localeUpdateIndex + @localeUpdateChunkSize - 1)

	set @localeUpdateIndex = @localeUpdateIndex + @localeUpdateChunkSize
end

print 'LocalizedObjectName creation and population complete'
set nocount off

go