Page 1 of 1

INSERT INTO table SELECT * FROM table

Posted: Fri Jan 21, 2005 7:12 am
by irealms
I want to run a query in the form

INSERT INTO table SELECT * FROM table

which i can do with no problems.

The problem i do i have is the data pulled in the select area needs to bbe changed slightly before inserting.

I Want to pull out the info, change 2 entries and then insert into another table. Is there a way to do this without building a table of results and then reinserting each one in the form INSERT INTO table (field1,field2,field3) VALUES (entry1,entry2,entry3) ?

If i did it this way then the query would have to be changed each time the table structure changed.

Posted: Fri Jan 21, 2005 7:27 am
by Weirdan
hmm... perhaps something like this:

Code: Select all

insert into table2
select 
  case id 
     when 1 then 'something'
     when 2 then 'something_else'
  end case as field1,
  case id
     when 2 then 'blah'
     when 10 then 'blah-blah-blah'
  end case as field 2,
  field3 /* as is */
from table1

Posted: Fri Jan 21, 2005 7:35 am
by irealms
Hmm not sure i follow that.

Say i had this

INSERT INTO table2 SELECT * FROM table1

table 1 has say 5 fields, and i want to change field 1 and field 3 and insert all 5 fields with 1 and 3 changed.

So it would insert fields 2,4,5 as they were in table1, but 1 and 3 altered.

Posted: Fri Jan 21, 2005 8:11 am
by Weirdan
irealms wrote:Hmm not sure i follow that.

Say i had this

INSERT INTO table2 SELECT * FROM table1

table 1 has say 5 fields, and i want to change field 1 and field 3 and insert all 5 fields with 1 and 3 changed.

So it would insert fields 2,4,5 as they were in table1, but 1 and 3 altered.
So, values of fields 1 and 3 would become fixed, say 'blah' and 'blah-blah' respectively, regardless of what values they had in the table1?
If so, the query is simple:

Code: Select all

insert into table2
select
  'blah' as field1,
   field2,
   'blah-blah' as field3,
   field4,
   field5
from table2

Posted: Wed Jan 26, 2005 3:27 am
by irealms
yeah so 1 and 3 could work like this $variable as field1 ?

That will work fine, also is there a way to get it to detect fields automatically, so it knows what fields to put in rather than me doing blas as field1, nextname?

So all i enter is the ones that need changing and it draws the rest? This would save updating in future if bits need adding to the table.