Clone a recordset but change the key ?!

[TheBionicOrange]

I have 17 records in a table, with a key of "10". I want to clone these, but have the key as "12", but keep all other field info the same.

Using SQL, how would I do this ?

insert into TableA (Field A,Field B etc)
values (select * from TableA where key = 10)

... wouldn't work as theres no way of saying "but make the keys 12 and not 10.

I guess this is probably a simple question, but I can't find an answer.

Can anyone help ?

[GaryMazzone]

All I can think of is passing though a temp table. Insert there update 10 to 12 then select insert into the other table. Drop the temp.

[si_the_geek]

You could hard-code the values in the select statement, eg:

values (select 12, FieldB etc from TableA where key = 10)

[Foxer]

Ya - what Si said - works fine for me

insert into TableA (12 ,Field B)
values (select * from TableA where key = 10)


If you wanted to get really really yucky about it you could insert into a temporary table, run an update statement to change the 10 to a 12, and then move the rows back to TableA but why bother. (Whoops - this is exactly as GM described).