如何在没有临时表的情况下插入一个SP中的多个表
表格:
TAccount
AccountNo
名称
地址
TAccount表中的所有数据都将生成/插入计费表中
TBilling
BillingNo -on insert auto increment number
AccountNo -on insert from TAccount table
名称 -on insert from TAccount table
地址 -on插入来自TAccount表
描述 - 插入来自@paramDescription
DueDate -on insert from @paramDueDate
同时也会向TBillingDetails表插入数据
TBillingDetails
否 -on insert auto increment number
StatementNo -on insert get来自TBilling表(来自generated / auto i ncrement)
详情 -on insert'RPT'
金额 -on insert 50.00
TBillingDetails表每个账单有多个详细信息
先谢谢了! :)
Tables:
TAccount
AccountNo
Name
Address
all data in TAccount table will generate/insert in billing table
TBilling
BillingNo -on insert auto increment number
AccountNo -on insert get from TAccount table
Name -on insert get from TAccount table
Address -on insert get from TAccount table
Description -on insert get from @paramDescription
DueDate -on insert get from @paramDueDate
at the same time will also insert data to TBillingDetails table
TBillingDetails
No -on insert auto increment number
StatementNo -on insert get from TBilling table (from generated/auto increment)
Details -on insert 'RPT'
Amount -on insert 50.00
TBillingDetails table have multiple details per billing
Thanks in advanced! :)
编写一个存储过程,在每次插入后插入表顺序捕获SCOPE_IDENTITY()
。
Write a stored proc that inserts the tables sequentially trapping theSCOPE_IDENTITY()
after each insert.
Declare @AccountID int, @BillingNo int
Insert TAccount...
Set @AccountID = SCOPE_IDENTITY()
Insert TBilling
AccountNo = @AccountID
...