473,240 Members | 1,499 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,240 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 2432
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

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

Similar topics

3
by: Alex | last post by:
Hi, I need to form a query where i can add some columns based on the result. Table A ColA, ColB ---------- 1 A 2 B
3
by: Tcs | last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't loaded it yet. I'm still using MS Access. And no, I don't believe this is an Access question. (But who knows? I...
13
by: RR | last post by:
Hi, It appears that DB2 has an arbitrary restriction on the use of "fetch first N rows". I want to insert into a table from a large table query, but only insert the first N rows: insert...
2
by: JMCN | last post by:
Here is a simple question regarding insert into and select insert into statements. I have the follwing sql from my table make query. how can i add other query with the same field names as tbl1? ...
7
by: Brian | last post by:
Hello all - I am trying to Insert a new record to an Access 2002 database. Following the insert, I need to obtain the primary key of the inserted row (person_ID) which is an Autonumber field. I...
1
by: Grant McLean | last post by:
Hi First a simple question ... I have a table "access_log" that has foreign keys "app_id" and "app_user_id" that reference the "application_type" and "app_user" tables. When I insert into...
5
by: Lennart | last post by:
I really like the construction: select * from new table (update ....) X but I noticed that it cant be used as: insert into T select * from new table (update ....) X because of:
1
by: new | last post by:
I am building an effective dated file that shows results to date. In part 1 of the select I I process a larges set of input and genrate records for each date in the processing period for which...
4
by: Andrew S | last post by:
Hello Mr. Expert: - I have 3 tables in mysql in MyISAM table format, I am using mysql4.0 on freebsd5.3 - producttbl, productdetailentbl, pricetblN - they all have "productid" as the Primary KEY....
2
by: paulmitchell507 | last post by:
I think I am attempting a simple procedure but I just can't figure out the correct syntax. My asp (classic) page runs a SELECT query to obtain dates and ID's from 2 tables uSQL = "SELECT...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

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.