Insert using values and subselect

[padshore@optonline.net]

Lets say the file is call yourfile and is in library yourlib The file contails 4 fields, field1 field2 field3 field4 where field 2 is the key field then the SQL would be something like insert into yourlib/yourfile a select b.field1, '1234', b.field2, b.field3, b.field4 from yourlib/yourfile b where.......; then you insert whatever you want in the ......

[Guest.Visitor]

I need to insert records from one database to the same database and would like to use SQL --- effectively copying a record and changing the key. The round about way that I use is to create record set fetch the records into a data structure, change key to the structure, and update the key on the insert using values. I thought that there may be a more efficient way of doing so with a single statement. Is this possible?

[garylavery@aol.com]

You can do what "padshore" says. But be careful not to create a dupe key or the statement fails. Even with a "WHERE" clause, your incoming data can have it's own duplicate. The "WHERE" clause will not access the records just inserted - only the records that existed prior to the start of the statement. And it's not easy to find the dupe key since it's not explicitly noted in any error message. I do literally hundreds of thousands of inserts every week and the only way I could create bulletproof code was by creating the records in a non-keyed temp file first, deleting dupes in that (slow!), THEN going from the temp file to the master file with the "WHERE" clause in place. Hope this helps.