>>>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?
You could try this;
SELECT newid() AS newid, column1, column2;
FROM tableA;
INTO ARRAY atable

SELECT tableb
APPEND FROM ARRAY atable