写一个小工具
是否有链接SQL2000或者sql2008的实例
要求:
填写服务器IP 数据库端口,用户名,密码
用小工具执行以下语句
需要带入参数@card_type 执行下面语句
DECLARE @table_no VARCHAR(120);
SELECT @table_no = 'bak' + REPLACE(REPLACE(REPLACE(CONVERT(CHAR(19), GETDATE(), 121), '-', ''), ':', ''), ' ', '');
--select @table_no
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'select * into [' + @table_no + '] from t_rm_vip_info';
EXEC (@sql);
/*****************************插入明细数据*******************************************/
---select * from t_rm_vip_info where dbo.f_get_descry_star(residual_amt) >10
---select card_id,dbo.f_get_descry_star(residual_amt) from t_rm_vip_info where dbo.f_get_descry_star(residual_amt) >10
INSERT INTO t_rm_saving_plus_record
(
branch_no,
card_id,
plus_count,
oper_id,
oper_date,
old_count,
num1,
other1,
residual_amt,
com_flag,
plus_real_count,
real_date,
pay_way
)
SELECT '000',
card_id,
(10 - dbo.f_get_descry_star(residual_amt)) AS plus_count,
'1001',
GETDATE(),
dbo.f_get_descry_star(residual_amt),
'0',
'自动清除',
'0',
'0',
(10 - dbo.f_get_descry_star(residual_amt)),
NULL,
'RMB'
FROM t_rm_vip_info
WHERE dbo.f_get_descry_star(residual_amt) > 10
AND card_type = @card_type;
/*****************************更改值*******************************************/
UPDATE t_rm_vip_info
SET residual_amt = '<:66420.-'
WHERE dbo.f_get_descry_star(residual_amt) > 10
AND card_type = @card_type;
GO