CODEWITHSUNDEEP
sqlsql-server
Jack Kada
Jack Kada

Reputation: 25212

Insert Into Statement - Omitting one Column

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

Upvotes: 2

Views: 231

Answers (5)

Simon Gill
Simon Gill

Reputation: 1106

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)

Upvotes: 0

user274242
user274242

Reputation: 11

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?

Upvotes: 1

Ahmad
Ahmad

Reputation: 24847

I would simply do the ffg 

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

Upvotes: 1

Klaus Byskov Pedersen
Klaus Byskov Pedersen

Reputation: 120937

Yes, you can do it like so:

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

Upvotes: 1

ewernli
ewernli

Reputation: 38615

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

Upvotes: 4

Related Questions