我有两个表Person和PersonLog,PersonId,Name,Address,Mobile,FK_PersonId。我正在尝试将旧数据存储在PersonLog和Person中以进行更新。
这是我的过程,但它只是更新Person,而不是将Person中选定(编辑)的数据存储到PersonLog:中
CREATE PROCEDURE [dbo].[UpdateInsertPerson]
(
@PersonId int,
@PersonName nvarchar(40),
@Address nvarchar(60),
@Mobile nvarchar(15)
)
AS
BEGIN
INSERT INTO
dbo.PersonLog(PersonName, Address, Mobile, FK_PersonId)
SELECT
Person.PersonId, Person.PersonName, Person.Address, Person.Mobile
FROM
dbo.Person JOIN dbo.PersonLog ON PersonLog.FK_PersonId = Person.PersonId;
UPDATE
dbo.Person
SET
PersonName = @PersonName,
Address = @Address,
Mobile = @Mobile
WHERE
PersonId = @PersonID;
END
有什么帮助吗?
我可能会建议进行这些更改。。。但我不知道你到底在追求什么。或者你的数据。
我强烈建议在您的PersonLog表中添加一个修改后的日期。。。。
alter table PersonLog add Modified_DT datetime default getdate()
这个代码对我有效…
CREATE PROCEDURE UpdateInsertPerson (
@PersonId int,
@PersonName nvarchar(40) = null,
@Address nvarchar(60) = null,
@Mobile nvarchar(15) = null
)
AS
BEGIN
INSERT INTO PersonLog (FK_PersonId, PersonName, Address, Mobile)
SELECT Person.PersonID,
Person.PersonName,
Person.Address,
Person.Mobile
FROM Person
WHERE Person.PersonId=@PersonID;
UPDATE Person
SET PersonName = case when @PersonName is not null then @PersonName else PersonName end,
Address = case when @Address is not null then @Address else Address end,
Mobile = case when @Mobile is not null then @Mobile else Mobile end
WHERE PersonId = @PersonID;
END
insert into Person values (1,'kim','123 main st','555-555-5555');
exec UpdateInsertPerson 1,'kim';
exec UpdateInsertPerson 1,'ryan';
exec UpdateInsertPerson 1,'taco';
select * from personlog
select * from Person
这样就不会插入一个全新的人。这样会。。。要使其工作,您的Person.PersonID必须设置为:PersonID int IDENTITY(1,1) primary key
。
ALTER PROCEDURE UpdateInsertPerson (
@PersonId int = null,
@PersonName nvarchar(40) = null,
@Address nvarchar(60) = null,
@Mobile nvarchar(15) = null
)
AS
BEGIN
INSERT INTO PersonLog (FK_PersonId, PersonName, Address, Mobile)
SELECT Person.PersonID,
Person.PersonName,
Person.Address,
Person.Mobile
FROM Person
WHERE Person.PersonId=@PersonID;
UPDATE Person
SET PersonName = case when @PersonName is not null then @PersonName else PersonName end,
Address = case when @Address is not null then @Address else Address end,
Mobile = case when @Mobile is not null then @Mobile else Mobile end
WHERE PersonId = @PersonID;
-- this inserts into Person if they didn't already exist
IF @@ROWCOUNT = 0
BEGIN
INSERT Person (PersonName, Address, Mobile) VALUES (@PersonName, @Address, @Mobile);
END
END
该过程不起作用,因为您正在将PersonLog与Person表连接到select语句中。由于最初该表为空,条件PersonLog.FK_PersonId=Person.PersonId为false,并且不会插入任何内容(因为FK_PersonId是NULL)我看不出将这两个表连接起来以在Log表中插入记录有任何目的。只需删除联接条件并包含一个where子句作为Person。PersonId=@PersonID