DB2与SqlServer数据移植备忘
一、自增序列：
DB2:
create table TestTable
(
recordindex integer identity generated as always,
stringDateTime varchar(50),
TrueDateTime timestamp
);
SQLServer:
create table TestTable
(
recordindex int identity,
stringDateTime varchar(50),
TrueDateTime datetime
)
二、记录筛选：
DB2:
strTemp = "select * from TestTable fetch first 5 rows only";
SQLServer:
strTemp = "select top 5 * from TestTable";
三、时间函数:
SQLServer:
strTmpDB2 = "DELETE 登录日志表 WHERE DATEDIFF(DAY,登录时间,getdate())>"+Days;
DB2:
strTmpDB2 = "DELETE 登录日志表 WHERE Date(登录时间) - Date('"+System.DateTime.Now.ToString()+"') >"+Days;
SQLServer:
strTmpDB2 +=" and Cast(办件表.受理日期 as datetime)>='"+searchInfo.BeginDate+"' ";
DB2:
strTmpDB2 +=" and Date(办件表.受理日期)>= Date('"+searchInfo.BeginDate+"')";
SQLServer:
strSQL[Lilosoft.CoSpace.Util.DBType.DB2] = "INSERT INTO 公文交换发送表(公文编号,接收单位,分发时间,发送状态) VALUES (@strCode,@strUnit,getdate(),@strMsg)";
DB2:
strSQL[Lilosoft.CoSpace.Util.DBType.DB2] = "INSERT INTO 公文交换发送表(公文编号,接收单位,分发时间,发送状态) VALUES (@strCode,@strUnit,timestamp('"+ System.DateTime.Now.ToString() +"'),@strMsg)";
四、类型检查
SQLServer:
varchar类型与int类型之间可以进行隐式转换和连接计算
DB2:
varchar类型与int类型之间不能进行隐式转换和连接计算
修改 角色权限表 角色序号字段 字段类型为integer,原类型为varchar
修改 办件部门表 部门序号字段 字段类型为integer,原类型为varchar
五，自增返回
SQLServer:
"SELECT @@IDENTITY"
DB2:
"select max(序号) from TableA"
六，对象所有
SQLServer:
"select * from dbo.TableA"
DB2:
"select * from TableA"
七、类型检查
SQLServer:
bit 型能被DotNet默认转换为Bool型
DB2:
smallint型不能够被DotNet默认转换为Bool型
八、标准支持
SQLServer:
delete TableA where a = a
DB2:
delete from TableA where a = a
九、日期格式
办件表中受理日期字段类型为varchar(8),字段值形如:20060113
SQLServer:
"and Cast(办件表.受理日期 as datetime)>='"+strBeginDate+"' "
DB2:
"and Date(Concat(Concat(Concat(Concat(Substr(办件表.受理日期,1,4),'-'),Substr(办件表.受理日期,5,2)),'-'),Substr(办件表.受理日期,7,2)))<= Date('"+strBeginDate+"')"
请支持本土事业,http://itabby.com