Sql-server – Copying all columns of a row from table into another

copysql server

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?

Best Answer

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.

Related Question