insert into history table

Question

Hi, I have two tables Table-A must contains current data rows. Table-A-History must contains historical data whenever a new data row is available, update Table-A 's row and insert a new row into Table-A-History . This needs to be achieve without using trigger on table, but may be with help of stored proc Could anyone of you can help here

Sachin Singh · Answer

simply merge the tables create two tables (TableA) your main table and TableA_History your hostory table CREATE TABLE dbo.TableA (id INT , Column1 nvarchar(30)) CREATE TABLE dbo.TableA_History (id INT , Column1 nvarchar(30), TimeStamp DateTime) create the procedure to merge records when something changes on TableA create procedure ManageHistory as begin MERGE INTO dbo.TableA_History USING dbo.TableA ON TableA_History.id = TableA.id AND TableA_History.Column1 = TableA.Column1 WHEN NOT MATCHED BY TARGET THEN INSERT (id,Column1,TimeStamp) VALUES (TableA.id,TableA.Column1,GETDATE()) end call this procedure on your each transaction on TableA

Gajendra Jangid · Answer

Pls share your table structure. 1. create triger on main table . then write code inside trigger insert into histreytble(columns) select columns from maintable where condition

Muhammad Imran Ansari · Answer

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'

jayu p · Answer

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 · Answer

In the table [CompliancePolicyState] Id datatype is uniqueidentifier [GUID] and you can update the record in actual table [CompliancePolicyState] on the bases of Id columns. In CompliancePolicyState.Id is refence in CompliancePSHistory with columns CompliancePSHistory.CompliancePolicyStateId

jayu p · Answer

Muhammad Imran Ansari here are the tables Source table target table target history table

Muhammad Imran Ansari · Answer

Can you please share the structure of table?

jayu p · Answer

Thank you Muhammad Imran Ansari, but I do not know which @Id will get updated as the source t fill thiese table is different

Muhammad Imran Ansari · Answer

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.