25112
asked on
Huge CTE - and log usage in simple mode.,
the below query still goes over the max of log space on disk..
this is a one time operation..
is there a way to break the above code to do in chunks so that the log does not go over (even though it is in simple mode)
there are 50M records in the main table.. so even if we can break into doing by 5M at a time, that will be good... is there a logic possible to break it into batches?
this is a one time operation..
is there a way to break the above code to do in chunks so that the log does not go over (even though it is in simple mode)
there are 50M records in the main table.. so even if we can break into doing by 5M at a time, that will be good... is there a logic possible to break it into batches?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Scott, please see below for data type and preferred out put (basically jumbles the data beyond recognition.. below sample has only 6 rows.. but in real table, we will have 50M+ records).
---------------------
CREATE TABLE [dbo].[MainTable](
[KeyColumn] [numeric](19,0) NULL,
[column1] [varchar](75) NULL,
[column2] [varchar](75) NULL,
[column3] [varchar](85) NULL,
[column4] [varchar](32) NULL,
[column5] [datetime] NULL
) ON [PRIMARY]
insert into [MainTable]
select 1,'abc1','bcd1','cde1','de f1',GETDAT E()-1 union
select 2,'abc2','bcd2','cde2','de f2',GETDAT E()-2 union
select 3,'abc3','bcd3','cde3','de f3',GETDAT E()-3 union
select 4,'abc4','bcd4','cde4','de f4',GETDAT E()-4 union
select 5,'abc5','bcd5','cde5','de f5',GETDAT E()-5 union
select 6,'abc6','bcd6','cde6','de f6',GETDAT E()-6
go
select * from MainTable;
with replaceOrder as (
select
KeyColumn,column1, column2,column3,column4,co lumn5,
key_id=row_number() over (order by KeyColumn),
column1_from=row_number() over (order by newid()),
column2_from=row_number() over (order by newid()),
column3_from=row_number() over (order by newid()),
column4_from=row_number() over (order by newid()),
column5_from=row_number() over (order by newid())
from MainTable
)
update r SET
column2=a.column2,
column1 = b.column1,
column3 = c.column3,
column4=d.column4,
column5 = e.column5
from replaceOrder r
inner join replaceOrder a on a.column1_from=r.key_id
inner join replaceOrder b on b.column2_from=r.key_id
inner join replaceOrder c on c.column3_from=r.key_id
inner join replaceOrder d on d.column4_from=r.key_id
inner join replaceOrder e on e.column5_from=r.key_id
GO
select * from MainTable;
drop table MainTable
---------------------
CREATE TABLE [dbo].[MainTable](
[KeyColumn] [numeric](19,0) NULL,
[column1] [varchar](75) NULL,
[column2] [varchar](75) NULL,
[column3] [varchar](85) NULL,
[column4] [varchar](32) NULL,
[column5] [datetime] NULL
) ON [PRIMARY]
insert into [MainTable]
select 1,'abc1','bcd1','cde1','de
select 2,'abc2','bcd2','cde2','de
select 3,'abc3','bcd3','cde3','de
select 4,'abc4','bcd4','cde4','de
select 5,'abc5','bcd5','cde5','de
select 6,'abc6','bcd6','cde6','de
go
select * from MainTable;
with replaceOrder as (
select
KeyColumn,column1, column2,column3,column4,co
key_id=row_number() over (order by KeyColumn),
column1_from=row_number() over (order by newid()),
column2_from=row_number() over (order by newid()),
column3_from=row_number() over (order by newid()),
column4_from=row_number() over (order by newid()),
column5_from=row_number() over (order by newid())
from MainTable
)
update r SET
column2=a.column2,
column1 = b.column1,
column3 = c.column3,
column4=d.column4,
column5 = e.column5
from replaceOrder r
inner join replaceOrder a on a.column1_from=r.key_id
inner join replaceOrder b on b.column2_from=r.key_id
inner join replaceOrder c on c.column3_from=r.key_id
inner join replaceOrder d on d.column4_from=r.key_id
inner join replaceOrder e on e.column5_from=r.key_id
GO
select * from MainTable;
drop table MainTable
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
scott, using temp table, i filled up tempdb and it quit after 30 minutes.
sharath, if i use CTE, log fills up and breaks.
any other way to have this contained?
if i put in bulk logged, will it help?
sharath, if i use CTE, log fills up and breaks.
any other way to have this contained?
if i put in bulk logged, will it help?
ASKER
with replaceOrder as (
select
KeyColumn,column1, column2,column3,column4,co
key_id=row_number() over (order by KeyColumn),
column1_from=row_number() over (order by newid()),
column2_from=row_number() over (order by newid()),
column3_from=row_number() over (order by newid()),
column4_from=row_number() over (order by newid()),
column5_from=row_number() over (order by newid())
from MainTable
)
update r SET
column2=a.column2,
column1 = b.column1,
column3 = LEFT(c.column3,1)+substrin
column4=d.column4,
column5 = e.column5
from replaceOrder r
inner join replaceOrder a on a.column1_from=r.key_id
inner join replaceOrder b on b.column2_from=r.key_id
inner join replaceOrder c on c.column3_from=r.key_id
inner join replaceOrder d on d.column4_from=r.key_id
inner join replaceOrder e on e.column5_from=r.key_id
GO