Cannot insert an explicit value into a timestamp column

Question

Trying to find a way around the errors

SELECT * 
INTO #Products
FROM Products
WHERE Code = @sku

Update #products 
SET Code = Code+'Group',
    ProducttypeID = @SimpleProducttype,
    ParentProductID = @SimpleProductID,
    ID = NEWID()

ALTER TABLE #Products Drop column ts

INSERT Products
SELECT *, null ts FROM #Products

Then I get:

Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.

I don't want to have to explicitly put every column in the statement , because the table might change and I just want to duplicate the row & change a few things.

I've tried a few workarounds, read lots of posts but no joy. Help!

Answer 1

Always, always, always list out your columns in an INSERT statement.

INSERT INTO your_table (foo, bar)
  SELECT list_columns
       , here_too
  FROM   select_star_is_evil As seriously
;

The only excuse to not do this is laziness.

Being explicit with your code is vastly important!

</rant>

The error message is quite clear: you're trying to insert a value in to your timestamp column. Your final statement is trying to insert a NULL value.

The only way to avoid this behaviour is to not list the timestamp column in your INSERT list.