首页 新闻 搜索 专区 学院

SQl存储过程插入数据表

0
悬赏园豆:100 [已解决问题] 解决于 2012-06-04 16:20

我要从一个视图里面读出数据,插入到2张表里面,要先读出部分列插入到第一个表 返回第一个表的主键,再把剩下的列和返回的主键插入第二个表里面,存储过程要怎么写?会的帮忙写个简单的例子,谢谢。

326742的主页 326742 | 初学一级 | 园豆:10
提问于:2012-06-04 09:20
< >
分享
最佳答案
0

USE [SMP_Bak]
GO

/****** Object:  StoredProcedure [dbo].[Park_Oder]    Script Date: 06/04/2012 16:09:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Park_Oder]
 AS
  --订单表:ORD_ORDER
  declare @OrderCode nvarchar(64)
  declare @OrderName nvarchar(50)
  declare @OrderType varchar(4)
  declare @OrdererGuid uniqueidentifier
  declare @OrdererCorpID int
  declare @OrderUsedCorpID int
  declare @LastOrderID bigint
  declare @Status int
  declare @OrderDescription nvarchar(max)
  declare @ContactZipCode nvarchar(6)
  declare @OrderTime datetime
  declare @CancelTime datetime
  declare @Remark nvarchar(max)
  declare @Property1 nvarchar(512)
  declare @PayType tinyint
  declare @MarketFee decimal(18, 2)
  declare @BehooveFee decimal(18, 2)
  declare @PreferentialFee decimal(18, 2)
  declare @VARPrice decimal(18, 2)
  declare @OrderStatus tinyint
  declare @OrderKind tinyint
  declare @PayStatus tinyint
  declare @CashType tinyint
  declare @PaymentGateWay tinyint
  declare @OrderOwnerID int
  declare @ParkId nvarchar(50)
  declare @ParkOrderId nvarchar(50)
  
  --订单明细表: ORD_Order_Detail_Final
  declare @SolutionID int
  declare @Final_Status tinyint
  declare @ProductID int
  declare @Final_OrderKind tinyint
  declare @StartTime datetime
  declare @EndTime datetime
  declare @AduitTime datetime
  declare @OpenTime datetime
  declare @ApplyTime datetime
  declare @LicenseNum int
  declare @RemainLicenseNum int
  declare @TimeLimit int
  declare @Fee decimal(18, 2)
  declare @Final_PreferentialFee decimal(18, 2)
  declare @Final_Remark nvarchar(max)
  declare @ActivityID int
  declare @ActivityPeriod int
  declare @FeeType tinyint
  declare @FeePeriod int
  declare @BillingCyclePeriod int
  declare @Final_BehooveFee decimal(18, 2)
  declare @OriginalOrderDetailID bigint
  declare @PreferentialFeerefundment decimal(18, 2)
  declare @BehooveFeerefundment decimal(18, 2)
  declare @PreferentialFeeIncome decimal(18, 2)
  declare @BehooveFeeIncome decimal(18, 2)
  declare @IsDelete bit
  declare @DeleteReason int
  declare @DeleteDetail nvarchar(max)
  declare @OpenStatus int
  
  declare @OrderID int --插入产品表后返回的主键
  declare @Id int --总表的主键,用来修改同步完成后的状态
 Declare mycursor cursor for select [Id]
   ,[OrderCode]
   ,[OrderName]
   ,[OrderType]
   ,[OrdererGuid]
   ,[OrdererCorpID]
   ,[OrderUsedCorpID]
   ,[LastOrderID]
      ,[Status]
      ,[OrderDescription]
      ,[ContactZipCode]
      ,[OrderTime]
      ,[CancelTime]
      ,[Remark]
      ,[Property1]
      ,[PayType]
      ,[MarketFee]
      ,[BehooveFee]
      ,[PreferentialFee]
      ,[VARPrice]
      ,[OrderStatus]
      ,[OrderKind]
      ,[PayStatus]
      ,[CashType]
      ,[PaymentGateWay]
      ,[OrderOwnerID]
      ,[ParkId]
      ,[ParkOrderId]
      ,[SolutionID]
      ,[Final_Status]
      ,[ProductID]
      ,[Final_OrderKind]
      ,[StartTime]
      ,[EndTime]
      ,[AduitTime]
      ,[OpenTime]
      ,[ApplyTime]
      ,[LicenseNum]
      ,[RemainLicenseNum]
      ,[TimeLimit]
      ,[Fee]
      ,[Final_PreferentialFee]
      ,[Final_Remark]
      ,[ActivityID]
      ,[ActivityPeriod]
      ,[FeeType]
      ,[FeePeriod]
      ,[BillingCyclePeriod]
      ,[Final_BehooveFee]
      ,[OriginalOrderDetailID]
      ,[PreferentialFeerefundment]
      ,[BehooveFeerefundment]
      ,[PreferentialFeeIncome]
      ,[BehooveFeeIncome]
      ,[IsDelete]
      ,[DeleteReason]
      ,[DeleteDetail]
      ,[OpenStatus]
      ,[Type]
  FROM [SMP_Bak].[dbo].[Price_Order] where [Type]!=1
OPEN mycursor
Fetch next from mycursor into @Id
   ,@OrderCode
   ,@OrderName
   ,@OrderType
   ,@OrdererGuid
   ,@OrdererCorpID
   ,@OrderUsedCorpID
   ,@LastOrderID
      ,@Status
      ,@OrderDescription
      ,@ContactZipCode
      ,@OrderTime
      ,@CancelTime
      ,@Remark
      ,@Property1
      ,@PayType
      ,@MarketFee
      ,@BehooveFee
      ,@PreferentialFee
      ,@VARPrice
      ,@OrderStatus
      ,@OrderKind
      ,@PayStatus
      ,@CashType
      ,@PaymentGateWay
      ,@OrderOwnerID
      ,@ParkId
      ,@ParkOrderId
      ,@SolutionID
      ,@Final_Status
      ,@ProductID
      ,@Final_OrderKind
      ,@StartTime
      ,@EndTime
      ,@AduitTime
      ,@OpenTime
      ,@ApplyTime
      ,@LicenseNum
      ,@RemainLicenseNum
      ,@TimeLimit
      ,@Fee
      ,@Final_PreferentialFee
      ,@Final_Remark
      ,@ActivityID
      ,@ActivityPeriod
      ,@FeeType
      ,@FeePeriod
      ,@BillingCyclePeriod
      ,@Final_BehooveFee
      ,@OriginalOrderDetailID
      ,@PreferentialFeerefundment
      ,@BehooveFeerefundment
      ,@PreferentialFeeIncome
      ,@BehooveFeeIncome
      ,@IsDelete
      ,@DeleteReason
      ,@DeleteDetail
      ,@OpenStatus
while(@@fetch_status = 0)
begin
 insert into SMP_Bak.dbo.ORD_Order([OrderCode]
   ,[OrderName]
   ,[OrderType]
   ,[OrdererGuid]
   ,[OrdererCorpID]
   ,[OrderUsedCorpID]
   ,[LastOrderID]
      ,[Status]
      ,[OrderDescription]
      ,[ContactZipCode]
      ,[OrderTime]
      ,[CancelTime]
      ,[Remark]
      ,[Property1]
      ,[PayType]
      ,[MarketFee]
      ,[BehooveFee]
      ,[PreferentialFee]
      ,[VARPrice]
      ,[OrderStatus]
      ,[OrderKind]
      ,[PayStatus]
      ,[CashType]
      ,[PaymentGateWay]
      ,[OrderOwnerID]
      ,[ParkId]
      ,[ParkOrderId]) values(@OrderCode,@OrderName,@OrderType,@OrdererGuid,@OrdererCorpID,@OrderUsedCorpID
   ,@LastOrderID
      ,@Status
      ,@OrderDescription
      ,@ContactZipCode
      ,@OrderTime
      ,@CancelTime
      ,@Remark
      ,@Property1
      ,@PayType
      ,@MarketFee
      ,@BehooveFee
      ,@PreferentialFee
      ,@VARPrice
      ,@OrderStatus
      ,@OrderKind
      ,@PayStatus
      ,@CashType
      ,@PaymentGateWay
      ,@OrderOwnerID
      ,@ParkId
      ,@ParkOrderId)
 SELECT  
  @OrderID=@@identity
 select @SolutionID=(select top 1 SolutionID from ISV_Solution where ParkId=@ParkId and ParkSolutionId=@SolutionID)
 insert into SMP_Bak.dbo.ORD_Order_Detail_Final([OrderID]
   ,[SolutionID]
      ,[Status]
      ,[ProductID]
      ,[OrderKind]
      ,[StartTime]
      ,[EndTime]
      ,[AduitTime]
      ,[OpenTime]
      ,[ApplyTime]
      ,[LicenseNum]
      ,[RemainLicenseNum]
      ,[TimeLimit]
      ,[Fee]
      ,[PreferentialFee]
      ,[Remark]
      ,[ActivityID]
      ,[ActivityPeriod]
      ,[FeeType]
      ,[FeePeriod]
      ,[BillingCyclePeriod]
      ,[BehooveFee]
      ,[OriginalOrderDetailID]
      ,[PreferentialFeerefundment]
      ,[BehooveFeerefundment]
      ,[PreferentialFeeIncome]
      ,[BehooveFeeIncome]
      ,[IsDelete]
      ,[DeleteReason]
      ,[DeleteDetail]
      ,[OpenStatus])
 values(@OrderID
   ,@SolutionID
      ,@Final_Status
      ,@ProductID
      ,@Final_OrderKind
      ,@StartTime
      ,@EndTime
      ,@AduitTime
      ,@OpenTime
      ,@ApplyTime
      ,@LicenseNum
      ,@RemainLicenseNum
      ,@TimeLimit
      ,@Fee
      ,@Final_PreferentialFee
      ,@Final_Remark
      ,@ActivityID
      ,@ActivityPeriod
      ,@FeeType
      ,@FeePeriod
      ,@BillingCyclePeriod
      ,@Final_BehooveFee
      ,@OriginalOrderDetailID
      ,@PreferentialFeerefundment
      ,@BehooveFeerefundment
      ,@PreferentialFeeIncome
      ,@BehooveFeeIncome
      ,@IsDelete
      ,@DeleteReason
      ,@DeleteDetail
      ,@OpenStatus)
 update [SMP_Bak].[dbo].[Price_Order] set [Type]=1 where Id=@Id
 Fetch next from mycursor into @Id
   ,@OrderCode
   ,@OrderName
   ,@OrderType
   ,@OrdererGuid
   ,@OrdererCorpID
   ,@OrderUsedCorpID
   ,@LastOrderID
      ,@Status
      ,@OrderDescription
      ,@ContactZipCode
      ,@OrderTime
      ,@CancelTime
      ,@Remark
      ,@Property1
      ,@PayType
      ,@MarketFee
      ,@BehooveFee
      ,@PreferentialFee
      ,@VARPrice
      ,@OrderStatus
      ,@OrderKind
      ,@PayStatus
      ,@CashType
      ,@PaymentGateWay
      ,@OrderOwnerID
      ,@ParkId
      ,@ParkOrderId
      ,@SolutionID
      ,@Final_Status
      ,@ProductID
      ,@Final_OrderKind
      ,@StartTime
      ,@EndTime
      ,@AduitTime
      ,@OpenTime
      ,@ApplyTime
      ,@LicenseNum
      ,@RemainLicenseNum
      ,@TimeLimit
      ,@Fee
      ,@Final_PreferentialFee
      ,@Final_Remark
      ,@ActivityID
      ,@ActivityPeriod
      ,@FeeType
      ,@FeePeriod
      ,@BillingCyclePeriod
      ,@Final_BehooveFee
      ,@OriginalOrderDetailID
      ,@PreferentialFeerefundment
      ,@BehooveFeerefundment
      ,@PreferentialFeeIncome
      ,@BehooveFeeIncome
      ,@IsDelete
      ,@DeleteReason
      ,@DeleteDetail
      ,@OpenStatus
end
CLOSE mycursor   
DEALLOCATE mycursor

GO

326742 | 初学一级 |园豆:10 | 2012-06-04 16:15
其他回答(2)
0

先申明几个变量

在插入第一张表时保存主键到变量里

如果主键数目不确定或者太多

可以用分隔符方式保存到一个变量

然后第二张表时分割变量进行循环插入

收获园豆:50
ERS | 园豆:728 (小虾三级) | 2012-06-04 09:44
0

如果你的主键是自增型,可以使用IDENT_CURRENT、@@IDENTITY、SCOPE_IDENTITY来返回新纪录的主键,http://www.cnblogs.com/downmoon/archive/2012/04/12/2444344.html

然后再插入第二张表

收获园豆:50
邀月 | 园豆:25375 (高人七级) | 2012-06-04 10:40
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册