RE: Table -> Generate Statement -> MERGE

Thanks very much John, I really appreciate that.

 

Regards,

 

Mike McAllister 

Principal Systems Engineer

Decypher

DSN:                      487-3751

Commercial:       (210) 652-3751

Cell:                       (512) 423-7447

Email:                    [email protected]

 

From: [email protected] [mailto:[email protected]] On Behalf Of John
Dorlon
Sent: Friday, December 21, 2012 4:55 PM
To: [email protected]
Subject: [toad] 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]] On Behalf Of
MCALLISTER, MICHAEL CTR AETC AETC/A3IS
Sent: Friday, December 21, 2012 2:41 PM
To: [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>