>>>>I am using the tastrade newid function. I want to copy data from one table to another table. In the new table, I need to assign surrogated key to each record. I tried using both APPEND FROM ARRAY or INSERT FROM ARRAY and the newid didn't seem to work. Am I correct? I know I can use SCAN+INSERT but is there a better way?
>>>>
>>>>Thanks in advance.
>>>
>>>Do the two tables have the same structure?
>>>Same field names?
>>>Same PK field name?
>>>Same table name?
>>>
>>>Anyway, the way I would go about this is to SCATTER MEMVAR, and if the second table has the same primary key field name as the old, RELEASE the memory variable associated with that field, SELECT the new table and GATHER MEMVAR. This should allow the unique ID function to work properly since you are not assigning any value to the PK with the INSERT.
>>
>>To put it in simpler words, I have a table A which I want to populate. It has a primary surrogated key with default value NEWID(). I get the input of this table by SQL SELECT. The SQL selected result does not have the surrogated key field and a few other fields. The rest of the fields are exactly the same. These records are to be inserted into table A.
>>
>>I tried to use SELECT INTO ARRY and APPEND FROM ARRAY/INSERT FROM ARRAY, the surrogated key field didn't seem to get assigned. Are you suggesting using SCATTER TO ARRAY and GATHER FROM ARRAY instead or did I confuse you in my previous message?
>
>Sorry, I was unclear from your original message on what you wanted to do. When you say the PK didn't get assigned, do you mean that the rest of the record gets appended, but no default value is assigned (therefore violating uniqueness)? This is odd, and I would guess that the cause is that you are somehow inserting a blank value over the default primary key. You could check to see if anything gets assigned there with getfldstate() (if you are using buffering). Keep in mind that a default value in a record chenges the GETFLDSTATE value for a field to 2, even though the value appears to be there when the record is added.
>
>Anyway, the approach you are taking should work, but IMHO, there is no reason not to just SELECT into CURSOR, and then APPEND FROM the cursor. No need for arrays.
>
>When I suggested SCATTER and GATHER, I was assuming you just wanted to copy one record from one table to another. I hope this helps.

No, GetFldState returns '3' for the PK if I use APPEND FROM. It can work if I use INSERT - SQL without specifying the PK. Ken's suggestion of putting the newid function in the SQL works, though.