[Pansophy] Data transfer

[Megan McDonald]

Hi David,

I was hoping you could give me some advice on how best to go about moving a bunch of data.

Situation: We are combining 3 projects to 1 and I want to move all of the traveler data but I keep running into an issue of carriage returns
What we've tried:

1.      Ideally, it would be nice to just copy and paste in SQL developer but we have CLOBs that people have put new lines in their comments so when pasting, that misaligns all the data.

2.      I did the data pull on Excel but I haven't been able to push back to a table, have you done this before? And if so, could you provide assistance?

3.      Exported the table to a .sql file with an INSERT statement but the INSERT doesn't include the CLOBs

4.      Pasted the table to an excel file, searched and searched new lines (Find and Replace. Find: ctrl+j Replace: space) which successfully took out the carriage returns in excel but when pasting back to developer, it still reads the carriage return (assuming it's a windows vs unix)

5.      Val was able to run INSERT INTO ADAPPSTST.ER5CCMACMDISAC20 SELECT * FROM ADAPPSTST.C20CMDISA WHERE OBS IS NULL; but for some of the tables, we have to shift some things around because fieldnames have slightly changed and the value of our sequence number column will be adjusted.
Is there a way to do a column search in SQL developer and delete the carriage returns? I'm just looking for any advice on how best to proceed

Thanks!
Megan

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mailman.jlab.org/pipermail/pansophy/attachments/20220824/294710b1/attachment.html>