--改为 WHILE EXISTS( SELECT [ID] FROM Test ) BEGIN INSERT INTO Subscription_Access (TestID,LearnerID,Subscription_PriceID,StartDate,Status,IsAdminData) select Test.[ID],@@IDENTITY,@Subscription_PriceID,GETDATE(),'Valid',1 from Test where 条件语句 END
--楼主是不是想在通过test表向subscription_access表插入数据时,LearnerID自动编号? --测试 create table subscription_access(TestID int ,LearnerID int ,Subscription_PriceID int , StartDate datetime,Status varchar(10),IsAdminData int) create table test(testid int, Subscription_PriceID int) insert test select 1, 2 union all select 2, 4 union all select 5, 3 --主要部分 select testid, LearnerID=identity(int), Subscription_PriceID, getdate() as StartDate, 'Valid' as Status, 1 as IsAdminData into #tt from test insert subscription_access select * from #tt select* from subscription_access --去除 drop table #tt drop table subscription_access drop table test
INSERT INTO Subscription_Access (TestID,LearnerID,Subscription_PriceID,StartDate,Status,IsAdminData)select TestID,LearnerID,Subscription_PriceID,StartDate,Status,IsAdminData from Test where ? where 后为提取那5条记录的条件
WHILE EXISTS(
SELECT [ID] FROM Test
)
BEGIN
INSERT INTO Subscription_Access (TestID,LearnerID,Subscription_PriceID,StartDate,Status,IsAdminData)
select Test.[ID],@@IDENTITY,@Subscription_PriceID,GETDATE(),'Valid',1
from Test
where 条件语句
END
--测试
create table subscription_access(TestID int ,LearnerID int ,Subscription_PriceID int ,
StartDate datetime,Status varchar(10),IsAdminData int)
create table test(testid int, Subscription_PriceID int)
insert test select 1, 2
union all select 2, 4
union all select 5, 3
--主要部分
select testid, LearnerID=identity(int),
Subscription_PriceID, getdate() as StartDate,
'Valid' as Status, 1 as IsAdminData
into #tt
from test
insert subscription_access select * from #tt
select* from subscription_access
--去除
drop table #tt
drop table subscription_access
drop table test
where ?
where 后为提取那5条记录的条件