RE: Table -> Generate Statement -> MERGE

Well, I decided that if I just left it at 'soon', that would turn into 'long overdue' again.   It will be in the next beta.

I added USING and ON clauses similar to what you get when you make merge statements from 'export dataset', like so, freshly generated from SCOTT.EMP:

MERGE INTO EMP d
USING (
  Select
    <value> as EMPNO,
    <value> as ENAME,
    <value> as JOB,
    <value> as MGR,
    <value> as HIREDATE,
    <value> as SAL,
    <value> as COMM,
    <value> as DEPTNO
  From Dual) s
ON
  (d.EMPNO = s.EMPNO)
WHEN MATCHED
THEN
UPDATE SET
  d.EMPNO = s.EMPNO,
  d.ENAME = s.ENAME,
  d.JOB = s.JOB,
  d.MGR = s.MGR,
  d.HIREDATE = s.HIREDATE,
  d.SAL = s.SAL,
  d.COMM = s.COMM,
  d.DEPTNO = s.DEPTNO
WHEN NOT MATCHED
THEN
INSERT (
  d.EMPNO, d.ENAME, d.JOB,
  d.MGR, d.HIREDATE, d.SAL,
  d.COMM, d.DEPTNO)
VALUES (
  d.EMPNO, d.ENAME, d.JOB,
  d.MGR, d.HIREDATE, d.SAL,
  s.COMM, s.DEPTNO);

Those are primary key columns in the ON condition, and if there are no PKs in the table, it'll just spit out the text you suggested.  For the <value> items, I could go so far as to say "<date value>", "<string value>", etc, if y'all think that would be helpful, but for now, it is just <value>


From: [email protected] [mailto:[email protected]] On Behalf Of John Dorlon
Sent: Friday, December 21, 2012 3:54 PM
To: [email protected]
Subject: [toad] RE: Table -> Generate Statement -> MERGE


Yeah, that's long overdue.  Thanks for the reminder.  I'll get to it soon.

From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of MCALLISTER, MICHAEL CTR AETC AETC/A3IS
Sent: Friday, December 21, 2012 2:41 PM
To: [email protected]<mailto:[email protected]>
Subject: [toad] Table -> Generate Statement -> MERGE



I'd like to suggest that along with being able to generate a basic insert, update and select statement for a table, it would be nice if TOAD could generate a basic merge statement as well. You could leave the USING portion of the merge statement blank, but still generate the update and insert clauses for me with all the columns of the table. As an example, consider the following table:-

CREATE TABLE EXAMPLE_TBL
(
COL1 VARCHAR2 (50),
COL2 VARCHAR2 (50)
);

Generate for me the following merge statement:-

MERGE INTO example_tbl d
USING (/* insert your merge source here */) s
ON (/* insert your merge condition here */)
WHEN MATCHED
THEN
UPDATE SET d.col1 = s.col1, d.col2 = s.col1
WHEN NOT MATCHED
THEN
INSERT (d.col1, d.col2)
VALUES (s.col1, s.col2);

Regards,

Mike McAllister
Principal Systems Engineer
Decypher
DSN: 487-3751
Commercial: (210) 652-3751
Cell: (512) 423-7447
Email: [email protected]<mailto:michael.mcallister.4.ctr%40us.af.mil>