I would like to keep the history of a table using SQL queries without triggers. The simplest way to do that is to fire a SELECT and INSERT into the history table like below:

INSERT INTO history (field1, field2) 
SELECT 
  field1, 
  field2 
FROM 
  current 
WHERE 
  field1 = condition

However is it possible to do this without hard-coding the columns i.e a select * equivalent?

up vote 1 down vote accepted

You can - you need to drop the column aliases from the INSERT list as below

CREATE TABLE SourceTest (id int, characters nvarchar(100))

INSERT INTO SourceTest (id, characters)
VALUES
(1,'hello'),(2,'goodbye'),(3,'test')

CREATE TABLE TargetTest (id int, characters nvarchar(100));

INSERT INTO TargetTest
SELECT * FROM SourceTest

SELECT * FROM TargetTest

DBFiddle

NOTE: The columns of both tables must be in the same order, and if the schema of the source table is modified the schema of the target table must be amended also.

DISCLAIMER: Using SELECT * in production code is seen as an anti-pattern - changes to column order or table structure can cause errors. I would recommend NOT using this method.

  • Correct. I was thinking of writing a pseudo syntax to explain what I need. Turns out that it is the exact one. Btw, thanks for pointing out that it is an anti-pattern. Explicit is better than implicit atleast in Production. I will go with the normal approach. – Nishant Dec 6 at 8:36
  • 1
    Using SELECT * in production code is seen as an anti-pattern That's not right I think. The problem you try to avoid is occured on the insertion stage, not on selection stage. The reason changes to column order or table structure can cause errors occures only in case of position-distinguishing whereas in most cases (excluding INSERT .. SELECT and WHERE (f1,f2) IN (SELECT f1,f2 ...)) name-distinguishing is used. In common SELECT * is anti-pattern because of excess data volume transfer mainly. – Akina Dec 6 at 8:52
  • @Akina you're only thinking of the database - SELECT * passing information to a front-end or middle layer can also break things. It's an anti-pattern for both reasons! – George.Palacios Dec 6 at 9:15
  • SELECT * passing information to a front-end or middle layer can also break things. It is excess data transfer a have said above. – Akina Dec 6 at 9:44
  • @Akina We'll agree to disagree on this one - unless you want to continue this in chat? – George.Palacios Dec 6 at 9:53

However is it possible to do this without hard-coding the columns

As pointed out already you can copy a row into another table as you are looking to do if they have the same column arrangement.

You can even create the target table using the base table as a template using something like SELECT * INTO NewTable FROM BaseTable or if you want to create the new table only and not populate it at all SELECT TOP 0 * INTO NewTable FROM BaseTable.

But I suspect you will want more in the history table than that main store: at least a timestamp otherwise all you have for each row is a bag of versions with no indication of what order they existed in. You could also end up with identical rows if there is a "no operation update" or a change is reverted in the base data. Having entirely identical rows can be problematical - for instance: how do you delete one of them?

You can do this too with slight variations to the examples above:

-- add row as copy
INSERT CopyTable SELECT *, SYSDATETIME() FROM BaseTable

-- create empty copy table using base as template
SELECT TOP 0 *, TimeRef=SYSDATETIME() FROM BaseTable

... keep the history of a table using SQL queries without triggers. The simplest way to do...

tag: sql-server

As you are using SQL Server there may be a built in option that will do this and more for you, depending on what versions of SQL server you need to support. If you are using Azure SQL or any editions (including Express) of SQL Server 2016 or later, then you can use system versioned temporal tables.

Not only do these keep the data for you without extra steps or triggers, you also get some nice syntactic sugar to make querying this data easy for a number of common scenarios (show me all version of this item from last year until now, show me what this item looked like last week, show me what the whole table looked like a week last Thursday at 02:34, ...).

They have their own gotchas, and you still need to take some care with the table structure (though for some changes this is handled automatically too), but might be exactly what you are looking for.

  • Thanks for pointing out the additional columns that might be needed. I will keep that in mind when I do the real implementation. – Nishant Dec 6 at 14:25

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.