SQL Server

给字段加默认值 alter table prj_sql_cfg add default newid() for RPT_SQL_ID with values

修改字段长度alter table IR_SBJ_MPNG_HD alter column SELF_SBJ_RELA_FLAG  [decimal](5,0) 

添加字段 ALTER TABLE table_name ADD column_name datatype

修改字段名称 exec sp_rename 'check_rule.DEFAULT_ERROR_REASION','DEFAULT_ERROR_REASON','column'

 

 

Mapper技巧:

在一个数据库连接中,批量插入,

	<insert id="importInsert" parameterType="java.util.ArrayList">
		insert into IR_DIM_CST_TEMP values
		<foreach collection="list" item="mapping" separator=",">
			(
			#{mapping.code},
			#{mapping.value},
			#{mapping.corpCd},
			#{mapping.corpNm}
			)
		</foreach>
	</insert>

 

插入前查重,,,oracle如此做: 主要是有 dual表

select 'aaa' as A,'bbb' from dual where not EXISTS (select 1 from prj_sql_cfg 
where prj_id = #{projectId} and rpt_type = 'AllBookSet');

sqlServer 如此做: 主要时可以直接 select 然后做你做的事情

select 'aaa' as A,'bbb' as B where not EXISTS 
(select 1 from prj_sql_cfg where prj_id = '56945C0' and rpt_type = 'subjectBe');