我在向一张表里插入数据时,怎样写才能让No字段顺序增加?
我下面写的这个加入的No都是一样的,如何修改?INSERT INTO order(
Line_No,
PONo,
CreateDate,
Res,
Comment,
PO_S_UnitPrice)
SELECT (select max(Line_No)+1 from order) AS Line_No,
FILE.PONo,
FILE.AcceptDate,
FILE.Res,
FILE.Comment,
FILE.PO_S_UnitPrice
FROM FILE
我下面写的这个加入的No都是一样的,如何修改?INSERT INTO order(
Line_No,
PONo,
CreateDate,
Res,
Comment,
PO_S_UnitPrice)
SELECT (select max(Line_No)+1 from order) AS Line_No,
FILE.PONo,
FILE.AcceptDate,
FILE.Res,
FILE.Comment,
FILE.PO_S_UnitPrice
FROM FILE
declare @i int
set @i=1
while @i<=100
begin
select @no =max(Line_No)+1 from order;
INSERT INTO order(
Line_No,
PONo,
CreateDate,
Res,
Comment,
PO_S_UnitPrice)
SELECT @no ,
FILE.PONo,
FILE.AcceptDate,
FILE.Res,
FILE.Comment,
FILE.PO_S_UnitPrice
FROM FILE
set @i = @i +1;
end
SELECT identity(int,1,1) as line_no
FILE.PONo,
FILE.AcceptDate,
FILE.Res,
FILE.Comment,
FILE.PO_S_UnitPrice
into #tmp FROM FILE INSERT INTO order(
Line_No,
PONo,
CreateDate,
Res,
Comment,
PO_S_UnitPrice)
SELECT (select isnull(max(Line_No),0) from order)+line_no AS Line_No,
PONo,
AcceptDate,
Res,
Comment,
PO_S_UnitPrice
from #tmp
drop table #tmp