34,590
社区成员
发帖
与我相关
我的任务
分享
alter proc InsertPro
--是插入数据库后的临时表
@tbName nvarchar(50)
as
begin
declare @sql varchar(max)
set @sql=''
set @sql='delete from '+@tbName+' where [店铺名称] is null and [标题] is null '
set @sql=@sql+' alter table '+ @tbName +' add 用户ID int '
set @sql=@sql+'alter table '+ @tbName +' add 产品类别ID int '
set @sql=@sql+'alter table '+ @tbName +' add 品牌ID int '
set @sql=@sql+'alter table '+ @tbName +' add 品牌英文 nvarchar(255) '
set @sql=@sql+'alter table '+ @tbName +' add 店铺产品类目ID nvarchar(255) '
set @sql=@sql+'update '+ @tbName+' set [店铺ID]=(select top 1 [ID] from T_Shop where [ShopName]=[店铺名称])'
set @sql=@sql+'update '+ @tbName+' set [用户ID]=(select top 1 [UserID] from T_Shop where ID=[店铺ID])'
set @sql=@sql+'delete from '+ @tbName+' where [店铺ID] is null or [用户ID] is null go '
set @sql=@sql+' INSERT INTO [T_ProBrand]
([Name]
,[EName]
,[SourcePhoto]
,[ThumbPhoto]
,[BigPhoto]
,[CategoryName]
,[FirstCatID]
,[SecondCatID]
,[ThirdCatID]
,[Url]
,[Keywords]
,[Descriptions]
,[OrderNum]
,[IsShow]
,[CheckState]
,[IsRecommend]
,[PID]
,[CreateTime])
select [品牌],'''','''','''','''','''',0,0,0,'''','''','''',49999,0,1,0,0,getdate() from
(
select distinct [品牌] from '+ @tbName+' where ([品牌] not in (select [Name] from T_ProBrand) or [品牌] not in (select [EName] from T_ProBrand))
) a'
set @sql=@sql+'update '+ @tbName+' set [品牌ID]=(select top 1 [ID] from T_ProBrand where ([Name]=[品牌] or [EName]=[品牌]))'
set @sql=@sql+'update '+ @tbName+' set [品牌英文]=(select top 1 [EName] from T_ProBrand where [ID]=[品牌ID])'
set @sql=@sql+'update '+ @tbName+' set [产品类别ID]=(select top 1 [ID] from T_ProCategory where ([Name]=[产品类别]))'
set @sql=@sql+'update '+ @tbName+' set [主图]=''http://img1.ymgkimg.com/UploadFiles/Product/img1025/''+[主图]'
set @sql=@sql+'update '+ @tbName+' set [主图]=[主图]+''.jpg'' where [主图] not like ''%.jpg'''
set @sql=@sql+'delete from '+ @tbName+' where [主图] is null go '
set @sql=@sql+'update '+ @tbName+' set [城市]=(select top 1 ID from T_Region where [Name] like ''%''+[城市]+''%'' and [Grade]=2)'
set @sql=@sql+'update '+ @tbName+' set [城市]=0 where [城市] is null go '
set @sql=@sql+'update '+ @tbName+' set [省份]=(select ParentID from T_Region where ID=[城市])'
set @sql=@sql+'update '+ @tbName+' set [省份]=0 where [省份] is null go '
set @sql=@sql+'update '+ @tbName+' set [店铺产品类目ID]=cast((select top 1 ID from [T_ShopProCategory] where [UserID]=[用户ID] and [Name]=[店铺产品类目]) as varchar(200))+'','''
set @sql=@sql+' Insert into T_Product ([UserID]
,[ShopID]
,[Title]
,[NOID]
,[NO]
,[CategoryID]
,[CategoryName]
,[FirstCatID]
,[FirstCatName]
,[SecondCatID]
,[SecondCatName]
,[ThirdCatID]
,[ThirdCatName]
,[TypeID]
,[TypeName]
,[BrandID]
,[BrandName]
,[BrandEName]
,[SpecificationsID]
,[SpecificationsName]
,[PropertyID]
,[PropertyName]
,[ProState]
,[StateTime]
,[StateRemark]
,[CommentStatus]
,[CommentTime]
,[CreateTime]
,[SignupTime]
,[EndTime]
,[EndDays]
,[Price]
,[DiscountPrice]
,[SourcePhoto]
,[ThumbPhoto]
,[BigPhoto]
,[Colors]
,[Counts]
,[Intro]
,[PayType]
,[Number]
,[Address]
,[SaleIntro]
,[StoreType]
,[GuaranteeTime]
,[GuaranteeUnit]
,[HasGuarantee]
,[HasBill]
,[ReturnAgreed]
,[IsNew]
,[NewPersent]
,[ReceiveType]
,[ReceiveDate]
,[Areas]
,[Pic1]
,[Pic2]
,[Pic3]
,[Pic4]
,[Pic5]
,[ShopCats]
,[PlateID]
,[PackType]
,[Province]
,[City]
,[FreightBy]
,[FirstFreight]
,[FirstEMS]
,[SecondFreight]
,[SecondEMS]
,[StockCountType]
,[RecShowcase]
,[ProStartType]
,[ProStartTime]
,[IsSale]
,[IsRecommend]
,[IsHot]
,[IsBuy]
,[IsCanSales]
,[Keywords]
,[Descriptions]
,[OrderNum]
,[SaleCount]
,[RefreshTime]
,[IsUpdate]
,[Freight]
,[IsDel]
,[DelTime]
,[IsBreak]
,[ErrorContent]
,[CheckState]
,[Operater]
,[OperateTime]
,[IsAdminRecommend]
) select [用户ID],[店铺ID],[标题],0
,[型号],0,'''',[产品类别ID]
,[产品类别],0,'''',0,'''',0,'''',[品牌ID]
,[品牌]
,[品牌英文],0,'''',0,'''',0,getdate(),'''',1,getdate(),getdate(),getdate(), DATEADD(day,30,getdate()),30
,[价格],0,'''','''','''',''''
,100
,[描述],1,'''','''',''''
,2
,1
,1
,2
,1,1
,1
,0
,1
,7
,1
,[主图],'''','''','''','''',[店铺产品类目ID],1
,1
,[省份]
,[城市]
,2
,0
,0
,0
,0
,1,0,1,getdate(),0,0,0,0,0,'''','''',0,0,getdate(),0,0,0,getdate(),1,'''',0,''导入员'',dateadd(ss, cast([用户ID] as int),getdate()),0 from '+@tbName
set @sql=@sql+' INSERT INTO [T_ProductPics]
([ProID]
,[UserID]
,[AlbumID]
,[CategoryID]
,[SourePhoto]
,[ThumbPhoto]
,[BigPhoto]
,[CreateTime]
,[ThumbPic1]
,[ThumbPic1_2]
,[ThumbPic1_3]
,[ThumbPic1_4]
,[ThumbPic1_5]
,[ThumbPic1_6]
,[ThumbPic1_7]
,[ThumbPic1_8]
,[ThumbPic1_9]
,[ThumbPic2]
,[ThumbPic3]
,[ThumbPic4]
,[ThumbPic5]
,[MidPic1]
,[MidPic2]
,[MidPic3]
,[MidPic4]
,[MidPic5]
,[BigPic1]
,[BigPic1_2]
,[BigPic2]
,[BigPic3]
,[BigPic4]
,[BigPic5])
select distinct ID,UserID,0,0,'''','''','''',getdate(),'''','''','''','''','''','''','''','''','''','''','''','''','''','''','''','''','''','''','''','''','''','''','''','''' from T_Product,'+ @tbName+' where [Operater]=''导入员'' and [Title]=[标题] and [UserID]=[用户ID] and [ShopID]=[店铺ID]'
exec(@sql)
end
alter proc InsertPro
--是插入数据库后的临时表
@tbName nvarchar(50)
as
begin
DECLARE @tbName nvarchar(50)
SET @tbName = -- 这里赋上表名