Inserting from a SELECT but changing one column?

using Oracle

DECLARE
CURSOR CUR_D IS
  SELECT *
  FROM TABLE
  WHERE id = some id;
ROW_D CUR_D%ROWTYPE;

BEGIN
  OPEN CUR_D;
  FETCH CUR_D INTO ROW_D;
  CLOSE CUR_D;

  ROW_D.column := 'some data';

  INSERT INTO some table
  VALUES ROW_D;

END;
/

You can use temporary tables

Create Temporary table

CREATE TEMPORARY TABLE temp_table AS (SELECT * FROM MyTable WHERE ...);

Update column

UPDATE temp_table SET column='Value' WHERE ...;

Or drop a column

ALTER TABLE temp_table DROP column_name;

Insert to destination table

INSERT INTO MyDestinationTable SELECT * FROM temp_table;

For SQL Server, the syntax would be:

insert  into TargetTable
        (col1, col2, col3, ...)
select  getdate(), col2, col3, ... 
from    SourceTable

SQL Server can generate a column list for you. Right click the table name, and choose Script as -> Select -> To New Window.

Create a VIEW with required number for columns.

Assume Tbl1 has 4 columns. Create a view with required columns. Here Col1 has been excluded.

CREATE VIEW V1 AS
SELECT col2, col3, col4
FROM TBL1

Use the VIEW for Inserting. Col1 value for TBL2 will be current date, for other columns, the values will be from view V1 ie., col2, col3, col4 from TBL1

INSERT INTO TBL2
SELECT GETDATE(), * 
FROM V1

This way you dont need to specify all the columns each time.

Hope it helps