CODEWITHSUNDEEP
sqloracle-databaseplsql
LoudNPossiblyWrong
LoudNPossiblyWrong

Reputation: 3893

Is it possible to return a row from a table as DML (insert, update) in Oracle?

I am trying to generate an insert statement from a row in a table?

if the row is [1 , "myfirstname", "mylastname"] is there a way of getting a query like:

"insert into table sometable (uid, FirstName, LastName) values (1 , "myfirstname", "mylastname")

...without using dynamic sql?

Thanks

EDIT:

admittedly, i am lazy about this and was trying not to use dynamic sql / all_tab_columns. I was hoping for some magical function call that returns a DML statement for a row just as there is a way to get a DDL expression for an object:

select dbms_metadata.get_ddl('TABLE','TABLENAME') from dual ... but DML for a row ... it's too much to ask i think.

Upvotes: 1

Views: 409

Answers (6)

Anubhab
Anubhab

Reputation: 1786

Yes you can do that in Oracle Sqldeveloper. Its easy as CMG told in the post. Just right click on the table you want to get the datas-->Export-->INSERT-->specify file path to save the scripts-->bingo :)

Upvotes: 0

CMG
CMG

Reputation: 11

download SQLDeveloper from Oracle website (it's free). Log into your database schema, and from the list of Tables, right click on the table you want to get data from, choose "Export..." and then follow the wizard steps, chosing the format "Insert" for your data. It'll create you a file of insert statements for the data in your table. You can restrict it to just a subset of rows by supplying where-clause conditions in one step in the wizard.

Upvotes: 0

Kirill Leontev
Kirill Leontev

Reputation: 10941

Assuming your string format is more or less static you might want to play with this sample:

INSERT
   INTO
  someTable
  (
     SELECT
      regexp_substr(expr, '[0-9]+') val1   ,
      regexp_substr(expr, '[a-zA-Z]+') val2,
      regexp_substr(expr, '[a-zA-Z]+', 1, 2) val3
       FROM
      (
         SELECT
          '[1 , "myfirstname", "mylastname"]' expr
           FROM
          dual
      )
  ) ;

Upvotes: 0

Scott Bruns
Scott Bruns

Reputation: 1981

Try this:

select 'insert into tablename (fieldname) values(''' + a.fieldname + ''')' from table a

Format each field value as necessary.

Upvotes: 1

user330315
user330315

Reputation:

You want to turn "raw" data into a SQL insert statement.

There is no way to do this with SQL, but usually GUI clients have this kind of functionality

Upvotes: 0

Liviu T.
Liviu T.

Reputation: 23664

I think you need this

   insert into table sometable (uid, FirstName, LastName) SELECT 1 ,'myfirstname', 'mylastname' from othertable where somecondition

Take a look at this also

Upvotes: 1

Related Questions