【请大家帮忙看看】SQL存储过程插入数据

[jy00251278]

Alter  PROCEDURE [dbo].[SRCInsertTable] (
	@StudentID nvarchar(30),
	@ProjectID nvarchar(30)	
)
AS

SET NOCOUNT ON

INSERT INTO [SRCTable] 
exec [dbo].[SRCSelectTable] @StudentID,@ProjectID

执行代码
exec [dbo].[SRCInsertTable] 'S0001','P0001'

另一个存储过程

Alter   PROCEDURE [dbo].[SRCSelectTable] (
	@StudentID nvarchar(30),
	@ProjectID nvarchar(30)	
)
AS

SET NOCOUNT ON

select 
	PT.StudentID,
	PS.ProjectID,
	S.SubjectID,
	SC.CoursePeriod
 from [OLTR_StudyProject] as P 
	inner join [ProjectRSubject] as PS on P.[ProjectID]=PS.[ProjectID]
	inner join [Subject] as S on PS.[SubjectID]=S.[SubjectID]
	inner join [SubjectRCourse] as SC on S.[SubjectID]=SC.[SubjectID]
	inner join [ProjectRStudent] as PT on P.[ProjectID]=PT.[ProjectID]

我要把存储过程SRCSelectTable里获取的内容全部插入SRCTable表里，用存储过程表示
我这样写应该怎么修改，或者应该怎么写
谢谢了。

[hyde100]

一个存储过程就可以了

CREATE  PROCEDURE [dbo].[SRCInsertTable] (
    @StudentID nvarchar(30),
    @ProjectID nvarchar(30)    
)
AS

SET NOCOUNT ON

INSERT INTO [SRCTable] 
select 
    PT.StudentID,
    PS.ProjectID,
    S.SubjectID,
    SC.CoursePeriod
 from [OLTR_StudyProject] as P 
    inner join [ProjectRSubject] as PS on P.[ProjectID]=PS.[ProjectID]
    inner join [Subject] as S on PS.[SubjectID]=S.[SubjectID]
    inner join [SubjectRCourse] as SC on S.[SubjectID]=SC.[SubjectID]
    inner join [ProjectRStudent] as PT on P.[ProjectID]=PT.[ProjectID]
GO

exec [dbo].[SRCInsertTable] 'S0001','P0001' 

[hyde100]

 select 的时候参数 @StudentID nvarchar(30), @ProjectID nvarchar(30) 怎么没有用到，少写了 where 了吧？

[dobear_0922]

--try
Alter   PROCEDURE [dbo].[SRCSelectTable] (
    @StudentID nvarchar(30),
    @ProjectID nvarchar(30)    
)
AS

SET NOCOUNT ON

select 
    PT.StudentID,
    PS.ProjectID,
    S.SubjectID,
    SC.CoursePeriod
 from [OLTR_StudyProject] as P 
    inner join [ProjectRSubject] as PS on P.[ProjectID]=PS.[ProjectID]
    inner join [Subject] as S on PS.[SubjectID]=S.[SubjectID]
    inner join [SubjectRCourse] as SC on S.[SubjectID]=SC.[SubjectID]
    inner join [ProjectRStudent] as PT on P.[ProjectID]=PT.[ProjectID]
where PS.[SubjectID]=@StudentID and PS.ProjectID=@ProjectID

[水族杰纶]

樓主的變量好像沒用到~~~

[dobear_0922]

像1楼那样改成一个存储过程也可以：

Alter   PROCEDURE [dbo].[SRCSelectTable] (
    @StudentID nvarchar(30),
    @ProjectID nvarchar(30)    
)
AS

SET NOCOUNT ON
INSERT INTO [SRCTable]
select PT.StudentID, PS.ProjectID, S.SubjectID, SC.CoursePeriod
 from [OLTR_StudyProject] as P 
    inner join [ProjectRSubject] as PS on P.[ProjectID]=PS.[ProjectID]
    inner join [Subject] as S on PS.[SubjectID]=S.[SubjectID]
    inner join [SubjectRCourse] as SC on S.[SubjectID]=SC.[SubjectID]
    inner join [ProjectRStudent] as PT on P.[ProjectID]=PT.[ProjectID]
where PS.[SubjectID]=@StudentID and PS.ProjectID=@ProjectID
GO

exec [dbo].[SRCInsertTable] 'S0001','P0001' 

[jy00251278]

系统提示：插入错误: 列名或所提供值的数目与表定义不匹配。

因为存储过程SRCSelectTable查询出的字段，只是SRCTable表的一部分

如果按照Insert into tables（）values（）的格式写，也会提示该错误

[rucypli]

INSERT INTO [SRCTable]
select
PT.StudentID,
PS.ProjectID,
S.SubjectID,
SC.CoursePeriod
from [OLTR_StudyProject] as P
inner join [ProjectRSubject] as PS on P.[ProjectID]=PS.[ProjectID]
inner join [Subject] as S on PS.[SubjectID]=S.[SubjectID]
inner join [SubjectRCourse] as SC on S.[SubjectID]=SC.[SubjectID]
inner join [ProjectRStudent] as PT on P.[ProjectID]=PT.[ProjectID]

[yzbsd]

INSERT INTO [SRCTable] (StudentID,ProjectID,SubjectID,CoursePeriod
) values
(exec [dbo].[SRCSelectTable] @StudentID,@ProjectID)

[apple02180503]

Alter   PROCEDURE [dbo].[SRCSelectTable] (
    @StudentID nvarchar(30),
    @ProjectID nvarchar(30)    
)
AS

SET NOCOUNT ON
INSERT INTO [SRCTable](StudentID,ProjectID,SubjectID,CoursePeriod)
select PT.StudentID, PS.ProjectID, S.SubjectID, SC.CoursePeriod
 from [OLTR_StudyProject] as P 
    inner join [ProjectRSubject] as PS on P.[ProjectID]=PS.[ProjectID]
    inner join [Subject] as S on PS.[SubjectID]=S.[SubjectID]
    inner join [SubjectRCourse] as SC on S.[SubjectID]=SC.[SubjectID]
    inner join [ProjectRStudent] as PT on P.[ProjectID]=PT.[ProjectID]
where PS.[SubjectID]=@StudentID and PS.ProjectID=@ProjectID
GO

exec [dbo].[SRCInsertTable] 'S0001','P0001'