Duplicating records with a few changes

[artmaat]

I need to duplicate 600 records in a data base table and make some changes along the way . Need some help on this. Basically select a table and all of its fields, duplicate the data to make new records except that two of the fields that have the same info in all of them , will be changed to have new info in them. ie;
Take all of the records in the IM_ITEMFILE table duplicate them and change the info in the MATERIAL field from CTR to C52 and change the LOCATION from 59 to 52, while leaving the original data intact .

 

[bperry]

The normal way I would do this would be to create a temp table, copy * from ourTable to temptable, fiddle around with the tempTable records, and then copy them from temptable to OurTable. Just in case something went wrong.


But this approach should also work, inserting records from OurTable right into itself.

Insert into IM_ITEMFILE
(col1, col2, col3, col4, col5 ....)
select col1, col2, 'C52', col4, 52)
from IM_ITEMFILE
-----------------------
Hope this gives you some ideas,
bperry

 

[artmaat]

Can't seem to get it to work , this is what I'm trying..
Insert into IM_ITEMFILE
(col1, col2, col3, col4, col5)
select col1, 'C52','52', col4, col5
from IM_ITEMFILE

result
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'col1'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'col1'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'col4'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'col5'.

 

[Rosko]

bperry 's statement should work. Did you by chance forget to replace Col1... with your own column names?


Rosko