1. importData
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[importData](
@table_type int
)
as
if @table_type = 1
begin
insert into gwkproduct_one
SELECT [id]
,[merchantId]
,[ProductName]
,[refprice]
,[price_1]
,[PinPai]
,[picurl]
,[shortintro]
,[SearchKeyword]
,[BrowseNodeKeyword]
,[url]
,getDate() from gwkproduct g
where [BrowseNodeKeyword] != '' and [BrowseNodeKeyword] is not null
and [shortintro] != '' and [shortintro] is not null
and g.flag=1
and [merchantId] in (select m.merchId from merchant m
where
m.status = 1)
--Print @@ERROR
end
else
begin
insert into gwkproduct_two
SELECT [id]
,[merchantId]
,[ProductName]
,[refprice]
,[price_1]
,[PinPai]
,[picurl]
,[shortintro]
,[SearchKeyword]
,[BrowseNodeKeyword]
,[url]
,getDate() from gwkproduct g
where [BrowseNodeKeyword] != '' and [BrowseNodeKeyword] is not null
and [shortintro] != '' and [shortintro] is not null
and g.flag=1
and [merchantId] in (select m.merchId from merchant m
where
m.status = 1)
--Print @@ERROR
end
2. switchAndImport
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[switchAndImport]
as
DECLARE
@v_workTableName nvarchar(50),
@v_startDateTime datetime,
@v_workTableName_new nvarchar(50)
DECLARE cur_gwkproduct_switch Cursor
For select workTableName,startDateTime
from gwkproduct_switch --for Update of workTableName,startDateTime
open cur_gwkproduct_switch
fetch next from cur_gwkproduct_switch into @v_workTableName,@v_startDateTime
--release cursor start
close cur_gwkproduct_switch
deallocate cur_gwkproduct_switch
--release cursor end
if @v_workTableName != 'gwkproduct_one'
begin
-------------imort data to gwkproduct_one start
set @v_workTableName_new='gwkproduct_one'
print @v_workTableName_new
--------
truncate table gwkproduct_one
exec dropindexproduct 1
exec importData 1
exec createindexproduct 1
---------fulltext rebuild
exec sp_fulltext_catalog 'gouwukeone', 'rebuild'
----create fulltext start
exec sp_fulltext_catalog 'gouwukeone', 'start_full'
While fulltextcatalogproperty( 'gouwukeone', 'populateStatus') <> 0
begin
waitfor delay '0:2:30'
end
----create fulltext end
-------------imort data to gwkproduct_one end
end
else
begin
-------------imort data to gwkproduct_two start
set @v_workTableName_new = 'gwkproduct_two'
print @v_workTableName_new
--------
truncate table gwkproduct_two
exec dropindexproduct 2
exec importData 2
exec createindexproduct 2
---------fulltext rebuild
exec sp_fulltext_catalog 'gouwuketwo', 'rebuild'
----create fulltext start
exec sp_fulltext_catalog 'gouwuketwo', 'start_full'
While fulltextcatalogproperty( 'gouwuketwo', 'populateStatus') <> 0
begin
waitfor delay '0:2:30'
end
----create fulltext end
-------------imort data to gwkproduct_two end
end
----------------------
update gwkproduct_switch
set workTableName =@v_workTableName_new ,startDateTime = DateAdd(minute,24*60+10,getDate())
where workTableName = @v_workTableName
3.dropindexproduct
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[dropindexproduct](
@table_type int
)
as
if @table_type = 1
begin
drop INDEX [IX_gwkproduct_id_BNK_Pinpai] ON [dbo].[gwkproduct_one]
drop INDEX [IX_gwkproduct_id_price_1] ON [dbo].[gwkproduct_one]
drop INDEX [IX_ID_BKN_price1] ON [dbo].[gwkproduct_one]
drop INDEX [IX_ID_BNK_merchantId] ON [dbo].[gwkproduct_one]
drop INDEX [IX_ID_BNK_merchantId_pinpai] ON [dbo].[gwkproduct_one]
drop INDEX [IX_ID_BNK_merchantID_price] ON [dbo].[gwkproduct_one]
drop INDEX [IX_ID_BNK_PinPai_mchId_price1] ON [dbo].[gwkproduct_one]
drop INDEX [IX_ID_PinPai_BNK_merchantId] ON [dbo].[gwkproduct_one]
drop INDEX [IX_ID_Pinpai_BNK_price1] ON [dbo].[gwkproduct_one]
drop INDEX [IX_ID_price1_merchantID_BNK_pinpai] ON [dbo].[gwkproduct_one]
end
else
begin
drop INDEX [IX_gwkproduct_id_BNK_Pinpai] ON [dbo].[gwkproduct_two]
drop INDEX [IX_gwkproduct_id_price_1] ON [dbo].[gwkproduct_two]
drop INDEX [IX_ID_BKN_price1] ON [dbo].[gwkproduct_two]
drop INDEX [IX_ID_BNK_merchantId] ON [dbo].[gwkproduct_two]
drop INDEX [IX_ID_BNK_merchantId_pinpai] ON [dbo].[gwkproduct_two]
drop INDEX [IX_ID_BNK_merchantID_price] ON [dbo].[gwkproduct_two]
drop INDEX [IX_ID_BNK_PinPai_mchId_price1] ON [dbo].[gwkproduct_two]
drop INDEX [IX_ID_PinPai_BNK_merchantId] ON [dbo].[gwkproduct_two]
drop INDEX [IX_ID_Pinpai_BNK_price1] ON [dbo].[gwkproduct_two]
drop INDEX [IX_ID_price1_merchantID_BNK_pinpai] ON [dbo].[gwkproduct_two]
end
4.createindexproduct
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[createindexproduct](
@table_type int
)
as
if @table_type = 1
begin
---create gwkproduct_one index start
CREATE UNIQUE NONCLUSTERED INDEX [IX_gwkproduct_id_BNK_Pinpai] ON [dbo].[gwkproduct_one]
(
[id] ASC,
[BrowseNodeKeyword] ASC,
[PinPai] ASC
)
CREATE UNIQUE NONCLUSTERED INDEX [IX_gwkproduct_id_price_1] ON [dbo].[gwkproduct_one]
(
[id] ASC,
[price_1] ASC
)
CREATE UNIQUE NONCLUSTERED INDEX [IX_ID_BKN_price1] ON [dbo].[gwkproduct_one]
(
[id] ASC,
[BrowseNodeKeyword] ASC,
[price_1] ASC
)
CREATE UNIQUE NONCLUSTERED INDEX [IX_ID_BNK_merchantId] ON [dbo].[gwkproduct_one]
(
[id] ASC,
[BrowseNodeKeyword] ASC,
[merchantId] ASC
)
CREATE UNIQUE NONCLUSTERED INDEX [IX_ID_BNK_merchantId_pinpai] ON [dbo].[gwkproduct_one]
(
[id] ASC,
[BrowseNodeKeyword] ASC,
[merchantId] ASC,
[PinPai] ASC
)
CREATE UNIQUE NONCLUSTERED INDEX [IX_ID_BNK_merchantID_price] ON [dbo].[gwkproduct_one]
(
[id] ASC,
[BrowseNodeKeyword] ASC,
[merchantId] ASC,
[price_1] ASC
)
CREATE UNIQUE NONCLUSTERED INDEX [IX_ID_BNK_PinPai_mchId_price1] ON [dbo].[gwkproduct_one]
(
[id] ASC,
[BrowseNodeKeyword] ASC,
[PinPai] ASC,
[merchantId] ASC,
[price_1] ASC
)
CREATE UNIQUE NONCLUSTERED INDEX [IX_ID_PinPai_BNK_merchantId] ON [dbo].[gwkproduct_one]
(
[id] ASC,
[PinPai] ASC,
[BrowseNodeKeyword] ASC,
[merchantId] ASC
)
CREATE UNIQUE NONCLUSTERED INDEX [IX_ID_Pinpai_BNK_price1] ON [dbo].[gwkproduct_one]
(
[id] ASC,
[PinPai] ASC,
[BrowseNodeKeyword] ASC,
[price_1] ASC
)
CREATE UNIQUE NONCLUSTERED INDEX [IX_ID_price1_merchantID_BNK_pinpai] ON [dbo].[gwkproduct_one]
(
[id] ASC,
[price_1] ASC,
[merchantId] ASC,
[BrowseNodeKeyword] ASC,
[PinPai] ASC
)
---create gwkproduct_one index end
end
else
begin
---create gwkproduct_two index start
CREATE UNIQUE NONCLUSTERED INDEX [IX_gwkproduct_id_BNK_Pinpai] ON [dbo].[gwkproduct_two]
(
[id] ASC,
[BrowseNodeKeyword] ASC,
[PinPai] ASC
)
CREATE UNIQUE NONCLUSTERED INDEX [IX_gwkproduct_id_price_1] ON [dbo].[gwkproduct_two]
(
[id] ASC,
[price_1] ASC
)
CREATE UNIQUE NONCLUSTERED INDEX [IX_ID_BKN_price1] ON [dbo].[gwkproduct_two]
(
[id] ASC,
[BrowseNodeKeyword] ASC,
[price_1] ASC
)
CREATE UNIQUE NONCLUSTERED INDEX [IX_ID_BNK_merchantId] ON [dbo].[gwkproduct_two]
(
[id] ASC,
[BrowseNodeKeyword] ASC,
[merchantId] ASC
)
CREATE UNIQUE NONCLUSTERED INDEX [IX_ID_BNK_merchantId_pinpai] ON [dbo].[gwkproduct_two]
(
[id] ASC,
[BrowseNodeKeyword] ASC,
[merchantId] ASC,
[PinPai] ASC
)
CREATE UNIQUE NONCLUSTERED INDEX [IX_ID_BNK_merchantID_price] ON [dbo].[gwkproduct_two]
(
[id] ASC,
[BrowseNodeKeyword] ASC,
[merchantId] ASC,
[price_1] ASC
)
CREATE UNIQUE NONCLUSTERED INDEX [IX_ID_BNK_PinPai_mchId_price1] ON [dbo].[gwkproduct_two]
(
[id] ASC,
[BrowseNodeKeyword] ASC,
[PinPai] ASC,
[merchantId] ASC,
[price_1] ASC
)
CREATE UNIQUE NONCLUSTERED INDEX [IX_ID_PinPai_BNK_merchantId] ON [dbo].[gwkproduct_two]
(
[id] ASC,
[PinPai] ASC,
[BrowseNodeKeyword] ASC,
[merchantId] ASC
)
CREATE UNIQUE NONCLUSTERED INDEX [IX_ID_Pinpai_BNK_price1] ON [dbo].[gwkproduct_two]
(
[id] ASC,
[PinPai] ASC,
[BrowseNodeKeyword] ASC,
[price_1] ASC
)
CREATE UNIQUE NONCLUSTERED INDEX [IX_ID_price1_merchantID_BNK_pinpai] ON [dbo].[gwkproduct_two]
(
[id] ASC,
[price_1] ASC,
[merchantId] ASC,
[BrowseNodeKeyword] ASC,
[PinPai] ASC
)
---create gwkproduct_two index end
end
