Insert Into Statement - Ommiting One Column

Question

I am trying to insert into a table all data but change just the date column

So far I have something like

INSERT INTO MyTable (SELECT * FROM MyTable...)

What I want to do is set one of the columns to be a constant value (like todays date instead of what is selected out)

Does anyone know of an easy way of doing that

Answer 1

INSERT INTO MyTable (col1, col2, col3) (SELECT col1, col2, 'constant' FROM MyTable...)

Answer 2

Yes, you can do it like so:

INSERT INTO MyTable (SELECT MyTable.Id, MyTable.xxx, CONSTANT_VALUE FROM MyTable ...)

Answer 3

I would simply do the ffg

   INSERT INTO MyTable
   Select col1, col2,....., THE_CONSTANT_VALUE, SOME_OTHER_VALUE from MyTable

Answer 4

You can do this if you specify the columns you are inserting into, something like this:

INSERT INTO MyTable (Col1, Col2, DateVal, StaticValue) (SELECT Col1, Col2, getdate() As DateVal, 'test' As StaticValue FROM MyTable2)

This would get Col1 and Col2 from MyTable2 but use the getdate() function to get the current date for the DateVal column, and every row inserted would have 'test' for the StaticValue column.

Does this help?

Answer 5

To update all the date field of every row in MyTable to the current date do:

UPDATE MyTable SET thedatecolumn = GETDATE()

If you want to insert from Table1 into Table2 where they have the same structure do:

INSERT INTO Table2 (column1, column2, column3, fixedcolumn) (SELECT column1, column2, 'textvalue' AS staticcolumn, GETDATE() AS functioncolumn FROM Table1)