购物客:购物客的四个存储过程 2024-04-20 08:35:55 0 0 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 转载于:https://www.cnblogs.com/px7034/archive/2010/12/15/1906931.html 收藏(0)