常用SQL语句

SQL Server 跨库连接插入：

insert  into 本地表
        select  *
        from    openrowset('SQLOLEDB ','172.16.50.40'; 'sa'; 'password', DB.dbo.远程表);

SQL Server 复制表：

select * into 新表 from 旧表

SQL Server 复制表结构：

select * into 新表 from 旧表 where 1 = 2

SQL Server 远程表导入本地表：

select * into 本地表 from openrowset( 'SQLOLEDB ', '172.16.50.40'; 'sa'; 'password', DB(远程数据库).dbo.远程表)

Oracle 建菜单：

select t.rowid,t.* from ssp_page_menu t where t.show_name like '%%'
select t.rowid,t.* from ssp_page_menu t where t.menu_level like '%'
--加密炼手持的时候先加密炼手持菜单和权限，再加后工序平台菜单和权限（ssp_page_action中的Bakup_flag为密炼syspageaction中该菜单的权限objid）
select max(objid) from ssp_page_menu
insert into ssp_page_menu(objid, 
                          menu_level, 
                          show_name, 
                          remark, 
                          page_url, 
                          ico_name, 
                          is_show,  
                          record_user_id, 
                          record_time, 
                          seq_idx, 
                          delete_flag, 
                          bakup_flag, 
                          bakup_time,
                          HTTP_URL)
select (select max(objid) from ssp_page_menu)+1, '', '',remark,'',ico_name, is_show, 
                          record_user_id, 
                          record_time, 
                          6, 
                          delete_flag, 
                          bakup_flag, 
                          bakup_time,
                          HTTP_URL from ssp_page_menu where objid = '' 
    insert into ssp_page_action(objid, 
       page_menu_id, 
       action_id, 
       action_name, 
       action_url, 
       show_name, 
       remark, 
       ico_name, 
       record_user_id, 
       record_time, 
       seq_idx, 
       delete_flag, 
       bakup_flag, 
       bakup_time)
select rownum+(select max(objid) from ssp_page_action), 
       (select max(objid) from ssp_page_menu), 
       action_id, 
       action_name, 
       action_url, 
       show_name, 
       remark, 
       ico_name, 
       record_user_id, 
       record_time, 
       seq_idx, 
       delete_flag, 
       bakup_flag, 
       bakup_time from ssp_page_action t where page_menu_id = ''  

Oracle 建表及主键自增

-- Create table
create table PSM_FIX_MAIN
(
  OBJID       NUMBER(10) not null,
  TYRE_NO     NUMBER(20),
  TYRE_BACKNO NUMBER(20),
  MATERIAL_ID NUMBER(20),
  ERPCODE     VARCHAR2(20),
  OUT_LEVEL   VARCHAR2(20),
  BACK_LEVEL  VARCHAR2(20),
  OUT_DATE    DATE,
  BACK_DATE   DATE,
  RECORD_TIME DATE,
  RECORD_USER VARCHAR2(20),
  BUGS        VARCHAR2(20)
)
tablespace MESDB_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 16
    next 8
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns 
comment on column PSM_FIX_MAIN.TYRE_NO
  is '外修胎号';
comment on column PSM_FIX_MAIN.TYRE_BACKNO
  is '回归胎号';
comment on column PSM_FIX_MAIN.MATERIAL_ID
  is '物料ID';
comment on column PSM_FIX_MAIN.ERPCODE
  is 'ERP编码';
comment on column PSM_FIX_MAIN.OUT_LEVEL
  is '外修品级';
comment on column PSM_FIX_MAIN.BACK_LEVEL
  is '回归品级';
comment on column PSM_FIX_MAIN.OUT_DATE
  is '外修时间';
comment on column PSM_FIX_MAIN.BACK_DATE
  is '回归时间';
comment on column PSM_FIX_MAIN.RECORD_TIME
  is '记录时间';
comment on column PSM_FIX_MAIN.RECORD_USER
  is '记录人';
comment on column PSM_FIX_MAIN.BUGS
  is '病疵';
-- Create/Recreate primary, unique and foreign key constraints 
alter table PSM_FIX_MAIN
  add constraint PSM_FIX_MAIN primary key (OBJID)
  using index 
  tablespace MESDB_DATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
       create sequence SEQ_PSM_FIX_MAIN_autoinc
     minvalue 1
     maxvalue 9999999999999999999999999999
     start with 1
     increment by 1
     nocache;
     
create or replace trigger insert_PSM_FIX_MAIN_autoinc
     before insert  on PSM_FIX_MAIN
     for each row
          
begin
     select SEQ_PSM_FIX_MAIN_autoinc.nextval into :new.OBJID from dual;
end;