CREATE TABLE HolderDetail(
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[OpentDate] datetime,
[TradeDate] datetime
)INSERT INTO HolderDetail([OpentDate])
SELECT '2013-01-01 15:00:00' UNION ALL
SELECT '2013-01-02 12:18:00' UNION ALL
SELECT '2013-01-03 09:00:00' UNION ALL
SELECT '2013-01-04 06:15:00'CREATE TABLE ReckonDate(
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[TradeDate] datetime,
[Startdate] datetime,
[Enddate] datetime
)INSERT INTO ReckonDate([TradeDate],[Startdate],[Enddate])
SELECT '2013-01-01 00:00:00','2013-01-01 04:00:00','2013-01-02 04:00:00' UNION ALL
SELECT '2013-01-02 00:00:00','2013-01-02 04:00:00','2013-01-03 04:00:00' UNION ALL
SELECT '2013-01-03 00:00:00','2013-01-03 04:00:00','2013-01-04 04:00:00' UNION ALL
SELECT '2013-01-04 00:00:00','2013-01-04 04:00:00','2013-01-05 04:00:00'----------------------------------------------------------------------------------
--现在需要更新HolderDetail,Tradedate值
--计算公式,HolderDetail表里OpentDate,对照ReckonDate表里的[Startdate],[Enddate]
--如果在它的范围里,对应找到TradeDate,然后把TradeDate的值更新到HolderDetail表里的 [TradeDate]
/*
'2013-01-01 15:00:00' = '2013-01-01 00:00:00'
'2013-01-02 12:18:00' = '2013-01-02 00:00:00'
'2013-01-03 09:00:00' = '2013-01-03 00:00:00'
'2013-01-04 06:15:00' = '2013-01-04 00:00:00'
*/
------------------------------------------------------------------------------------
--我只想到最笨的方法,用游标。一个个对比得到tradedate值,然后再更新。数据量很大,更新慢。
--有没有更好方法,比如批量直接更新。优化sql
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[OpentDate] datetime,
[TradeDate] datetime
)INSERT INTO HolderDetail([OpentDate])
SELECT '2013-01-01 15:00:00' UNION ALL
SELECT '2013-01-02 12:18:00' UNION ALL
SELECT '2013-01-03 09:00:00' UNION ALL
SELECT '2013-01-04 06:15:00'CREATE TABLE ReckonDate(
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[TradeDate] datetime,
[Startdate] datetime,
[Enddate] datetime
)INSERT INTO ReckonDate([TradeDate],[Startdate],[Enddate])
SELECT '2013-01-01 00:00:00','2013-01-01 04:00:00','2013-01-02 04:00:00' UNION ALL
SELECT '2013-01-02 00:00:00','2013-01-02 04:00:00','2013-01-03 04:00:00' UNION ALL
SELECT '2013-01-03 00:00:00','2013-01-03 04:00:00','2013-01-04 04:00:00' UNION ALL
SELECT '2013-01-04 00:00:00','2013-01-04 04:00:00','2013-01-05 04:00:00'----------------------------------------------------------------------------------
--现在需要更新HolderDetail,Tradedate值
--计算公式,HolderDetail表里OpentDate,对照ReckonDate表里的[Startdate],[Enddate]
--如果在它的范围里,对应找到TradeDate,然后把TradeDate的值更新到HolderDetail表里的 [TradeDate]
/*
'2013-01-01 15:00:00' = '2013-01-01 00:00:00'
'2013-01-02 12:18:00' = '2013-01-02 00:00:00'
'2013-01-03 09:00:00' = '2013-01-03 00:00:00'
'2013-01-04 06:15:00' = '2013-01-04 00:00:00'
*/
------------------------------------------------------------------------------------
--我只想到最笨的方法,用游标。一个个对比得到tradedate值,然后再更新。数据量很大,更新慢。
--有没有更好方法,比如批量直接更新。优化sql
然后这句没问题? 然后把TradeDate的值更新到HolderDetail表里的 [TradeDate] ?
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[OpentDate] datetime,
[TradeDate] datetime
) INSERT INTO HolderDetail([OpentDate])
SELECT '2013-01-01 15:00:00' UNION ALL
SELECT '2013-01-02 12:18:00' UNION ALL
SELECT '2013-01-03 09:00:00' UNION ALL
SELECT '2013-01-04 06:15:00' CREATE TABLE ReckonDate(
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[TradeDate] datetime,
[Startdate] datetime,
[Enddate] datetime
) INSERT INTO ReckonDate([TradeDate],[Startdate],[Enddate])
SELECT '2013-01-01 00:00:00','2013-01-01 04:00:00','2013-01-02 04:00:00' UNION ALL
SELECT '2013-01-02 00:00:00','2013-01-02 04:00:00','2013-01-03 04:00:00' UNION ALL
SELECT '2013-01-03 00:00:00','2013-01-03 04:00:00','2013-01-04 04:00:00' UNION ALL
SELECT '2013-01-04 00:00:00','2013-01-04 04:00:00','2013-01-05 04:00:00'
UPDATE
A
SET
TradeDate=B.TradeDate
FROM
HolderDetail A, ReckonDate B
WHERE
DATEDIFF(DD,A.OpentDate,B.TradeDate)=0
AND
A.OpentDate BETWEEN B.Startdate AND B.Enddate
SELECT * FROM HolderDetail
DROP TABLE HolderDetail,ReckonDate
/*Id OpentDate TradeDate
----------- ----------------------- -----------------------
1 2013-01-01 15:00:00.000 2013-01-01 00:00:00.000
2 2013-01-02 12:18:00.000 2013-01-02 00:00:00.000
3 2013-01-03 09:00:00.000 2013-01-03 00:00:00.000
4 2013-01-04 06:15:00.000 2013-01-04 00:00:00.000(4 行受影响)*/
以后会每天更新的。如果我用游标更新的话,几十万条要更新几个小时呢。所以想有最优的更新方式。
我写的存储过程如下:
PROCEDURE [dbo].[UpdateHolderDetailTradeDate]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ExecSQL varchar(1000),
@ID int,
@Opendate datetime,
@Tradedate datetime,
@errorSun INT --定义错误计数器
SET @errorSun=0 --没错为0
BEGIN TRANSACTION
DECLARE holderDetail_cur cursor FOR
SELECT ID,OpenDate FROM HolderDetail OPEN holderDetail_cur
FETCH NEXT FROM holderDetail_cur INTO @ID,@Opendate --先取第一条数据
SET @errorSun=@errorSun+@@ERROR
--开始循环插入数据
WHILE @@FETCH_STATUS=0
BEGIN
set @Tradedate = null
select @Tradedate=TradeDate from ReckonDate where @Opendate>=startDate and @Opendate<=endDate
UPDATE HolderDetail SET TradeDate=@Tradedate WHERE ID=@ID
SET @errorSun=@errorSun+@@ERROR
PRINT convert(varchar(10),@ID)+':'+convert(varchar(20),@Tradedate,120)
FETCH NEXT FROM holderDetail_cur INTO @ID,@Opendate
END
CLOSE holderDetail_cur --关闭游标
DEALLOCATE holderDetail_cur --释放游标
IF @errorSun<>0
BEGIN
PRINT '有错误,回滚'
ROLLBACK TRANSACTION--事务回滚语句
END
ELSE
BEGIN
PRINT '成功,提交'
COMMIT TRANSACTION--事务提交语句
END
END
不过你的语句,我写进去的测试数据是没问题的。我测试下原表看下。谢谢了
update a
set a.TradeDate=b.TradeDate
from HolderDetail a
inner join ReckonDate b on a.OpentDate between b.Startdate and b.Enddate