Select and Insert Question

[Kliot]

I have a table that I want to query and modify the data in some columns then insert the result back into the same table as new records. The index column will be changed so there will not be a worry about an index violation.

I have no problem running the select query to modify the records, but I'm not sure what's the best way to insert the result back into the table. I'm hoping that there is a way I can do this in one query

I'm SqlServer 2005

Thanks
Perrin

[aramki]

Yyou cannot do it in the same statement since both are different type of operations update/insert. My recommendation is that you should have a field in that table like "updatetime " that should be updated with getdate() when your update runs, that allows you to make an insertable select statement based on the updatetime.

[Kliot]

Actually I was shown an easy that it can be easily done with a

Insert, Select Query

insert into my_table (field1, field2, field3)
select field 1,
field 2,
case
when field3 = 1
then field3 * 10 / 200
when field3 = 4
then field3 * 20 / 50
end
from my_table
where field1 = 5

[agocurti]

Actually I was shown an easy that it can be easily done with a

Insert, Select Query

insert into my_table (field1, field2, field3)
select field 1,
field 2,
case
when field3 = 1
then field3 * 10 / 200
when field3 = 4
then field3 * 20 / 50
end
from my_table
where field1 = 5

I think you can to use this syntax:

select * into my_tmp_tab from my_table where.....