You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




15 September 2006

generate dml

I just discovered that cool new function in SQL/Developer

just right click on the table and chose export sql insert


-- INSERTING into EMP
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (7369,'SMITH','CLERK',7902,to_date('1980-12-17','DD-MON-RR'),800,null,20);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (7499,'ALLEN','SALESMAN',7698,to_date('1981-02-20','DD-MON-RR'),1600,300,30);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (7521,'WARD','SALESMAN',7698,to_date('1981-02-22','DD-MON-RR'),1250,500,30);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (7566,'JONES','MANAGER',7839,to_date('1981-04-02','DD-MON-RR'),2975,null,20);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (7654,'MARTIN','SALESMAN',7698,to_date('1981-09-28','DD-MON-RR'),1250,1400,30);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (7698,'BLAKE','MANAGER',7839,to_date('1981-05-01','DD-MON-RR'),2850,null,30);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (7782,'CLARK','MANAGER',7839,to_date('1981-06-09','DD-MON-RR'),2450,null,10);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (7788,'SCOTT','ANALYST',7566,to_date('1987-04-19','DD-MON-RR'),3000,null,20);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (7839,'KING','PRESIDENT',null,to_date('1981-11-17','DD-MON-RR'),5000,null,10);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (7844,'TURNER','SALESMAN',7698,to_date('1981-09-08','DD-MON-RR'),1500,0,30);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (7876,'ADAMS','CLERK',7788,to_date('1987-05-23','DD-MON-RR'),1100,null,20);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (7900,'JAMES','CLERK',7698,to_date('1981-12-03','DD-MON-RR'),950,null,30);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (7902,'FORD','ANALYST',7566,to_date('1981-12-03','DD-MON-RR'),3000,null,20);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (7934,'MILLER','CLERK',7782,to_date('1982-01-23','DD-MON-RR'),1300,null,10);


It is quite handy to post insert statements on forums, or move a table from one database to another just with copy paste, or export only a subset of the columns, because you can export a view too

4 Comments:

Blogger Laurent Schneider said...

ok, it is not very nice to introduces a bug 2050 here, because of the 'RR' format for year !

15/9/06 14:31  
Blogger Laurent Schneider said...

actually, it is not a bug 2050, it is a pure bug !


Insert into "EMP" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (7369,'SMITH','CLERK',7902,to_date('1980-12-17','DD-MON-RR'),800,null,20)
*
ERROR at line 1:
ORA-01861: literal does not match format string

15/9/06 14:34  
Blogger Laurent Schneider said...

note that there are a few more options for limiting the number of rows/columns

15/9/06 14:45  
Blogger Nicolas Gasparotto said...

Hi Laurent,
There was an article into OraMag from May/June 2006 : Oracle SQL Developer Soars, but nothing about this problem... interesting indeed.

15/9/06 21:44  

Post a Comment

<< Home