In Oracle I have the requirement to produce a set of INSERT statements using the results of a SELECT statement. Basically the same way that Toad for Oracle will export the contents of a table to a set of Insert Statements either in a file or on the clip board. Is this possible?

The reason for this is that some of my geometric data is in WGS84 format and when I access it it needs to be British National Grid. This is not a problem in Oracle as I can simply use SDO_CS.Transform(date, srid) however in SQL Server this is not possible. My intention is to produce the INSERTS in Toad and them run them on SQL Server in order to populate the geometry column with the pre-transformed data. This means applying the transform in Oracle during the SELECT.

Toad can produce the INSERT statements and it seems to be fine for you. So what is the question? Where exactly do you need help?
–
CodoMay 31 '12 at 11:29

In Toad you cannot modify the SELECT statement which produces the INSERTS via via the export tool. Or if you can I dont know how as the window the query appears in does not allow me to edit. The data is in WGS84 in the Toad/Oracle table and I need it to have been converted to BNG when it is put into the INSERT statements. My question really is if I write a SELECT statement which returns the contents of the table but with the relevant column transformed then is it possible to then use the output of the query to produce INSERT statements as the export tool does?
–
CSharpenedMay 31 '12 at 11:33

I don't quite understand how you're trying to export the data from TOAD. But it should work as follows: 1) Run the SELECT statement in a TOAD editor window. 2) Press the right mouse button in the data grid with the result and select "Export dataset...". 3) Choose "Insert Statements" from the first drop down. 4) Click OK.
–
CodoMay 31 '12 at 11:50