Declare @Sale Table
(
ID Int,
BillNo Varchar(200),
Qty Decimal(18, 2)
)Declare @MaxNum Table
(
TableName varchar(20),
MaxNum Int
)Declare @OtherSale Table
(
BillNo Varchar(200),
Qty Decimal(18, 2)
)
Insert Into @MaxNum
Select 'Sale', 100Insert Into @OtherSale
Select 'A001', 12.01 Union
Select 'A002', 12.21 Union
Select 'B001', 10.99 Union
Select 'B002', 10.01
/*
Select * from @OtherSale
Select * from @Sale
Select * from @MaxNum
*/
Insert Into @Sale(ID, BillNo, Qty)
Select
IsNull((Select MaxNum + 1 from @MaxNum where TableName = 'Sale'), 1),
BIllNo,
Qty
from
@OtherSaleSelect * from @Sale/*
需要结果(即ID在原来的基础上自增)
ID BillNo Qty
101 A001 12.01
102 A002 12.21
103 B001 10.99
104 B002 10.01
*/
(
ID Int,
BillNo Varchar(200),
Qty Decimal(18, 2)
)Declare @MaxNum Table
(
TableName varchar(20),
MaxNum Int
)Declare @OtherSale Table
(
BillNo Varchar(200),
Qty Decimal(18, 2)
)
Insert Into @MaxNum
Select 'Sale', 100Insert Into @OtherSale
Select 'A001', 12.01 Union
Select 'A002', 12.21 Union
Select 'B001', 10.99 Union
Select 'B002', 10.01
/*
Select * from @OtherSale
Select * from @Sale
Select * from @MaxNum
*/
Insert Into @Sale(ID, BillNo, Qty)
Select
IsNull((Select MaxNum + 1 from @MaxNum where TableName = 'Sale'), 1),
BIllNo,
Qty
from
@OtherSaleSelect * from @Sale/*
需要结果(即ID在原来的基础上自增)
ID BillNo Qty
101 A001 12.01
102 A002 12.21
103 B001 10.99
104 B002 10.01
*/
ID=identity(int, 101, 1),
BIllNo,
Qty
into Sale2
from
@OtherSaleSelect * from Sale2
Declare @MaxNum Table
(
TableName varchar(20),
MaxNum Int
)Declare @OtherSale Table
(
BillNo Varchar(200),
Qty Decimal(18, 2)
)
Insert Into @MaxNum
Select 'Sale', 100Insert Into @OtherSale
Select 'A001', 12.01 Union
Select 'A002', 12.21 Union
Select 'B001', 10.99 Union
Select 'B002', 10.01 Select
ID=identity(int, 101, 1),
BIllNo,
Qty
into Sale
from
@OtherSaleSelect * from Sale--result
ID BIllNo Qty
----------- ------------
101 A001 12.01
102 A002 12.21
103 B001 10.99
104 B002 10.01(4 row(s) affected)
select BIllNo, Qty from @OtherSale
where ...
我现在需要
(
ID Int,
BillNo Varchar(200),
Qty Decimal(18, 2)
)create table MaxNum
(
TableName varchar(20),
MaxNum Int
)create table OtherSale
(
BillNo Varchar(200),
Qty Decimal(18, 2)
)
Insert Into MaxNum
Select 'Sale', 100Insert Into OtherSale
Select 'A001', 12.01 Union
Select 'A002', 12.21 Union
Select 'B001', 10.99 Union
Select 'B002', 10.01 select
ID=identity(int, 1, 1),
MaxNum=IsNull((Select MaxNum from MaxNum where TableName = 'Sale'), 1), * into #T
from OtherSaleInsert Into Sale(ID, BillNo, Qty)
Select ID+MaxNum as ID, BillNo, Qty from #Tdrop table #T, OtherSale, MaxNum, Sale