从数据表中插入许多Anto编号到DataBase [英] Insert with many Anto Number from Data Table to DataBase
问题描述
我有3个数据表来更新数据库
发票表主键是InvoiceNo
InvoiceProduct表主键是InvoiceProductNo和外键InvoiceNo
InvoiceProductExp表主键是InvoiceProductExpNo,外国kyes是InvoiceNo和InvoiceProductNo
3数据表数据输入例如是
发票(InvoiceNo,...)
(0001,...)
InvoiceProduct(InvoiceProductNo,InvoiceNo,...)
(1,0001,...)
(2,0001,...)
InvoiceProductExp(InvoiceProductExpNo,InvoiceProductNo,InvoiceNo,...)
(1,1,0001,...)
(2,1,0001,...)
(3,2,0001,...)
(4,2,0001,...)
问题是我喜欢使用sql server生成的AutoNumber或所有prim的Identity列3个桌子的ary键
如何准备插入语句
插入InvoiceProductExp值(auto_number ,?,?)
将第一行插入发票表格,如
CREATE PROCEDURE PROCEDURENAME
@ Parameter1 dataType,
@ Parameter2 dataType,
@ Parameter3 dataType,
@ InvoiceNo BigInt ,
@LastId BigInt = 0 输出
AS
BEGIN
INSERT INTO 发票(Field1,Field2 ...)值( @ Parameter1 , @ Parameter2 ....)
SELECT @ InvoiceNo = MAX(InvoiceNo) FROM 发票
SET @ LastId = @ InvoiceNo
END
现在你将获取发票号为插入行..在@LastId你可以将它传递给第二个查询..
并在第二个sp中按照相同的方法获取第二个表的主键...
i have 3 data tables to update database
Invoice Table primary key is InvoiceNo
InvoiceProduct Table primary key is InvoiceProductNo and foreign key InvoiceNo
InvoiceProductExp Table primary key is InvoiceProductExpNo and foreign kyes are InvoiceNo and InvoiceProductNo
3 Data Tables Data entry would be for example is
Invoice (InvoiceNo,...)
(0001,...)
InvoiceProduct (InvoiceProductNo, InvoiceNo,...)
(1,0001,...)
(2,0001,...)
InvoiceProductExp (InvoiceProductExpNo,InvoiceProductNo,InvoiceNo,...)
(1,1,0001,...)
(2,1,0001,...)
(3,2,0001,...)
(4,2,0001,...)
the problem is i liked to use sql server generated AutoNumber or Identity column for all primary keys of 3 tables
how can i prepare for insert statements
Insert Into InvoiceProductExp values (auto_number, ?, ?)
insert first row to Invoice table like
CREATE PROCEDURE PROCEDURENAME @Parameter1 dataType, @Parameter2 dataType, @Parameter3 dataType, @InvoiceNo BigInt, @LastId BigInt=0 OUTPUT AS BEGIN INSERT INTO Invoice(Field1,Field2...)Values(@Parameter1,@Parameter2....) SELECT @InvoiceNo= MAX(InvoiceNo) FROM Invoice SET @LastId=@InvoiceNo END
Now you will get invoice no for inserted row..in @LastId you can pass it to second query ..
and follow same method in second sp to get the primary key of second table...
这篇关于从数据表中插入许多Anto编号到DataBase的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!