insert into history table

I have modied the storeprocedures with uniqueidentifier to achieve this functionality, hope this will work for you:

Insert Record: 

ALTER PROCEDURE [dbo].[procAddTableA]
@FName nvarchar(50),
@LName nvarchar(50),
@Address nvarchar(50)
AS
BEGIN
DECLARE @GuidID AS uniqueidentifier = NEWID()
INSERT INTO [dbo].[TableA]([GuidID],[FName],[LName],[Address], [RecordDate]) VALUES(@GuidID, @FName, @LName, @Address,GETDATE())
INSERT INTO [dbo].[TableAHistory]([GuidID],[FName],[LName],[Address], [RecordDate]) VALUES(@GuidID, @FName, @LName, @Address,GETDATE())
END

Update Record:

ALTER PROCEDURE [dbo].[procUpdateTableA]
@GUIDID UNIQUEIDENTIFIER,
@FName nvarchar(50),
@LName nvarchar(50),
@Address nvarchar(50)
AS
BEGIN
INSERT INTO TableAHistory
SELECT * FROM TableA WHERE GuidID = @GUIDID
UPDATE [dbo].[TableA]
SET [FName] = @FName,[LName] = @LName,[Address] = @Address, ModifiedDate = GETDATE()
WHERE GuidID = @GUIDID
-- INSERT a New Rown in History
--INSERT INTO [dbo].[TableAHistory]([ID],[FName],[LName],[Address]) VALUES(@ID, @FName, @LName, @Address)
END

Result:

Insert Transaction-1:

[dbo].[procAddTableA] 'Kerry', 'William', 'NY'

Update Transaction-2:

[dbo].[procUpdateTableA] '92505FE9-B574-4ECA-861C-6F2F5968F5A3', 'John', 'William', 'NY'

Update Transaction-3:

[dbo].[procUpdateTableA] '92505FE9-B574-4ECA-861C-6F2F5968F5A3', 'John', 'Velley', 'NY'

Muhammad Imran Ansari

yes, but everytime I do not have to insert all rows but just the affected row

e.g row 1 has changed 5 times today so to insert previous 4 rows into history and the latest will be in main table.

currently to update and insert into target table ,I am using merge logic 

I have to achieve this how it will work when we write trigger on insert and update 

Muhammad Imran Ansari

• here are the tables

Source table

target table 

target history table

Thank you Muhammad Imran Ansari, 

but I do not know which @Id will get updated as the source t fill thiese table is different 

You can achieve through store procedures. On add new record into Table-A then same time add data into Table-A History. On updating Table-A, to maintain history insert previous or new entry into Table-A History. I have create two store procedure for this:

Add New Record:

CREATE PROCEDURE procAddTableA
@ID BIGINT,
@FName nvarchar(50),
@LName nvarchar(50),
@Address nvarchar(50)
AS
BEGIN
INSERT INTO [dbo].[TableA]([ID],[FName],[LName],[Address]) VALUES(@ID, @FName, @LName, @Address)
INSERT INTO [dbo].[TableAHistory]([ID],[FName],[LName],[Address]) VALUES(@ID, @FName, @LName, @Address)
END
GO

Update Record:

ALTER PROCEDURE [dbo].[procUpdateTableA]
@ID BIGINT,
@FName nvarchar(50),
@LName nvarchar(50),
@Address nvarchar(50)
AS
BEGIN
-- INSERT History Before Update the with Current
INSERT INTO TableAHistory

SELECT * FROM TableA WHERE ID = @ID
UPDATE [dbo].[TableA]
SET [FName] = @FName,[LName] = @LName,[Address] = @Address
WHERE ID = @ID
-- INSERT History With New record After Update the
--INSERT INTO [dbo].[TableAHistory]([ID],[FName],[LName],[Address]) VALUES(@ID, @FName, @LName, @Address)
END

You can change table structure accordingly.