Procedure to Insert Data from One Table to Another – SQL Server 2012

sql serversql-server-2012

I have two tables Person with PersonId, Name,Address,Mobile and PersonLog with Id,Name,Address,Mobile,FK_PersonId. I am trying store the old data in PersonLog and Person to be updated.

This is my procedure, but it is only updating Person and not storing selected(edited) data from Person into 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

Any help?

Best Answer

I think you are just missing the @PersonID in the INSERT statement and there is no need to join to PersonLog. In other words, you should do this:

INSERT INTO
dbo.PersonLog(PersonName, Address, Mobile, FK_PersonId)
SELECT
Person.PersonId, Person.PersonName, Person.Address, Person.Mobile
FROM
dbo.Person
WHERE dbo.Person.PersonID = @PersionID  /*  Add this line */

Also, you will most likely want to surround the INSERT and UPDATE with a BEGIN TRANSACTION and an COMMIT TRANSACTION

Related Question