471,581 Members | 2,502 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,581 software developers and data experts.

Select and Insert Question

5
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
Sep 15 '06 #1
3 2348
aramki
9
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.
Sep 18 '06 #2
Kliot
5
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
Sep 18 '06 #3
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.....
Sep 19 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Alex | last post: by
3 posts views Thread by Tcs | last post: by
13 posts views Thread by RR | last post: by
7 posts views Thread by Brian | last post: by
2 posts views Thread by paulmitchell507 | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by Vinnie | last post: by
1 post views Thread by lumer26 | last post: by
reply views Thread by lumer26 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.