插入特殊行的连接表中 [英] insert into joined tables in special row
本文介绍了插入特殊行的连接表中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有4个表是joind
[dbo]。[PersonelInfo](
[pID] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[PersonelCode] [ varchar ]( 50 ) NOT NULL )
[dbo] 。[EmploymentInfo](
[empID] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[FK_pID] [ int ] NOT NULL )
ALTER 表 [dbo]。[EmploymentInfo] WITH CHECK ADD CONSTRAINT [FK_EmploymentInfo_PersonelInfo] FOREIGN KEY ([FK_pID])
REFERENCES [dbo]。[PersonelInfo]([ pID])
[dbo]。[ICardDate](
[FK_empID] [ int ] NOT NULL ,
[Imonth] [ int ] NOT NULL ,
[Iyear] [ bigint ] NOT NULL )
ALTER TABLE [dbo]。[ICardDat e] WITH CHECK ADD CONSTRAINT [FK_ICardDate_EmploymentInfo] FOREIGN KEY ([FK_empID])
REFERENCES [dbo]。[EmploymentInfo]([empID])
[dbo]。[PersonelCard](
[pcID] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[FK_empID] [ int ] NOT NULL ,
[FK_month] [ int ] NOT NULL ,
[FK_year] [大int ] NOT NULL ,
[VacationHours] [ time ]( 7 ) NULL )
ALTER TABLE [dbo]。[PersonelCard] WITH CHECK ADD CONSTRAINT [FK_PersonelCard_ICardDate] FOREIGN KEY ([FK_empID],[FK_month],[FK_year])
REFERENCES [dbo]。[ICardDate]([FK_empID],[Imonth],[Iyear])
GO
现在我想根据我的storedprocedure输入参数在[dbo]。[PersonelCard]中添加一行。
CREATE PROCEDURE [dbo]。[InsertRowInPersonelCard]
( @ personelcode varchar ( 50 ),
@ fk_month int ,
@ fk_year bigint ,
@ vacationhours 时间( 7 ))
< span class =code-keyword> as
- 问题:我不知道如何通过@personelcode找到相关的FK_empID
插入 进入 PersonelCard(FK_empID,FK_month, FK_year,VacationHours)
值( @ empID , @fk_month , @ fk_year , @ vacationhours )
- @ empID,@ fk_month,@ fk_year的相同值也应该在此表中插入
insert into ICardDate(FK_empID,Imonth,Iyear)
值( @ empID , @ fk_month , @ fk_year )
GO
解决方案
CREATE PROCEDURE [dbo]。[ InsertRowInPersonelCard]
(@personelcode varchar(50),
@fk_month int,
@fk_year bigint,
@vacationhours时间(7))
as
DECALRE @empID INT
SELECT @ empID = empID FROM EmploymentInfo WHERE FK_pID =(从PersonelInfo中选择pID WHERE FK_pID = @ p ersonelcode)
插入PersonelCard(FK_empID,FK_month,FK_year,VacationHours)
值(@ empID,@ fk_month,@ fk_year,@ Vacationhours)
插入ICardDate(FK_empID,Imonth,Iyear)
值(@ empID,@ fk_month,@ fk_year)
I have 4 tables are joind
[dbo].[PersonelInfo](
[pID] [int] IDENTITY(1,1) NOT NULL,
[PersonelCode] [varchar](50) NOT NULL)
[dbo].[EmploymentInfo](
[empID] [int] IDENTITY(1,1) NOT NULL,
[FK_pID] [int] NOT NULL)
ALTER TABLE [dbo].[EmploymentInfo] WITH CHECK ADD CONSTRAINT [FK_EmploymentInfo_PersonelInfo] FOREIGN KEY([FK_pID])
REFERENCES [dbo].[PersonelInfo] ([pID])
[dbo].[ICardDate](
[FK_empID] [int] NOT NULL,
[Imonth] [int] NOT NULL,
[Iyear] [bigint] NOT NULL)
ALTER TABLE [dbo].[ICardDate] WITH CHECK ADD CONSTRAINT [FK_ICardDate_EmploymentInfo] FOREIGN KEY([FK_empID])
REFERENCES [dbo].[EmploymentInfo] ([empID])
[dbo].[PersonelCard](
[pcID] [int] IDENTITY(1,1) NOT NULL,
[FK_empID] [int] NOT NULL,
[FK_month] [int] NOT NULL,
[FK_year] [bigint] NOT NULL,
[VacationHours] [time](7) NULL)
ALTER TABLE [dbo].[PersonelCard] WITH CHECK ADD CONSTRAINT [FK_PersonelCard_ICardDate] FOREIGN KEY([FK_empID], [FK_month], [FK_year])
REFERENCES [dbo].[ICardDate] ([FK_empID], [Imonth], [Iyear])
GO
now I want add a row to [dbo].[PersonelCard] base on my storedprocedure input parameters.
CREATE PROCEDURE [dbo].[InsertRowInPersonelCard]
(@personelcode varchar (50),
@fk_month int,
@fk_year bigint,
@vacationhours time(7))
as
--question:I dont know how find related FK_empID through @personelcode
insert into PersonelCard(FK_empID,FK_month,FK_year,VacationHours)
Values(@empID,@fk_month,@fk_year,@vacationhours)
--the same value of @empID,@fk_month,@fk_year should insert in this table too
insert into ICardDate(FK_empID,Imonth,Iyear)
Values(@empID,@fk_month,@fk_year)
GO
解决方案
CREATE PROCEDURE [dbo].[InsertRowInPersonelCard]
(@personelcode varchar (50),
@fk_month int,
@fk_year bigint,
@vacationhours time(7))
as
DECALRE @empID INT
SELECT @empID=empID FROM EmploymentInfo WHERE FK_pID =(SELECT pID FROM PersonelInfo WHERE FK_pID=@personelcode)
insert into PersonelCard(FK_empID,FK_month,FK_year,VacationHours)
Values(@empID,@fk_month,@fk_year,@vacationhours)
insert into ICardDate(FK_empID,Imonth,Iyear)
Values(@empID,@fk_month,@fk_year)
这篇关于插入特殊行的连接表中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文